Создать таблицу учета продаж мороженого, в которой выполняется подсчет результатов продаж мороженого по кварталам и итоги продаж за год.
1. Загрузить программу Excel и на первом листе ввести форму таблицей, заполнить ее наименованиями мороженого и формулами расчетов “суммы выручки от продаж каждого сорта мороженого и всех сортов” (табл. 11.4)
Таблица 11.4
Шаблон таблица учета продаж
| А | В | С | D | |
| Учет продаж мороженого | ||||
| Марка | Количество | Цена | Сумма | |
| Сливочное | =В3*С3 | |||
| Эскимо | =В4*С4 | |||
| Молочное | =В5*С5 | |||
| Лакомка | =В6*С6 | |||
| Пломбир | =В7*С7 | |||
| Фруктовое | =В8*С8 | |||
| ИТОГО: | =СУММ(ВЗ:В8) | =CУММ(D3:D8) |
2. Отформатировать ячейки таблицы в столбцах Цена и Сумма, в которых будут отображаться финансовые значения, используя команду Ячейки в меню Формат и выбрав Финансовый Формат представления
3. Создать аналогичные заготовки таблицы на листах, отображающих расчеты продаж в 1,2,3 и 4 кварталах, и итогов продаж за год. Выделить диапазон A1:D9 и скопировать таблицу на другие листы, для чего, выделив указанный диапазон таблицы, выбрать в меню Правка команду Копировать. Затем, указав другой лист, установить курсор в начало листа, выделив ячейку А1, и вставить таблицу из буфера обмена командой Вставить из меню Правка. Если в книге будет недостаточно листов, то командой Лист в меню Вставка вставить недостающий лист.
4. Переименовать листы, задав им названия: 1 квартал, 2 квартал, 3 квартал, 4 квартал, Год.
5. Удалить на листе Год столбец С (Цена), для чего, выделив этот столбец, выбрать в меню Правка команду Удалить.
6. Заполнить таблицы продаж мороженого по кварталам на листах: 1 квартал, 2 квартал, 3 квартал, 4 квартал.
7. В столбец В (Количество) на листе Год ввести формулу, суммирующую количество проданных мороженых по сортам =СУММ(‘1вартал:4 квартал’!ВЗ), где: «1 квартал:4 квартал»! - ссылка на диапазон листов; ВЗ — ссылка на ячейку на всех указанных листах.
Эту формулу можно вставить и другим способом: на листе Год указать ячейку ВЗ, в которую вводится функция, ввести знак равенства (=), ввести имя функции СУММ, а затем — открывающую круглую скобку. После этого указать ярлычок листа 1 квартал, и выделить ячейку ВЗ. Затем, удерживая нажатой клавишу Shift, указать последний лист, на который необходимо сослаться, 4 квартал, и ячейку ВЗ, после чего ввести закрывающую скобку. Скопировать формулу =СУММ(‘ 1 квартал:4 квартал’!ВЗ) из ячейки ВЗ на листе Год в диапазон В4:В9.
8. В столбец С (Сумма) на листе Год ввести формулу расчета суммы выручки от продаж мороженого по сортам и всего за год =СУММ(‘1 квартал:4 квартал’!DЗ). Скопировать формулу =СУММ(‘1 квартал:4 квартал’!DЗ) из ячейки СЗ на листе Год в диапазон С4:С9.
9. Поочередно открывая листы: 1 квартал, 2 квартал, 3 квартал, 4 квартал, ввести данные о продажах мороженого разных сортов (количество и цену). Пронаблюдать, как на листе Год суммируются итоги продаж по кварталам.
10. Построить круговую диаграмму, отражающую долю выручки от продажи каждого сорта мороженого за год в % от общей суммы. Выделив диапазон данных АЗ:С8, выбрать в меню Вставка команду Диаграмма. Следуя указаниям Мастера диаграмм, выбрать Объемный вариант разрезанной круговой диаграммы и щелкнуть кнопку Далее. Затем уточнить диапазон отображаемых данных Год!$А$3:$С$8, указать на отображение рядов данных в столбцах, на вкладке Ряд удалить Ряд 1, оставив для отображения данные только Ряд 2 из столбца с суммой выручки от продаж в столбце С на листе Год. Щелкнув кнопку Далее, задать заголовки диаграммы и включить подписи долей на диаграмме. На последнем шаге диалога с Мастером диаграмм включить размещение диаграммы на имеющемся листе Год и щелкнуть кнопку Готово. Просмотреть полученную диаграмму и уточнить ее позицию на листе.
11. Вставить на лист Год рисунок мороженого из Microsoft Clip Gallery.для этого сделать текущим лист Год и вставить в начало таблицы три пустых строки. Указав ячейку В1, выбрать команду Рисунок в меню Вс тавка. затем выбрать опцию Картинка и в списке рисунков Microsoft Gallery выбрать нужный, а затем щелкнуть кнопку Вставить.
12.. Закрыть окно Excel, сохранив файл под именем Продажа мороженного
Лабораторная работа 5
Обработка списков в Excel
1. Создать на первом листе таблицу Список участников соревнований. Вначале нужно определить исходные данные задачи: фамилии и имена спортсменов (текст), вес (число), название региона (текст), набранные в ходе состязаний очки и место, занятое на соревновании (числа).
2. Описать структуру таблицы и заполнить ее данными, как показано в табл.
| А | В | С | D | Е | F | |
| Список участников соревнований | ||||||
| Фамилия | Имя | Вес | Регион | Очки | Место | |
| Власов | Василий | Москва | ||||
| Знаков | Евгений | Самара | ||||
| Зуйков | Владимир | Ульяновск | ||||
| Кадышев | Евгений | Самара | ||||
| 7. | Карелин | Александр | Новосибирск | |||
| Сайфутдинов | Джалиль | Курган | ||||
| Сердюков | Александр | Воронеж | ||||
| Чеглаков | Алексей | Пермь | ||||
| Шевченко | Алексей | Ростов |
4. Отсортировать список участников, упорядочив его в порядке возрастания весовых категорий. Для этого следует установить курсор в столбец С и щелкнуть кнопку Сортировка по возрастанию в панели инстру 
ментов Стандартная. После этого порядок размещения записей о спортсменах в таблице изменится.
5. Найти участников соревнований из Самары. Для этого в меню Правка выбрать команду Найти, затем в окне Найти в поле Что ввести образ поиска «Самара», задать опции поиска: не учитывать регистр, просматривать по строкам, и щелкнуть кнопку Найти далее. Курсор выделяет ячейку с найденным текстом. Щелчок кнопки Найти далее переводит курсор на следующую ячейку таблицы, удовлетворяющую условиям поиска. Если данных нет, то на экране будет выведено сообщение об этом. Закрыть окно поиска.
6. Отфильтровать список спортсменов и просмотреть участников состязаний в весовой категории до 130 кг. Для этого следует выбрать команду Фильтр в меню Данные, а затем выбрать опцию Автофильтр. После этого в правой части заголовков всех граф таблицы появятся стрелки. Выбрав графу Вес, нажмите кнопку со стрелкой, следует выбрать значение в списке, например 130, как показано на рис.
После этого в окне Excel будет выведен список участников, удовлетворяющий заданному фильтру (табл.).
| А | В | с | D | Е | F | |
| Список участников соревнований | ||||||
| Фамилия | Имя | Вес | Регион | Очки | Место | |
| Кадышев | Евгений | Самара | ||||
| Карелин | Александр | Новосибирск | ||||
| И | Сердюков | Александр | Воронеж |
Можно применить фильтр к уже отфильтрованному списку. Например, если, щелкнув стрелку в графе Имя, установить фильтр Александр, то в таблице будут отображаться только записи о спортсменах, у которых Вес=130 и Имя=«Александр».
7. Удалить фильтр Имя=«Александр», для чего следует нажать кнопку со стрелкой в графе Имя, а затем выбрать из развернувшегося списка Все.
Чтобы удалить фильтры для всех столбцов списка, нужно выбрать пункт Фильтр в меню Данные, а затем — команду Отобразить все. Для удаления автофильтра выберите пункт Фильтр в меню Данные, а затем — команду Автофильтр.
8. Для просмотра записей таблицы с использованием формы выбрать команду Форма в меню Данные. После этого на экране раскроется форма с отображением записей таблицы на листе 1.
Для получения подсказки о назначении какого-либо параметра формы нажмите кнопку в правом верхнем углу окна и щелкните интересующий вас параметр.
Сначала в форме отображается информация из первой строки таблицы. В окне формы над кнопкой Добавить отображается информация 1 из 9, что означает: в форме отображается текущая запись с номером 1, всего записей в таблице 9.
Для просмотра следующей записи щелкните кнопку Далее, предыдущей записи — Назад.
9. Для того чтобы задать фильтр отбора данных, нужно щелкнуть кнопку Критерии, затем в поле Регион ввести значение «Самара» и нажать Enter.
После этого в форме будут отображаться только данные об участниках из Самары.
Для просмотра остальных записей, удовлетворяющих условиям отбора Регион=«Самара» используйте кнопки Далее и Назад.
10. Для отмены фильтрации данных следует щелкнуть кнопку Критерии и очистить поле, в котором задано значение фильтра отбора данных (в данном примере в поле Регион удалить значение «Самара»).
11. Для вставки записи в таблицу укажите ячейку списка, начиная с которой следует добавлять записи (например, 5), затем щелкнуть кнопку Вставить и ввести значения полей новой записи, используя клавишу Tab перемещения к следующему полю. Задать следующие данные новой записи: Петров, Василий, 97, Тула, 5, 2. Завершить ввод данных, нажав Enter. Обратите внимание, что количество записей в таблице, отображаемое над кнопкой Добавить, увеличилось на 1.
12. Изменить запись об участнике из Ульяновска, для чего, щелкнув кнопку Критерии, в поле Регион ввести значение «Ульяновск» и нажать Enter, затем изменить данные в записи (например, измените фамилию Зуйков» на «Зайцев»). После изменения данных нажмите клавишу Enter да обновления текущей записи.
Примечание. Если поле списка содержит формулу, то в форме выводится ее результат. Изменять это поле в форме нельзя.
13. Для удаления записи об участнике из Ульяновска установить ее текущей, а затем щелкнуть кнопку Удалить. Подтвердить удаление записи, щелкнув кнопку ОК на панели сообщения Microsoft Excel.
Примечание. Чтобы отменить добавление записи, нажмите кнопку Вернуть перед нажатием клавиши Enter или кнопки Закрыть.
14. Нажать кнопку Закрыть для выхода из формы и просмотреть изменения, внесенные в нее в режиме формы.
15. Завершить работу Excel, сохранив таблицу под именем Список участников соревнований.
