Ссылки на ячейки другого листа
Создать таблицу учета продаж мороженого, в которой выполняется подсчет результатов продаж мороженого по кварталам и итоги продаж за год.
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, сохранив таблицу под именем Список участников соревнований.