Задание № 2. Работа с листами, связи между листами
1. Создайте новую рабочую книгу (Файл/Создать/Книгу).
2. Сохраните на своем диске под именем : Зарплата.xls.
3. На первом рабочем листе создайте таблицу Начисления
Ø Сформируйте таблицу, выполните вычисления по формулам, переименуйте рабочий лист – Начисления.
Ø Исходными данными являются: оклад сотрудников, вычеты по НДФЛ, размер премии (в процентах).
Ø Формулы вычислений:
Профс. налог = 1% от оклада (оклад * 0,01)
НДФЛ = (оклад – вычеты по НДФЛ)*0,13
Сумма к выдаче = оклад – налоги
| № | Фамилия, имя, отчество | Оклад | Налоги | Вычеты по НДФЛ | Сумма к выдаче | Премия | |
| Профс. | НДФЛ | ||||||
| Сидоров В.А. | |||||||
| Петров Р.М. | |||||||
| Логинова В.С. | |||||||
| Коноплёва Т.В. | |||||||
| Иванов В.А. | |||||||
| Громова Т.С. | |||||||
| Васильева А.В. |
4. На втором рабочем листе создайте таблицу Премия.
Ø Сформируйте таблицу Премия на основе таблицы Начисления. Для выполнения этого задания создадим связи между таблицами, по которым данные будут вставляться из листа “Начисления”.
| Ф.И.О. | Сумма |
В графе ФИО будет располагаться формула = Начисления!В3, где В3 – адрес ячейки, в которой размещена первая фамилия сотрудника на листе “Начисления”. Скопируйте формулу вниз, так чтобы скопировались фамилии всех сотрудников.
В графе Сумма будет располагаться формула = Начисления!G3 * Начисления!H3/100. Скопируйте формулу вниз, так чтобы произвести подсчет суммы по каждому сотруднику.
Ø Переименуйте рабочий лист – Премия.
5. На третьем листе создайте таблицу К выдаче
Ø Переименуйте лист из “Лист 3” в “К выдаче”
Ø Сформируйте заголовок таблицы.
| Ф.И.О. | Сумма | Подпись |
Ø Вставьте фамилии сотрудников со ссылкой на лист “Начисления”.
Ø Вычислите Сумму к выдаче, используя ссылки на лист “Начисления” и лист “Премия”.
Ø В графе Сумма будет располагаться формула = Начисления!G3 + Премия!B2.
Ø Скопируйте формулу вниз, так чтобы произвести подсчет суммы к выдаче по каждому сотруднику.
6. На четвертом рабочем листе создайте диаграмму.
Постройте круговую диаграмму, отражающую начисления каждого сотрудника, используя таблицу Начисления.
Последовательность выполнения задания:
Ø Выделить два столбца (ФИО и Сумма к выдаче).
Ø Запустить Мастер диаграмм (Меню Вставка / Диаграмма/ На новом листе).
Ø Передвигаться по шагам с мастером диаграмм.
Ø В результате вы должны получить диаграмму:
Лабораторная работа № 5 (MSExcel).
Тема: Работа со списками. Структурирование рабочих таблиц. Подведение итогов для данных, расположенных в смежных диапазонах.
Цель: использовать функции списка. Научиться создавать структуру таблицы, подводить итоги в смежных диапазонах.
Теоретические сведения
Список.
Список – это табличная база данных, которая содержится в листе рабочей таблицы. Такая база данных состоит из имен полей, которые расположены в первой строке и информационных записей, находящихся в следующих строках. В списке не должно быть пустых столбцов или пустых строк. Над списками можно выполнять следующие операции:
· Ввод данных в список.
· Фильтрация данных в списке – это выбор данных по заданному критерию (условию). Фильтрация данных осуществляется с помощью команды Данные/Фильтр. Имеются две разновидности этой команды, задаваемые параметрами: Автофильтр и Расширенный фильтр. Фильтрация данных может осуществляться с помощью специальной формы, которая вызывается командой Данные/Форма.
· Сортировка списка. Сортировка – это расположение данных в определенном порядке по возрастанию или убыванию. Сортировка выполняется командой Данные/Сортировка.
· Вставка формул для подведения промежуточных итогов.
· Создание с помощью сводной таблицы итоговой таблицы данных списка, отфильтрованного по определенным критериям.
· Создание с помощью сводной таблицы итоговой таблицы данных списка.
2. Структурирование.
Структурирование заключается в распределении данных по уровням иерархической структуры. Структуры используются для создания итоговых отчетов. Работая со структурами, нужно иметь в виду следующее:
· структуру можно создать автоматически или вручную;
· одна рабочая таблица может иметь только одну структуру (вертикальную, горизонтальную или обе);
· можно скрыть символы структуры (чтобы освободить пространство экрана), но сохранить при этом саму структуру;
· структура может иметь до восьми вложенных уровней.
2.1. Подготовка данных к преобразованию их в структуру.
Прежде всего, необходимо убедиться в том, данные подходят для создания структуры и формулы правильно заданы. Данные должны иметь иерархическую организацию, например, расчет бюджета строиться по следующей схеме:
Фирма
Подразделение
Отдел
Категория бюджета
Статья бюджета
Формулы должны быть введены правильно и согласованно. Если формулы не согласованы, т.е. одни формулы находятся под соответствующими ячейками, другие над ячейками, то при создании структуры не будет получен желаемый результат.
2.2. Создание структуры
· Автоматическое создание структуры.
Чтобы автоматически создать структуру для некоторого диапазона данных, поместите курсор в любую ячейку этого диапазона. После этого выберите команду Данные/Группа и Структура/Создание структуры. Программа проанализирует формулы в диапазоне и создаст структуру. Если формулы отсутствуют в диапазоне, то автоматически структура не будет создана.
· Создание структуры вручную
Процесс создания структуры вручную подразумевает создание групп строк (для вертикальной структуры) или групп столбцов (для горизонтальной структуры). Чтобы создать группу строк, выделите полностью все строки, которые нужно включить в эту группу. Затем выберите команду Данные/Группа и структура/Сгруппировать. После создания группы будет отображаться символ структуры.
3. Подведение промежуточных итогов в смежных диапазонах.
Осуществляется через команду меню Данные/Итоги.
Порядок выполнения работы
1. Ввести список «Периферия», представленный ниже.
| Товар | Тип | Наименование | Цена | Кол- во | Сумма |
| Сканер | Листовой | Paragon Page Easy | |||
| Сканер | Планшетный | Paragon 1200 SP | |||
| Сканер | Планшетный | ScanExpress A3 P | |||
| Принтер | Струйный | Epson Stylus Photo 700 | |||
| Принтер | Лазерный | HP LaserJet 4000 | |||
| Принтер | Лазерный | HP LaserJet Color 8500 | |||
| Сканер | Листовой | Paragon Page 630 | |||
| Сканер | Планшетный | Paragon 800IIEP | |||
| Принтер | Матричный | Epson LX-1050+ | |||
| Принтер | Лазерный | HP LaserJet 5000 | |||
| Сканер | Планшетный | ScanExpress 6000 SP | |||
| Принтер | Струйный | Epson Stylus-1500 | |||
| Принтер | Матричный | Epson LQ-2170 | |||
| Принтер | Матричный | Epson LQ-100 | |||
| Принтер | Струйный | Epson Stylus-1520 |
2. Подсчитайте сумму для каждого товара по формуле : Цена*Кол-во.
3. Примените к списку следующие команды, выбранные в меню Данные: Форма, Сортировка, Фильтр, Итоги.
Ход выполнения работы:
1. В меню Данные выберите команду Форма. Определите назначение каждой кнопки в окне. Введите еще две записи, нажимая кнопку Добавить. Нажав кнопку Критерии, установите в поле Сумма значение >300, переместитесь по записям, нажимая кнопки Назад и Далее (Вперед). Закройте окно Формы.
2. Отсортируйте записи по Товару в порядке возрастания, по полю Тип в порядке возрастания командой Данные/Сортировка.
3. Используя автофильтр Данные /Фильтр/Автофильтр, отберите записи:
· По полю Тип: Лазерные принтеры. Для этого перейдите на поле Тип. Раскройте список фильтрации и выберите Лазерные. На экране остались только данные по лазерным принтерам. Отмените фильтр (Данные /Фильтр/Отобразить все).
· По полю Товар: Сканеры. Выполнить самостоятельно! Отмените использование фильтра.
· 
По полю Кол-во: больше 3. Раскройте список фильтрации и выберите Условие. Выберите из списка оператор отношения «больше». В соседнем поле задайте значение 3. ОК.
Отмените использование фильтра.
4. 
используя Расширенный фильтр Данные /Фильтр/Расширенный фильтр найдите сканеры, которых больше 1. Для этого под таблицей запишем условия отбора Товар – Сканер, Кол – во > 1. Выполним команду Данные /Фильтр/Расширенный фильтр. Выполним обработку, как показано на рисунке ниже.
Результат:
5. Создайте промежуточные итоги командой Данные/Итоги.
5.1. Найдем суммы, затраченные отдельно на покупку всех принтеров и всех сканеров. Для этого:
1) Отсортируйте таблицу по товару.
2) В меню Данные выберите Итоги. Откроется диалоговое окно Промежуточные итоги.
3) Для того, чтобы подвести итоги по каждому товару (отдельно принтеры и отдельно сканеры), в списке При каждом изменении в выберите Товар
4) Убедитесь, что в окне Операция выбрана Сумма
5) Для того, чтобы просуммировать показатели количества товара и сумм, затраченных на покупку, установите флажки в поле Добавить итоги по напротив строк Кол-во и Сумма.
6) Проверьте, что напротив строк Заменить текущие итоги и Итоги под данными установлены флажки и нажмите ОК.
7) Таким образом, вы получите итоговые значения количества и суммы для каждого типа товара.
8) Для удаления промежуточных итогов выберите команду Данные-Итоги и щелкните кнопку Убрать все. Таблица вернется в исходное состояние.
5.2. Найдите среднее значение цены отдельно для всех принтеров и всех сканеров, а также общее среднее значение цены. Для этого:
1) Отсортируйте таблицу по товару.
2) Данные è Итоги èПромежуточные итоги:
3) При каждом изменении è Товар
4) Операция èСреднее
5) Добавить итоги по èЦена
6) В конце не забудьте убрать промежуточные итоги.
5.3. Вычислите число разновидностей принтеров и сканеров. Для этого
1) Отсортируйте таблицу по товару.
2) Данные è Итоги
3) Промежуточные итоги:
4) При каждом изменении è Товар
5) Операция èКол-во значений
6) Добавить итоги по èНаименование
7) В конце не забудьте убрать промежуточные итоги.
5.4. Самостоятельно вычислите среднее значение сумм, потраченных на покупку всех принтеров и всех сканеров.
Покажите результат преподавателю!!!
В конце не забудьте убрать промежуточные итоги.
5.5. Вычислите суммы, потраченные на покупку каждого типа товара (каждой разновидности принтеров и сканеров).
1) Отсортируйте таблицу по типу принтеров.
2) Данные è Итоги
3) Промежуточные итоги:
4) При каждом изменении è Тип
5) Операция èСумма
6) Добавить итоги по èСумма
6. Часто при составлении итогового отчета нужно видеть только итоги, а остальные записи скрыть. Для этого в колонках с кнопками 1 2 3 можно поменять знак - на +. Такое можно провести для каждого уровня.
Воспользуйтесь результатами предыдущего задания.
1) Скройте результаты второго уровня: Щелкните по кнопке 2.
2) Для возврата таблицы в исходный вид щелкните по кнопке того уровня, который вы хотите показать (кнопка 3 )
3) Попробуйте по очереди скрывать отдельные записи второго уровня (нажимайте на кнопку -)
4) Для возврата - на кнопку +.
5) Попробуйте поработать с кнопкой 1.
6) Верните таблицу в исходное состояние.
7) Уберите промежуточные итоги.
Лабораторная работа №6 (MSExcel)
Тема: Подведение итогов для данных, расположенных в несмежных диапазонах.
Цель: Научиться подводить итоги в несмежных диапазонах.
Теоретические сведения
Для подведения итогов данных, расположенных в несмежных диапазонах, например, на разных рабочих листах или в разных рабочих книгах используются команды Консолидация и Сводная таблица из меню Данные.
Задание
1. Введите следующие данные для создания сводной таблицы:
| Дата | Счет | Тип | Открыт | Отделение | Клиент |
| 01.09.99 | Текущий | Представитель | Центральное | Старый | |
| 01.09.99 | 15759,9 | Депозит | Кассир | Западное | Старый |
| 01.10.99 | 15367,60 | Депозит | Представитель | Северное | Старый |
| 03.10.99 | 12000,68 | Депозит | Представитель | Западное | Старый |
| 03.10.99 | 5000,00 | Срочный | Представитель | Центральное | Старый |
| 12.11.99 | 7000,00 | Текущий | Кассир | Северное | Новый |
| !3.11.99 | 90000,00 | Срочный | Представитель | Западное | Старый |
| 01.12.99 | 12000,00 | Текущий | Представитель | Северное | Старый |
| 01.12.99 | 10000,00 | Срочный | Представитель | Западное | Старый |
| 01.12.99 | 6900,00 | Депозит | Представитель | Центральное | Старый |
| 04.12.99 | 5500,00 | Срочный | Кассир | Северное | Старый |
| 04.12.99 | 3500,00 | Срочный | Представитель | Центральное | Старый |
Создайте сводные таблицы, отвечая на следующие вопросы:
· Какая общая сумма вклада для каждого из отделений и по каждому из типов счетов?
· Как распределена сумма по разным типам счетов?
· Какое отделение имеет больше Срочных счетов?
При использовании Мастера сводных таблиц (команда Данные> Сводная таблица) на первом шаге указывается источник данных. Для этой задачи нужно задать опцию «В списке или в базе данных Microsoft Excel».
На втором шаге Мастера определяется диапазон данных, данные из которого будут включены в сводную таблицу. В диапазоне данных не должно быть пустых строк или столбцов.
На третьем шаге Мастера определяется структура создаваемой таблицы. В центре окна расположена схема сводной таблицы. На схеме представлены следующие области:
· Строка. Значения поля используются в качестве заголовков строки в сводной таблице.
· Столбец. Значения поля используются в качестве заголовков столбцов в сводной таблице.
· Данные. Поле, для которого подводятся итоги в сводной таблице.
· Страница. Значения поля используются в качестве заголовков страниц в сводной таблице.
Поля базы данных (указанного диапазона ячеек) представлены в виде кнопок, которые расположены в столбце в правой части окна. В каждую из этих областей можно перетащить любое количество полей, все поля использовать необязательно. Неиспользованные поля не будут присутствовать в сводной таблице.
Например, установив указатель мыши на кнопку Счет, нажмите кнопку мыши и переместите в область Данные, затем переместите поле Тип в область Строка, поле Отделение в область Столбец.
Самостоятельно выберите размещение нужных полей на схеме. Если нужно использовать другую функцию, кроме суммирования, то нужно двойным щелчком мыши по области Данные вызвать список доступных функций и выбрать нужную.
На четвертом шаге Мастера установите параметры сводной таблицы, выделите опции Общие итоги по столбцам и Общие итоги по строкам.
Сохраните созданную таблицу на отдельном листе.
В созданной сводной таблице можно группировать определенные элементы поля. Например, в следующей задаче производится группировка дат по месяцам:
1. Сколько счетов обрабатывается по каждому из отделений за месяц?
В область Столбец поместите поле Дата, в область Строка поместите поле Отделение, в область Данные –поместите поле Счет. Для поля Счет выберите функцию Счет (Count). Затем, когда будет создана сводная таблица, сгруппируйте данные из поля Дата. Поместите указатель мыши на название поля Дата (или выделите ячейки, которые нужно сгруппировать), команда Данные/Группа и структура/Сгруппировать. В зависимости от типа выбранных данных для группировки появится соответствующее окно для выбора интервалов группировки. Например, для дат можно выбрать квартал, месяц и т.д. Выберите месяц.
2. Для работы с консолидируемыми данными:
2.1. Введите следующие данные, группируя рабочие листы .
Примечание. При необходимости добавьте в рабочую книгу рабочие листы, используя команду Вставка/ Лист или Добавить из контекстного меню Ярлычков (Установить указатель мыши на ярлычок рабочего листа и нажать правую кнопку мыши).
4.2. Переименуйте эти листы , дайте им соответствующие названия Магазин1, Магазин2, Магазин3, используя команду Переименовать из контекстного меню Ярлычков.
4.3.Затем объедините три листа в группу. Для этого нажмите клавишу Ctrl, и, удерживая ее нажатой, щелчком мыши выделите эти листы.
4.4.Затем введите данные. При вводе информации в группу, эта информация будет отображаться на всех листах группы. Затем разгруппируйте эти листы, выбрав в контекстном меню Ярлычков команду Разгруппировать. Немного измените данные на втором и третьем листах. Измените Код товара и Количество за любой месяц.
| Код товара | Янв. | Фев. | Мар. |
| А-145 | |||
| А-189 | |||
| А-195 | |||
| С-213 | |||
| С-415 | |||
| Е-10 | |||
| Е-400 | |||
| Е-456 | |||
| Е-790 |
3. Консолидируйте следующие данные, размещенные на трех рабочих листах с помощью команды Консолидация и с помощью команды Сводная таблица:
При использовании команды Данные>Сводная таблица на первом шаге Мастера сводных таблиц задайте опцию В нескольких диапазонах консолидации.
На втором шаге Мастера (2а) выберите опцию Создать одно поле страницы, на шаге (2b) последовательно задайте диапазоны данных, размещенные на трех рабочих листах.
Третий шаг Мастера оставьте по умолчанию, нажмите кнопку Далее, на четвертом оставьте изменения установки, сделанные по умолчанию.
При создании итоговой таблицы с помощью команды Данные >Консолидация перейдите на новый лист и выберите команду. В окне этой команды в списке Функция выберите нужную функцию, например, Сумм, затем поместите указатель мыши в поле Ссылка, затем щелкните мышью на ярлычке листа Магазин1 и выделите диапазон ячеек с данными для консолидации (выделяйте все данные , включая заголовки), вернитесь на лист , где создается консолидация, нажмите кнопку Добавить, затем укажите на данные листа Магазин2, нажмите кнопку Добавить, потом – на данные листа Магазин3 и кнопку Добавить. В окне команды Консолидация установите опции «Подписи верхней строки», «Значения левого столбца», «Создавать связи с исходными данными».
Сравнить полученные итоговые таблицы. Результаты предъявить преподавателю.