Продвинутые технологии Microsoft Excel для экономиста

Санкт-Петербургский  государственный университет

 

Экономический  факультет

 

Кафедра информационных систем в экономике

 

 

 

 

 

 

 

 

 

 

 

“Продвинутые технологии Microsoft Excel для экономиста”

 

 

 

 

Курсовая работа

 

по дисциплине «Информатика» 

 

 

Работа представлена ________  ______________

     Дата         Подпись студента

 

Руководитель

кандидат физико-математических наук, доцент Лёзина Татьяна Андреевна

 

 

Работа защищена ________   _________________

Оценка       Подпись руководителя

 

 

 

 

 

 

Санкт-Петербург   2009

 

Оглавление

 

 

Введение

 

Моя курсовая работа посвящена обеспечению автоматизированного учета отпуска товаров для магазинов с помощью средств Microsoft Excel. Эту тему я выбрал потому что посчитал её наиболее интересной и актуальной из предложенных.

Работал я в программе Microsoft Excel 2003, так как её интерфейс более привычен рядовому пользователю, чем интерфейс MS Excel 2007.

 

Целью моей курсовой работы являлось знакомство с «продвинутыми» и малоизвестными функциями Excel, таких как сводная таблица, списки, динамические диапазоны, и другими, а также применение их на практике. О том, как я их применял, далее и пойдет речь.

 

Начиная работать над курсовой, я  поставил перед собой задачу –  облегчить работнику сферы продаж (кассиру, продавцу, бухгалтеру,…) учет отпуска товаров. Для этого мною были привлечены следующие средства MS Excel: функции «Просмотр», «Если», «Индекс», «Поиск», «Счет», а также инструменты «Сводная таблица», «Список» и «Диапазон».

 

 

Принцип работы

 

Продав товар, пользователь вводит информацию о том, сколько единиц этого товара он продал. Для этого он выбирает нужный товар из списков в столбцах С-Е, а в соответствующей строке столбца G вводит число – количество проданного товара. Артикул, цена и стоимость рассчитываются по формулам. По окончанию работы с этой таблицей (например по истечении месяца) пользователь переходит к листу «Сводная таблица». С помощью сводной таблицы он может воспользоваться своими данными для расчета количества и стоимости проданного товара в наиболее удобном ему варианте, так как для изменения структуры сводной таблицы не требуется специальной подготовки.

 

Создание книги

 

Шаг 1

Первым делом, для начала работы необходимо заполнить библиотеку, в  нашем случае – создать списки товаров для продажи, к которым  в дальнейшем мы будем обращаться. В моем примере пользователь продает  3 типа товаров: аудиоплееры, акустические системы, и сотовые телефоны различных марок и моделей. Список этих товаров находится на листе «Библиотека», и устроен таким образом, чтобы было удобно присваивать имена диапазонам ячеек, содержащим товары одной группы (фирмы, марки).

На листе «Артикулы» все товары из библиотеки записаны в столбце  A начиная с А2. А напротив наименования каждого товара в столбцах В и С содержатся его артикул и цена соответственно. На этом создание библиотеки окончено, переходим к шагу 2.

 

Шаг 2

Теперь я должен был определить диапазоны ячеек на листе «Библиотека» и присвоить им соответствующие имена. Первым делом выделим названия марок товаров и присвоим им имена соответствующих товаров. Например диапазон ячеек, содержащих названия марок Nokia, Samsung, Siemens, Acer, HTC и Motorola называется «Сот.телефоны». Этот диапазон статический, это значит что он не будет ни расширяться, ни сужаться, ни при каких обстоятельствах.

Для моделей также можно было создать такие же диапазоны, но моя  библиотека предполагает возможное  добавление моделей в библиотеку, поэтому необходимо было воспользоваться не статическим диапазоном, а динамическим, то есть расширяющимся при добавлении в него файлов. Создание динамического диапазона оказалось самой сложной частью моей работы, далее я поясню – почему.

 

Шаг 3

Затем, на листе «Таблица» я создал таблицу со списками. Рассмотрим её подробнее. Списки в столбце С включают в себя лишь 3 неизменных значения: аудиоплееры, акустич.системы, и сот.телефоны. Источником списка в столбце D является формула «=ДВССЫЛ($C6)», которая работает благодаря диапазонам, одноименным с названиями товаров столбца C. Таким образом выбрав в столбце С значение «сот.телефоны», в столбце D пользователь будет выбирать именно одну из марок сотовых телефонов а не что либо другое. Но как поступить с моделями? Я уже писал о том что хочу воспользоваться динамическим диапазоном. Но проблема в том что функция «=ДВССЫЛ()» распознает содержимое ячейки на которую ссылается исключительно как текст. Динамический же диапазон, созданный с помощью функции «=СМЕЩ()» (на примере плееров Explay:

=СМЕЩ(Библиотека!$E$3;0;0;СЧЁТЗ(Библиотека!$E:$E);1)

не отображается в списке имен диапазонов книги, поэтому не происходит сопоставления содержимому столбца D и имени диапазона. Поэтому динамический диапазон был создан другим образом:

=СМЕЩ(ИНДЕКС(Библиотека!$1:$1;1;ПОИСКПОЗ(таблица!$D6; Библиотека!$1:$1;0));2;0;9999;1).

Таким образом содержимое ячеек в столбце Е напрямую зависит от ячеек столбца D, и мне даже не пришлось использовать функцию «=ДВССЫЛ()».

Также пользователь с руки вводит число проданных товаров в  столбце G, остальные же столбцы рассчитываются на основе введенных данных по формулам.

 

Шаг 4

И последний шаг – создание сводной  таблицы. Я создал таблицу на отдельном листе который так и называется – «Сводная таблица». В область страницы я поместил наименование и марку, в область строк – артикул и товар, а в область данных – количество по полю «продано ед.», и сумму по полю «стоимость». Таким образом пользователь может оперировать таблицей, вызывая на лист те значения которые ему необходимы для различных задач (подведение баланса за месяц, составление отчета и т.п.).

После окончания работы необходимо наложить защиту на те части книги, которые не подразумевают изменение их пользователем. Это нужно для того чтобы неопытный пользователь случайно не испортил формулы или имена диапазонов, что может критически повлиять на работоспособность таблиц.

 

 

 

Заключение

 

Я считаю, что выполняя работу, я  достиг своей цели, но мою книгу  можно дополнить еще множеством полезных различных функций и  макросов, а также оптимизировать для нужд конкретного пользователя. А так как тема работы, как и сама работа, мне очень понравилась, я думаю, что в дальнейшем я продолжу изучение возможностей Excel, и использую их для следующей курсовой работы.

 

Список использованной литературы:

 

1. Уэйн Л. Винстон. «Microsoft Excel: анализ данных и построение бизнес моделей. 2004г
2. http://www.planetaexcel.ru.