Ответы для проверки расчетов по вариантам

Таблица 3

Лабораторная работа 10

Сводные таблицы

Цель: Построение сводных таблиц и сводных диаграмм.

Задание на лабораторную работу. Провести анализ реализации товаров различных наименований по каждому клиенту.

Порядок выполнения лабораторной работы

Лист 16СвТаб1. Сделайте копию листа продаж на следующий лист книги. Все сводные таблицы в этой работе будут иметь одинаковую структуру – товары по клиентам, поэтому рекомендуется копировать последнюю полученную сводную таблицу на этот же лист ниже и преобразовывать ее для получения следующей таблицы, сводные диаграммы лучше размещать на отдельные листы диаграмм.

1. Постройте сводную таблицу суммарной выручки по товарам и клиентам, фамилии клиентов по оси строк, наименования товаров по оси столбцов, см. Рис. 2.

Рис. 2. Сводная таблица выручки по товарам и клиентам

1. Вставка – Сводная таблица

2. На существующий лист

3. ОК!

4. Мышкой переносим (согласно задания – рис. 36):

Клиент – Названия строк

Товар – Названия столбцов

Для данных выбираем - Выручка

Получили таблицу соответствующую заданной

Меняем: Задания строк – Клиент

Задания столбцов – Товар

2. Постройте сводную таблицу суммарной выручки по товарам и клиентам, суммы в ячейках сводной таблицы в денежном формате с рублями, см. Рис. 3. Денежный формат отображения настраивается через Формат поля данных сводной таблицы: вызвать Параметры поля данных сводной таблицы – Формат – Числовые форматы: Денежный – Обозначение: р.

Рис. 3. Образец сводной таблицы 2

3. Постройте сводную таблицу количества продаж товаров по клиентам. Для построения этой таблицы необходимо, не изменяя структуру сводной таблицы товары-клиенты, заменить итоговую функцию поля данных на количество значений: Параметры поля – Операция: Количество. Убрать рубли в ячейках сводной таблицы: Параметры поля – Формат – Числовые форматы: Общий. Образец таблицы см. на Рис. 4.

Рис. 4. Сводная таблица по количеству продаж

4. Постройте сводную таблицу суммарного количества килограмм проданных товаров клиентам. Смена поля в области данных осуществляется визуальным перетаскиванием полей с панели Список полей сводной таблицы (запускается одноименной кнопкой Список полей с панели инструментов Сводные таблицы). Для отображения весов с кг необходимо настроить пользовательский формат ячеек поля данных сводной таблицы: Параметры поля данных сводной таблицы – Формат – Числовые форматы: Все форматы – Тип: «0,000" кг"». Образец см. на Рис. 5.

Рис. 5. Сводная таблица по весам

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

Рис. 6. Сводная таблица с двумя полями данных

6. Постройте сводную таблицу суммарной выручки в руб. и суммарного количества проданного в кг. Управление форматами ячеек сводной таблицы осуществляется через Параметры соответствующего поля – Формат, образец форматирования см. на Рис. 7.

Рис. 7. Образец форматирования ячеек сводной таблицы

7. Постройте сводную таблицу средней выручки и среднего веса разовой продажи товаров клиентам. Смена функций в полях данных сводной таблицы осуществляется через Параметры соответствующего поля – Операция, см. Рис. 8.

Рис. 8. Сводная таблица с операциями Среднее

8. Постройте сводную таблицу средних цен товаров для разных клиентов. Вычисляемое поле =Выручка/Количество; рекомендуемое название нового поля СрЦена, см. Рис. 9.

Рис. 9. Сводная таблица с вычисляемым полем

9. Постройте сводную таблицу суммарного налога с продаж по каждому товару и каждому клиенту. Вычисляемое поле Налог=Выручка*0,3, Рис. 10.

Рис. 10. Сводная таблица налога с продаж

10. Постройте сводную таблицу выручки от продажи товаров четырем лучшим клиентам в долях от общей суммы. Использовать дополнительные функции поля данных, сортировку по столбцу Общий итог, фильтрацией по полю Клиент убрать клиента с наименьшей выручкой – Новых, Рис. 11.

Рис. 11. Сводная таблица выручки в долях от общей суммы

11. По сводной таблице 10 постройте сводную диаграмму выручки от продаж различных товаров по каждому клиенту в процентах от общей суммы по магазину. Тип диаграммы – накопительная гистограмма. На диаграмме (Рис. 12) должно быть видно, что Бистров предпочитает огурцы, а Кукин не любит капусту, Оптов берет все товары и в большом количестве и т.д. Заголовки, настройка шкалы оси значений, цветовой палитры рядов овощей, подписей значений сделайте по образцу Рис. 12.

Рис. 12. Образец оформления сводной диаграммы 11

12. На основе Сводной таблицы 2 постройте сводную диаграмму выручки в рублях по каждому товару и каждому клиенту. Тип диаграммы – цилиндрическая гистограмма, отображающая объемными цилиндрами группы значений из рядов (товаров) по категориям (клиентам). Отмените на диаграмме показ данных по покупателям Новых и Бистрову. Уберите серые стены и основание. Настройте объемный вид на изометрию, высота – 20 %. Для изображения стопок монет используйте заливку маркеров способом – Узор – Горизонтальная штриховка, см. Рис. 13.

Рис. 13. Образец оформления сводной диаграммы 12

13. На основе сводной таблицы 4 постройте сводную диаграмму, отображающую вклад каждого товара в сумму всего купленного каждым клиентом. Тип диаграммы – области с накоплением. Отмените на диаграмме показ данных по клиенту Новых. Настройте порядок рядов так, чтобы картофель лежал внизу, над ним – капуста, еще выше – огурцы, и сверху – помидоры. Подписи шкалы значений с кг, см. Рис. 14.

Рис. 14. Образец оформления сводной диаграммы 13

Рис. 15. Образец оформления сводной диаграммы 14

14. На основе сводной таблицы 4 постройте сводную диаграмму, отображающую раскладкк значений по категориям-клиентам и рядам-товарам. Тип диаграммы – трехмерная коническая гистограмма, конусы символизируют сыпучие товары. Объемный вид – изометрия, высота 40 %. Порядок рядов настроен так, чтобы высокие конусы не закрывали более низкие, см. Рис. 15.

Форма отчетности по лабораторной работе 10. Десять сводных таблиц на листе 16СвТаб1 и четыре сводные диаграммы на отдельных листах.

Лабораторная работа 11

Графики по данным из сводных таблиц

Цель: Построение графиков по данным из сводных таблиц.

Задание на лабораторную работу. Провести анализ динамики продаж отдельно по каждому товару и каждому клиенту, продемонстрировать результаты на графиках.

Порядок выполнения лабораторной работы

Лист 17СвТаб2. Сделайте копию листа продаж на следующий лист книги. Постройте следующие сводные таблицы и графики по данным из этих сводных таблиц.

Постройте сводную таблицу суммарных дневных продаж в кг. Сводная таблица должна иметь структуру «товары по дням» – наименования товаров по оси строк, дни по оси столбцов, Рис. 16.

Рис. 16. Сводная таблица дневных продаж по товарам

Сводн

′

′

ые таблицы не дают возможность непосредственно построить временные сводные диаграммы. Сделайте копию сводной таблицы средствами Правка – Специальная вставка – Форматы, и на этот же диапазон ячеек Специальная вставка – Значения. Постройте диаграммы типа график с временной горизонтальной осью реализации картофеля и капусты, Рис. 17 и отдельно график реализации огурцов и помидоров, Рис. 18.

Рис. 17. График по выбранным рядам данных

Выбор на диаграмме рядов данных осуществляется в окне диаграммы Исходные данные – вкладка Ряд – Добавить/Удалить; заказ временной оси – из Параметры диаграммы – Оси – Ось Х – ось времени. Чтобы соединить точки данных на графиках, необходимо настроить для активной диаграммы Сервис – Параметры – Диаграмма – для пустых ячеек – интерполяция значений. На графиках необходимо поддерживать естественные цвета, ассоциирующиеся с конкретным товаром: коричневый картофель, желтая капуста, зеленые огурцы, красные помидоры.

Рис. 18. График после замены рядов данных

Постройте вторую сводную таблицу – суммарной дневной выручки. Таблица должна иметь структуру «клиенты по дням» – фамилии клиентов по оси строк, дни по оси столбцов, Рис. 19.

Рис. 19. Сводная таблица дневных продаж по клиентам

Постройте диаграммы типа график с временн

′

ой горизонтальной осью выручки по клиентам Торбин, Кукин, Оптов, Рис. 20 и график выручки по клиентам Торбин, Кукин, Новых, Рис. 21. Вывод на график рядов данных также как и в предыдущем случае осуществляется через исходные данные диаграммы на вкладке Ряд.

Рис. 20. График дневной выручки по трем клиентам

Рис. 21. График выручки после смены рядов данных

Форма отчетности по лабораторной работе 11. Ни листе 17СвТаб2 четыре графика по данным из двух сводных таблиц.

Лабораторная работа 12

Консолидация данных

Цель: Освоить средства Excel по консолидации данных в пределах одного листа и консолидацию данных из нескольких листов.

Задание на лабораторную работу. Подсчитать прибыль по торговле с каждым клиентом.

Порядок выполнения лабораторной работы

Листы 18Картофель, 18Огурцы, 18Капуста, 18Помидоры. Вынести из сводной таблицы суммарной выручки структуры «клиенты-товары» (сводная таблица 1, построенная в лабораторной работе 10) детальную информацию по каждому товару на отдельные листы. Это можно быстро сделать инструментом Отобразить детали панели Сводные таблицы. А также можно применить второй способ – DoubleClick на ячейке в итоговой строке сводной таблицы выносит все данные из сводной таблицы, по которым вычислено это значение, на новый рабочий лист.

Для удобства одновременной работы на нескольких листах держите открытыми несколько окон с нужными листами в рабочем окне программы Excel. Это достигается из верхнего меню программы Окно – Новое, каждое из которых подсвертывается стандартной кнопкой в правом верхнем углу Свернуть в окно, см. Рис. 22.

На каждом из полученных четырех листов провести консолидацию (объединение) данных по клиентам, то есть по категориям в левом столбце, меню Данные – Консолидация. Опишем подробно технологию консолидации на листе 18Картофель выручки по клиентам. Скройте столбец Товар с повторяющимися данными, а также столбец Цена, см. Рис. 22. Установите курсор в диапазон ответа, B14 на Рис. 22. Вызываем окно Консолидация из верхнего меню Данные – Консолидация. Заполняем Функция: Сумма, визуально заполняем Список диапазонов: 18Картофель!$B1:$G11, включаем флажки  подписи верхней строки и  подписи левого столбца. OK. Ячейка B14 на пересечении названий категорий останется пустой, ее можно заполнить с клавиатуры.

В полученных таблицах суммарных значений выручки посчитать справа столбцы Прибыль – прибыль от торговли с каждым клиентом по конкретному товару, используя закупочные цены с одного из предыдущих листов книги. В строке формул на Рис. 22 показана формула для расчета прибыли от продаж картофеля Торбину. В этой формуле ссылка на ячейку $E$2 с листа ‘16СвТа1’ подставляет значение закупочной цены картофеля из списка товаров торговой точки на листе 16СвТа. Набор в формулы

Рис. 22. Четыре листа с детальной информацией по каждому товару в рабочем окне программы Excel

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

Проведите консолидацию из четырех восемнадцатых листов на новый лист 19Прибыль по категориям из верхней строки и из левого столбца, также с операцией суммирования, – получим прибыль от торговли по каждому покупателю. Список диапазонов набирается визуально, окно консолидация при этом держится на переднем плане, позволяя перемещаться по листам. Установите флажок  Создавать связи с исходными данными в окне Консолидация, чтобы получить структурированный лист, см. Рис. 23. Постройте диаграмму прибыли по покупателям типа ЧБ гистограмма (с вкладки нестандартные) объемного вида, с таблицей данных, все в серых тонах. Законченная диаграмма, представленная на Рис. 23, получена за два клика без дальнейшей переделки и готова для печати на не цветном принтере.

Рис. 23. ЧБ гистограмма прибыли по клиентам

Форма отчетности по лабораторной работе 12. Структурированный Лист 19Прибыль с ЧБ гистограммой.

Защита книги Excel

В некоторых случаях необходимо блокировать доступ сторонних пользователей к рабочей книге. Актуальность этого положения возрастает по мере прохождения лабораторных работ, так как злоумышленник может позаимствовать вашу рабочую книгу или нанести разрушительные действия в ней. Функциональные возможности Excel из меню Сервис – Защита предназначены для предотвращения случайного изменения данных рабочей книги. При наличии времени всегда можно получить и изменить все данные такой книги. Чтобы обеспечить безопасность книги, необходимо ограничить доступ к ней на уровне файла. Все параметры защиты файла книги собраны на вкладке Сервис – Параметры – Безопасность. Установить защиту можно также при сохранении книги: Файл – Сохранить как – Сервис – Общие параметры. – Параметры сохранения, см. рис. 58.

Рис. 24. Защита файла книги

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

Более сильная защита, защита от несанкционированного чтения, устанавливается наложением пароля на открытие файла книги. Система защиты попросит подтвердить пароль его повторным вводом, так как это ответственное решение. После назначения пароля нет способа снятия защиты без ввода этого пароля. В случае утери пароля открыть защищенную паролем книгу будет невозможно. Указываемый при этом пароль используется в качестве симметричного секретного ключа для шифрования-дешиф­рования книги. Обратите внимание, что пароль не хранится ни в книге, ни где-либо еще, поэтому при утере пароля восстановить содержимое книги невозможно. Это сделано в целях повышения безопасности, поскольку хранение пароля внутри книги облегчает задачу его выявления.

Пароли могут состоять из любого сочетания букв, цифр, пробелов или других знаков и иметь длину до 15 знаков. В паролях учитывается регистр знаков, поэтому для начинающих рекомендуются только цифровые пароли без переключения регистров и языков, например, 6–8 цифр даты вашего дня рождения. Excel рекомендует записывать пароли и хранить их в надежном месте, но лучше использовать пароль, который вы можете запомнить, чтобы не записывать его. Пароли взламываются методом подбора. Поэтому стойкость пароля напрямую зависит от его длины и количества разных использованных символов. Также необходимо учитывать, что взлом пароля начинают с просмотра словарей всех языков всех народов в прямом направлении, обратном и всевозможных транслитераций. Поэтому пароль не должен ни с чем ассоциироваться, а также быть трудно произносимым и не запоминаемым.

При всех последующих попытках открытия книги система защиты запрашивает пароль, рис. 59. Человек, не обладающий паролем, не прочитает вашу книгу. Для большей безопасности Excel не отображает на экране вводимый в окне пароль, набираемые с клавиатуры символы закрываются так называемыми эхо-символами. Это делается для того, чтобы злоумышленник не подсмотрел пароль из-за плеча или специальные программы – трояны, заранее внедренные на компьютер, не отсканировали пароль с экрана.

Рис. 25. Работа системы защиты

Для предотвращения чтения информации из книги сторонним программным обеспечением: служебными программами, текстовыми процессорами и прочими подобными средствами просмотра содержимого файла книги, например, редактором Far, используются дополнительные возможности по шифрованию книги, кнопка Дополнительно на рис. 58. Шифрование делает текст нечитаемым для всех, кроме пользователей, обладающих ключом шифра, который соответствует паролю на открытие файла. То есть пароль на открытие файла служит и в качестве пароля, и в качестве ключа шифрования. Предоставляемые Microsoft Excel возможности шифрования книг, см. рис. 60 вполне достаточны для обеспечения требований безопасности, предъявляемых большинством пользователей, если только вы не работаете в компании или правительственной организации, за секретами которой охотятся шпионы, пытающиеся украсть ваши последние разработки. Выбор метода шифрования осуществляет сам пользователь. См. рис. 60.

Рис. 26. Выбор метода шифрования

Алгоритм XOR заключается в наложении на битовый поток файла книги гаммы ключа с операцией исключающего ИЛИ и считается слабой защитой.

По умолчанию осуществляется совместимое с версиями MS Office 97/2000 симметричное шифрование по алгоритму RC4.

Более сильные методы шифрования: DSS, RSA, AES поддерживают более длинные ключи: 40-128 символов. Однако такие книги открыть в ранних версиях Excel невозможно.

Следует понимать, что идеальной защиты в электронном мире не существует. Также надо всегда помнить, что любой файл, в том числе и книгу Excel, можно удалить или подменить средствами проводника Windows.

Контрольные вопросы

1. Что такое электронная таблица?

2. Как запустить программу Excel?

3. Какие расширения имеют файлы с книгами Excel?

4. Сколько листов содержит новая книга по умолчанию?

5. Сколько максимально рабочих листов может содержать книга Excel?

6. Как добавить лист в книгу Excel?

7. Как изменить порядок листов в книге?

8. Как переименовать лист в книге Excel?

9. Как отобразить одновременно несколько листов в рабочей области программы Excel?

10. Какие типы данных поддерживает Excel в своих ячейках?

11. Каково внутреннее представление дат в Excel?

12. Какой символ используется в Excel по умолчанию в качестве разделителя между целой и дробной частями вещественного числа?

13. Как организовать заголовок точно по центру таблицы?

14. Как организовать многострочный заголовок?

15. Как осуществить переход на новую строку внутри ячейки Excel?

16. Какие форматы отображения данных предоставляет пользователю Excel?

17. Что такое Общий формат?

18. Как выравнивает Общий формат текст внутри ячейки?

19. Как выравнивает Общий формат число внутри ячейки?

20. Чем числовой формат отличается от Общего формата?

21. Чем Денежный формат отличается от Финансового формата?

22. Что такое Экспоненциальный формат?

23. Как создать пользовательский формат?

24. Как настроить формат ячеек, чтобы в них отображались числовые данные и текст, например, 100 кг?

25. Сколько секций в маске пользовательского формата ячейки?

26. Как при помощи пользовательских форматов осуществить условное форматирование цветом шрифта?

27. Что делать, если в ячейке вместо числа отображаются решетки #####?

28. Какой вид приобретает курсор в режиме заполнения формулами диапазона ячеек?

29. Что такое диапазон ячеек?

30. Как выделить на рабочем листе несмежный диапазон ячеек?

31. Как задать в формуле Excel смежный диапазон ячеек?

32. Как задать в формуле Excel несмежный диапазон ячеек?

33. Что такое относительная ссылка в формуле Excel?

34. Что такое абсолютная ссылка в формуле Excel?

35. Что такое смешанные ссылки в формулах Excel?

36. Для чего используются относительные ссылки?

37. Когда необходимо использовать абсолютные ссылки?

38. Какие символы используются для обозначения абсолютных адресов в формулах Excel?

39. Как быстро переключать относительные, смешанные и абсолютные ссылки?

40. Как задается ссылка на другие листы рабочей книги?

41. Что является признаком формулы для Excel?

42. Сколько ссылок на ячейки можно использовать в одной формуле Excel?

43. Для чего предназначена строка формул?

44. Какие арифметические операции используются в формулах Excel?

45. Какой порядок вычисления арифметических операций в формулах Excel?

46. Как изменить порядок вычисления арифметических операций в формуле Excel?

47. Какова форма обращения к функциям в формулах Excel?

48. Сколько функций встроено в Excel?

49. Что такое аргумент функции?

50. Какой символ используется для перечисления нескольких аргументов в функции?

51. Каково максимальное количество аргументов у функции Excel?

52. Можно ли в качестве аргумента функции использовать функцию?

53. Сколько уровней вложенности функций поддерживает Excel?

54. Какая самая распространенная функция Excel?

55. Как быстро вставить одну из десяти последних использованных функций?

56. Какие ячейки называются влияющими?

57. Какие ячейки называются зависимыми?

58. Как ведут себя относительные ссылки на влияющие ячейки при перемещении формулы?

59. Как ведут себя абсолютные ссылки на влияющие ячейки при перемещении формулы?

60. Как ведут себя относительные ссылки на влияющие ячейки при копировании формулы?

61. Как ведут себя абсолютные ссылки на влияющие ячейки при копировании формулы?

62. Как ведут себя в формуле абсолютные ссылки на влияющие ячейки при перемещении этих влияющих ячеек?

63. Как ведут себя в формуле относительные ссылки на влияющие ячейки при перемещении этих влияющих ячеек?

64. Как быстро выделить весь лист Excel?

65. Что такое Форма данных?

66. Для чего предназначена Форма данных?

67. Какие режимы поддерживает Форма данных?

68. Как запустить Форму данных?

69. Что делать, если в Форме данных не отображаются имена полей списка?

70. Что такое сортировка? Дайте понятие.

71. Что такое ключ сортировки?

72. В каком порядке Excel сортирует текстовые данные?

73. Что такое многоуровневая сортировка?

74. Сколько уровней сортировки поддерживает инструмент Excel Данные – Сортировка?

75. Что такое пользовательский порядок сортировки?

76. Как сортировать в пользовательском порядке?

77. По какому ключу Excel поддерживает пользовательский порядок сортировки?

78. Как отсортировать список в пользовательском порядке по второму ключу?

79. Как отсортировать список в пользовательском порядке по первому и второму ключам?

80. Сколько уровней отмены поддерживает Excel для операций сортировки?

81. Как вернуть список к первоначальному состоянию, к виду до всех сортировок?

82. Какой порядок сортировок в многоуровневых сортировках?

83. Сколько цветов поддерживает Excel?

84. Как настроить цветовую палитру пера и кисти в Excel?

85. Что такое диаграмма Excel?

86. Как быстро построить диаграмму Excel?

87. Сколько стандартных типов диаграмм предоставляет Excel?

88. Для чего используются нестандартные типы диаграмм?

89. Что такое гистограмма?

90. Сколько рядов данных можно изобразить на круговой диаграмме?

91. Что такое категории данных на диаграмме?

92. Как категории данных изображаются на гистограммах?

93. Что такое нормированные диаграммы?

94. Что такое накопительные диаграммы?

95. Какие типы диаграмм имеют существенно трехмерный вид?

96. Какую форму имеет маркер данных на гистограмме?

97. Какую форму имеет маркер данных на круговой диаграмме?

98. Как поменять цвет маркера на диаграмме?

99. Чем график отличается от точечной диаграммы?

100. Что такое тренд?

101. Какие типы диаграмм позволяют построить тренд?

102. Как добавить тренд на диаграмму?

103. Как добавить пояснение на диаграмму?

104. Что такое легенда диаграммы?

105. Как убрать легенду с диаграммы?

106. Как добавить легенду на диаграмму?

107. Как изменить порядок отображения рядов данных на диаграмме?

108. Как изменить тип диаграммы?

109. Как убрать серый фон области построения диаграммы?

110. Каковы правила назначения пароля на открытие книги Excel?

111. Где и в каком виде хранятся пароли?

112. В чем отличие пароля для открытия файла и пароля разрешения записи в MS Excel?

113. Какой длины пароль можно назначать на открытие книги Excel?

114. Какие символы можно использовать в пароле?

115. Почему при вводе пароль закрывается эхом?

116. Как восстановить утерянный пароль?

117. В чем заключается идея шифрования информации?

118. Как действует средство шифрования книги Excel?

119. Какая защита считается абсолютной?

120. Использование более длинного ключа шифрования повышает или снижает защиту зашифрованного файла?

121. Что такое сильное шифрование?

122. Какие алгоритмы шифрования считаются слабыми?

123. Какой метод шифрования используется по умолчанию в MS Excel?

Заключение

В лабораторном практикуме Excel большинство заданий предложено по работе с одним из наиболее востребованных в реальной практике разделов – обработке списков данных.

Первая лабораторная работа предназначена для формирования списка Excel. Предварительно во введении необходимо ознакомиться с общими понятиями по спискам Excel, требованиями к их разработке, процедурами обработки списков. В результате выполнения работы пользователь сможет формировать список различными способами, освоить приемы работы с Формой данных, осуществлять поиск записей по критериям (одному или нескольким) и т.п.

Начальный этап – подготовка списка исходных данных является самым ответственным при работе со списками в дальнейшей работе. Поэтому ошибки, допущенные на этом этапе, скажутся и на всех дальнейших действиях пользователя. Учитывая это, в электронной таблице Excel предусмотрены средства защиты данных, которые отрабатываются в лабораторной работе №2. В этой же работе пользователь может настроить условия проверки, с выводом сообщения о наличии ошибок и осуществить проверку введенных данных.

После того как список выверен, устранены ошибки данных при формировании таблицы, можно приступать к обработке данных в списках. Одной из самых распространенных операций при работе со списком является сортировка записей в списке. В Excel предусмотрены как простейшие процедуры (используя кнопки панели инструментов Стандартная), так и многоуровневые варианты, включая пользовательские сортировки. Доступ к более сложным сортировкам осуществляется через главное меню Данные – Сортировка или Сервис – Параметры – Списки. Лабораторная работа №3 как раз позволяет выполнить сортировку в различных вариантах.

Работа со списками в Excel предусматривает такую функцию как подведение итогов. Данная функция позволяет значительно ускорить и упростить наиболее часто используемые в экономической практике функции суммирования, нахождения среднего значения, максимума, минимума в разных интерпретациях по включенным в списки полям. Функция Данные – Итоги применяется после упорядочивания (сортировки) списка. Эта функция позволяет, как укрупнить, так и детализировать итоговые результаты, выводить только те, которые необходимы для анализа. По результатам работы функции Данные – Итоги в лабораторной работе №4 предлагается построить диаграммы и совмещенную диаграмму с гистограммой. Пятая лабораторная работа продолжает развивать возможности функции Данные – Итоги для аналитических заключений. Здесь пользователь имеет возможность научиться создавать графики с помощью Мастера диаграмм, используя дополнительно для оформления панель инструментов Рисование. Придание наглядности и выразительности табличных данных, выраженных графическим методом, является одним из основных преимуществ Excel. В лабораторных работах №5 и №6 даны подробные описания приемов оформления различных диаграмм и графиков.

Лабораторные работы №7 и №8 предназначена для отработки навыков с так называемой фильтрацией данных в списках. В Excel заложены специальные возможности работы с различными фильтрами. Их легко активизировать через главное меню Данные – Фильтр. Следует отметить, что в Excel предусмотрены разные варианты фильтрации: от простых до сложных условий выборки записей. К простым относится отбор записей с помощью Автофильтра (Данные – Фильтр – Автофильтр). Выполнив в работе 20 предложенных вариантов Автофильтра пользователь научится работать в данном режиме. Следующих 20 заданий выполняется Пользовательским фильтром, который позволяет применять более сложные критерии выборки. Лабораторная работа №8 предназначена для работы с Расширенным фильтром. Расширенный фильтр применяется в том случае, когда необходимо использовать несколько критериев для фильтрации данных в списке. Закончив выполнение следующих 20 заданий, можно вполне освоить всевозможные действия, связанные с выборкой данных в списке.

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

Целью лабораторных работ №10 и №11 является получить знания по формированию сводных таблиц, которые позволяют интерпретировать табличные данные в различных срезах как в табличной форме, так и в виде диаграмм. Выполнив все задания по этим работам, пользователь сумеет в дальнейшем применить эти знания в аналогичной ситуации.

Завершает цикл работ 12-я лабораторная работа, предназначенная для отработки темы «Консолидация данных». Данная работа позволяет выполнить ряд операций с однотипными таблицами.

Задачи, аналогичные рассмотренной в данном практикуме, составляют какой-то процент применения электронных таблиц Excel. В качестве заключения приведем некоторую их классификацию.

Это во-первых, использование Excel в качестве “умного” калькулятора – изготовление небольших электронных таблиц на скорую руку с целью воспользоваться в оперативном порядке несколькими встроенными функциями из арсенала Excel. Например, чтобы решиться взять кредит, можно быстро, за несколько минут подсчитать в Excel размер ежемесячной выплаты для разных сумм кредита. Такие таблицы, как правило, даже не сохраняются.

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

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

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

Используются электронные таблицы в качестве клиентских программ для доступа к большим базам банных. Excel имеет средства доступа из своих книг к внешним источникам данных, даже если у этих данных разный формат. Встроенные в Excel инструменты управления плоскими базами данных: диалоговое окно формы данных, автофильтры, промежуточные итоги, сводные таблицы, – намного облегчают обработку данных, по сравнению с СУБД с огромным количеством таблиц и сложными связями между ними. Импорт внешних данных на рабочий лист осуществляется на основе запросов Microsoft Query. Кроме того, на основе технологии DAO (Data Access Objects – объектов доступа к данным) есть возможность производить изменения непосредственно в базе данных, а не только на рабочем листе.

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

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

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

Управление ресурсами Интернет из Excel обеспечивает набор функций по сохранению рабочих листов в формате html, в том числе интерактивных.

И наконец, класс нетрадиционного применения электронных таблиц Excel, то есть успешные попытки использования Excel там, для чего они не предназначались. Это создание и проведение интерактивных презентаций средствами Excel. Это постоянное и широкое использование электронных таблиц Excel как инструмента для ввода данных, так как Excel поддерживает самые разные форматы других программ. Это изготовление типовых печатных бланков так, как это невозможно даже в текстовом процессоре Word или издательской системе PageMaker, например, счетов клиентам, накладных, счет-фактур, прайс-листов, ценников, этикеток. Наблюдается также использование Excel в качестве платформы компьютерных игр, особенно стратегических.

Библиографический список

1. Информатика для юристов и экономистов / С. В. Симонович и др. – СПб. : Питер, 2001. – 688 с.

2. Информатика : учебник / Н. В. Макарова и др. – М. : Финансы и статистика, 2004. – 768 с.

3. Гарнаев А. Excel, VBA, Internet в экономике и финансах. – СПб. : БХВ, 2002. – 816 с.

4. Гельман В. Решение математических задач средствами Excel : практикум. – СПб. : Питер, 2003. – 240 с.

5. Гладкий А. , Чиртик А. Excel. Трюки и эффекты. – СПб. : Питер, 2005. – 368 с.

6. Додж М. , Стинсон К. Эффективная работа с Microsoft Excel 2002. – СПб. : Питер, 2004. – 992 с.

7. Долженков В. , Колесников Ю. Microsoft Excel 2000. – СПб. : БХВ, 1999. – 1088 с.

8. Коцюбинский А. , Грошев С. Excel для бухгалтера в примерах. – М. : ЗАО Издательский Дом Главбух, 2003. – 240 с.

9. Кузьмин В. Microsoft Office Excel 2003 : учебный курс. – СПб. : Питер, 2004. – 464 с.

10. Microsoft Office XP шаг за шагом : практическое пособие / Пер. с англ. – М. : ЭКОМ, 2002. – 720 с.

11. Плакунов М. Планирование на малых и средних предприятиях средствами EXCEL. – СПб. : Питер, 2004. – 160 с.

12. Сандалов В. Использование встроенных функций MS Excel в финансово-экономических расчетах : учебное пособие. – Хабаровск, РИЦ ХГАЭП, 2001. – 76 с.

13. Соломенчук В. , Романович А. Практическая бухгалтерия на Excel для малого бизнеса. – СПб. : Питер, 2005. – 256 с.

14. Рычков В. Самоучитель Excel 2002. – СПб. : Питер, 2004. – 320 с.

15. Уокенбах Д. Excel 2003. – М. : Вильямс, 2005. – 800 с.

16. Фрай К. Хитрости Excel. – СПб. : Питер, 2005. – 368 с.

17. Хэлворсон М. , Янг М. Эффективная работа с Microsoft Office 2000. – СПб. : Питер, 2000. – 1232 с.

18. Электронная справочная система Microsoft Excel.

WWW-ссылки

19. http://www.microsoft.ru/office/excel. Официальная страница Microsoft Excel.

20. http://www.microsoft.com/Microsoft Office 2000 Resource Kit. Руководство по Microsoft Office на Web-узле корпорации Microsoft. In English.

21. http://www.microsoft.com/rus/support. Русская версия базы знаний Microsoft Knowledge Base.

 Не путать название заголовка столбца и функцию Количество