Расчёты в электронных таблицах Ms Excel

Размещено на http://www.allbest.ru/

МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ

РОССИЙСКОЙ ФЕДЕРАЦИИ

ВЫБОРГСКИЙ ФИЛИАЛ ГОСУДАРСТВЕННОГО ОБРАЗОВАТЕЛЬНОГО УЧРЕЖДЕНИЯ ВЫСШЕГО ПРОФЕССИОНАЛЬНОГО ОБРАЗОВАНИЯ

«САНКТ-ПЕТЕРБУРГСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ СЕРВИСА И ЭКОНОМИКИ»

КАФЕДРА «ГУМАНИТАРНЫЕ И ЕСТЕСТВЕННОНАУЧНЫЕ ДИСЦИПЛИНЫ»

Т.П. Бокучава

ИНФОРМАТИКА

Расчёты в электронных таблицах Ms Excel

Лабораторный практикум

для студентов всех специальностей и направлений

Выборг-2012

Одобрено на заседании кафедры «Гуманитарные и естественно-научные дисциплины», протокол № 6 от 30.01.2012 г.

Одобрен и рекомендован к изданию Учебно-методическим советом СПбГУСЭ, протокол № __ от ________2012 г.

Бокучава Т.П. Информатика. Расчёты в электронных таблицах MS Excel. Лабораторный практикум для студентов всех специальностей и направлений / Т.П. Бокучава, - СПб.: Изд-во СПбГУСЭ, 2012. - 52 с.

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

ВВЕДЕНИЕ

Данный лабораторный практикум включает задания по разделу «Электронные таблицы» дисциплины «Информатика» и предназначен для студентов первого курса всех специальностей и всех форм обучения. Содержание практикума отвечает требованиям ФГОС ВПО.

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

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

Методические указания даны достаточно подробно, чтобы студент смог справиться с заданием как в аудитории в присутствии преподавателя, так и при самостоятельной проработке темы.

Особенностью настоящего практикума является, во-первых, изложение в терминах современного пакета MS Office 2007/2010, во-вторых, многовариантность заданий: каждая лабораторная работа включает 30 вариантов однотипных заданий.

КРАТКО ОБ ИНТЕРФЕЙСЕ MICROSOFT EXCEL 2007

Основные элементы интерфейса окна MS Excel - это лента, содержащая вкладки, кнопка Office (расположена в левом верхнем углу окна), панель быстрого доступа (расположена рядом с кнопкой) - см. Рис.1.

Рис.1 MS Excel: лента Главная

Лента занимает область в верхней части окна. В верхней строке ленты идут названия вкладок. Выбрав какую-либо вкладку, получаем доступ к необходимым инструментам, представленных в виде групп связанных команд и значков. Если нужной команды не оказывается на панели, то ее можно найти, нажав на небольшую стрелочку в правом нижнем углу определенной группы. При этом всплывает подсказка, которая информирует о предназначении инструментов.

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

Рис.2 Меню кнопки Office

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

Рис.3 Панель быстрого доступа

Чтобы изменить состав панели быстрого доступа, выберите пункт меню "Другие команды..". Откроется окно настроек - Параметры Excel. Нужный раздел "Настройка" при этом будет выбран по умолчанию (рис.4).

Для добавления нужной команды в панель быстрого доступа необходимо ее выделить в левой части списка и нажать кнопку "Добавить". Для удаления ненужной команды, ее надо выделить в правом списке и нажать кнопку "Удалить". Кроме того, здесь же можно указать, будет ли панель иметь заданный вид при открытии всех документов, выбрав из списка пункт "Для всех документов", или только для определенного документа.

Рис.4 Настройка Панели быстрого доступа

Стартовое окно программы Microsoft Excel 2007 содержит три пустых листа рабочей книги. Листы можно добавлять в книгу и удалять. Максимальное количество листов не ограничено, минимальное - один лист.

Выбрать режим просмотра листа можно при работе в любой вкладке Excel 2007. Ярлыки выбора основных режимов просмотра книги расположены в правой части строки состояния (рис.5). Если ярлыки не отображаются, щелкните правой кнопкой мыши в любом месте строки состояния и в появившемся контекстном меню выберите команду Ярлыки режимов просмотра листа.

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

Рис.5 Выбор режима просмотра листа

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

В режиме Страничный в окне отображается только собственно сама таблица. Остальные ячейки листа не отображаются. Зато отображены границы страниц.

Во вкладке Вид в группе Режимы просмотра книги можно выбрать еще один режим просмотра - Во весь экран. Этот режим обеспечивает скрытие большинства элементов окна для увеличения видимой части документа.

На этом описание общих принципов работы с интерфейсом MS Excel 2007 мы закончим.

УКАЗАНИЯ ПО ВЫПОЛНЕНИЮ ЛАБОРАТОРНЫХ РАБОТ

Методические указания имеют следующую структуру:

Формулировка цели работы

Задание на выполнение работы по пунктам (содержание)

Методика (технология) выполнения работы (на примере разбирается решение)

Вопросы для самоконтроля

Индивидуальное задание по вариантам (всего 30 вариантов).

Рассмотренные в методических указаниях в качестве примеров работы должны быть выполнены студентом. Только после этого студент самостоятельно делает работу в соответствии с выбранным вариантом. Выполненные лабораторные работы сохраняются в каталоге студента на компьютере.

ЛАБОРАТОРНАЯ РАБОТА № 1

Тема: Microsoft Excel: создание, заполнение и форматирование таблиц

Цели: получить первоначальные практические навыки создания электронной таблицы, ввода в нее данных и форматирования средствами MS Excel .

Содержание работы:

1. На первом листе заполнить таблицу (не менее 5 строк). Имеющиеся в шапке таблицы данные (года, месяцы, дни недели) заносить с помощью приема автозаполнения.

2. Оформить таблицу c помощью обрамления, добавить заголовок, расположив его по центру таблицы, шапку таблицы набрать полужирным шрифтом, первый столбец - курсивом. Шапку и первый столбец выполнить в цвете (шрифт и фон).

3. Переименовать лист книги, задав имя Таб-1-Иванов.

4. Скопировать таблицу на второй лист книги, дальнейшие действия выполнять на втором листе книги.

5. Добавить 2 столбца в начало таблицы. В первом проставить с помощью автозаполнения № п/п, во втором с помощью заполнения ввести числа с шагом, равным номеру задания.

6. Отсортировать таблицу по содержимому третьего столбца таблицы (по наименованию товара).

7. Переименовать второй лист книги (Таб-2-Иванов).

8. Сохранить таблицу под своим именем - Лаб-1-Иванов.

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

Технология выполнения заданий на примере таблицы (рис.9)

1. Основные принципы ввода и редактирования данных изложены в любом учебном пособии. Рассмотрим более подробно только некоторые моменты. Текст "Наименование товара", который мы вводим в ячейку А3, целиком в этой ячейке не поместился (см. рис. 6). Поскольку в ячейку В3 не было введено никакой информации, текст виден полностью. При вводе в ячейку В3 текста "Стоимость…", текст в А3 будет виден частично, в пределах границ столбца А.

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

Рис.6

Изменить ширину столбца можно несколькими способами:

1) Пометить столбец (столбцы). Лента Главная вкладка Ячейки - инструмент Формат-Ширина столбца. В появившемся окне указать нужную ширину столбца.

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

3) Выделить нужные столбцы и протащить с помощью мыши правую границу заголовка столбца до тех пор, пока столбец не станет нужной ширины (Рис.7).

Рис.7

Чтобы текст в ячейке располагался в несколько строчек, необходимо в контекстном меню выбрать команду Формат Ячеек, выбрать вкладку Выравнивание, и воспользоваться опцией "Переносить по словам".

Текст "Продано за 1 полугодие текущего года (т)" необходимо расположить по центру над столбцами, содержащими сведения о продаже по месяцам, в нашем случае это столбцы D - I. Для этого необходимо выделить ячейки D3 - I3. Затем - Лента Главная вкладка Выравнивание, и щелкнуть по кнопке "Объединить и поместить в центре"  на панели.

Остановимся на занесении в таблицу названий месяцев. Для автоматизации ввода часто повторяющихся последовательностей данных (дни недели, названия месяцев, и т.д.) в MS Excel применяется прием автозаполнения. Для этого надо:

1) Ввести название первого элемента (в нашем случае - название месяца), например, Январь, нажать клавишу <Enter>.

2) Перетащить маркер заполнения в нужном направлении на число ячеек, равное количеству элементов, которые вы хотите включить в таблицу.

Результат выполнения первого пункта задания - см. рис.8

Рис.8

2. Для оформления заголовка необходимо ввести в ячейку А1 заголовок и отцентрировать его.

Для обрамления таблицы необходимо выделить всю таблицу и выбрать на ленте Главная вкладку Шрифт, открыть окно кнопки Границы и выбрать опцию Все границы - .

Изменение размера, цвета шрифта и фона выполняется с помощью команд вкладки Шрифт на ленте Главная.

3. Чтобы переименовать лист книги, достаточно вызвать контекстное меню (щелкнув правой кнопкой мыши на ярлыке листа) и выбрав пункт Переименовать, ввести новое имя для листа.

4. Чтобы скопировать таблицу на другой лист, воспользуйтесь способом перетаскивания при нажатой клавише Ctrl.

5. Для добавления столбцов: выделите два столбца - А, В и выберите на ленте Главная вкладку Ячейки, откройте окно кнопки Вставить и выберите пункт Вставить столбцы на лист.

В ячейку А5 введите 1, в ячейку А6 - 2. Выделите две ячейки - А5, А6 и используя прием автозаполнения введите числа в ячейки А7-А9.

В ячейку В5 введите 1, в ячейку В6- номер вашего варианта (например, 30), далее используя прием автозаполнения введите числа в ячейки В7-В9.

6. Чтобы отсортировать таблицу по наименованию товаров, надо выделить диапазон (в нашем случае С5:K9). Нажмите кнопку "Сортировка и фильтр" на панели Редактирование ленты Главная. Выберите "Сортировка от А до Я". Наш список будет отсортирован . по полю Наименование товара.

Рис.9 Заполненная и отформатированная таблица к Лаб/р №1

Вопросы для самоконтроля

1. В каких случаях можно использовать автозаполнение. Продемонстрировать некоторые приемы автозаполнения.

2. Как расположить текст в ячейке в несколько строк?

3. Как можно объединять ячейки?

4. Как можно удалять и добавлять строки (столбцы)?

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

6. Как отсортировать таблицу по содержимому какого-либо столбца?

7. Как можно изменить формат ячейки (группы ячеек)?

Задания по вариантам к лабораторной работе №1

Вариант №1 Отчет по заболеваемости

Вариант №2 Отчет по выпущенной продукции

Вариант №3 Отчет о приросте населения

Вариант №4 Отчет о посещаемости

Вариант №5 Отчет по травмированности работников шахт

Вариант №6 Отчет по выданным ссудам

Вариант №7 Отчет о среднем заработке

Вариант №8Отчет о количестве проданных путевок

Вариант № 9 Отчет о добычи угля бригадами

Вариант №10 Отчет о перелетах

Вариант №11 Отчет о поставках изделий

Вариант №12 Отчет о межгородских переговорах

Вариант №13 Отчет о продажах

Вариант №14 Отчет по заболеваемости в цехах

Вариант №15 Отчет о затратах на питание больных

Вариант №16 Отчет о выпуске деталей

Вариант №17 Распределение рекламного времени по радиостанциям

Вариант №18 Отчет о продажах товаров

Вариант №19 Отчет о продажах книг по отраслям

Вариант №20 Отчет о перелетах

Вариант №21 Отчет о продажах мебели

Вариант №22 отчет о прибылях банков

Вариант №23 Отчет по продажам авиабилетов

Вариант №24 Отчет о реализации автомобилей

Вариант №25 Отчет о расходах на лечение заболеваний

Вариант №26 Демографический отчет

Вариант №27 Отчет по продажам компьютеров

Вариант №28 Отчет об объеме продаж магазинами

Вариант №29 Отчет о заключенных контрактах

Вариант №30 отчет о продажах телевизоров

ЛАБОРАТОРНАЯ РАБОТА № 2

Тема: Редактирование таблиц, расчет по формулам средствами MS EXCEL

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

Краткие теоретические сведения

При копировании формул в Excel все адреса изменяются (смещаются) относительно расположения исходной ячейки. Такие адреса (ссылки) называются относительными. Если ссылка на ячейку (ячейки) не должна изменяться ни при каком копировании, то вводят абсолютный (фиксированный) адрес ячейки (ячеек). Абсолютная ссылка создается из относительной путем вставки знака доллара ($) перед заголовком столбца и/или номером строки. Например, $A$1 - это абсолютный адрес ячейки А1, $A$1:$B$3 - абсолютный адрес диапазона ячеек A1:B3.

Содержание работы:

Продолжаем работать с таблицей, созданной в предыдущей лаб/р. Используя данные таблицы из лаб/р №1, найти:

1. Сумму продаж всех продуктов за каждый месяц текущего года (в тоннах).

2. Сумму продаж всех продуктов за каждый месяц текущего года (в руб.).

3. Сумму продаж каждого продукта за I полугодие текущего года (в тоннах).

4. Сумму продаж каждого продукта за I полугодие текущего года (в руб.).

5. Среднее количество продаж каждого продукта за I полугодие текущего года (в тоннах).

6. Минимальное количество продаж каждого продукта за I полугодие текущего года (в тоннах).

7. Прирост продаж каждого продукта по сравнению с предыдущим полугодием (в %).

Примечание

При выполнении данной работы используются следующие функции:

1. Математические:

СУММ - сумма аргументов

СУММПРОИЗВ - сумма произведений соответствующих массивов (диапазонов)

2. Статистические:

СРЗНАЧ - среднее арифметическое аргументов

МАКС - максимальное значение из списка аргументов

МИН - минимальное значение из списка аргументов

Технология выполнения заданий на примере таблицы (рис.9)

1. Чтобы найти сумму продаж всех продуктов за каждый месяц текущего года (в тоннах), достаточно применить функцию суммирования для соответствующих диапазонов. Начнем с января: выделим диапазон F5:F9 и нажмем значок автосуммирования ? на вкладке Редактирование ленты Главная. Далее скопируем полученную формулу в ячейки G9 - K9, используя прием автозаполнения. Объединим ячейки A10 - E10 и введем поясняющую подпись: «Сумма продаж по месяцам (в тоннах)». Отформатируем получившиеся данные: для ячеек F10-K10 зададим размер шрифта 12 пт., для ячеек А10-Е10 - 14 пт, для всего диапазона А10-K10 - выравнивание в ячейке по вертикали - по центру (Рис.10).

Рис.10

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

(D5*F5 + D6*F6 + D7*F7 + D8*F8 + D9*F9) * 1000

Но это громоздко и неграмотно. Правильнее будет воспользоваться функцией СУММПРОИЗВ, для чего:

активизируем ячейку F11, нажмем Shift+F3 или щелкнем по кнопке fx (вставить функцию) yна вкладке Библиотеки функций линейки Формулы

в диалоговом окне выберем функцию СУММПРОИЗВ, ОК

далее укажем в диалоге нужные массивы (диапазоны) - см. рис.11. Знаки доллара для диапазона D5:D9 означают фиксированные ссылки (при копировании формул фиксированные адреса не изменяются, а ссылка на этот диапазон не должна изменяться, в отличие от ссылки на диапазон F5:F9). При копировании формулы в ячейки G11:K11 ссылка F5:F9 будет соответственно изменяться на G5:G9, H5:H9 и т.д.

Рис.11

таким образом полученную формулу, осталось умножить на 1000 - установим курсор в строку формул и впечатаем окончание формулы (рис.12)

полученную формулу копируем в ячейки G11:K11, чтобы получить результаты для месяцев февраль - июнь текущего года (рис.12).

Объединим ячейки A11 - E11 и введем поясняющую подпись: «Сумма продаж по месяцам (в рублях)». Отформатируем последнюю строку в таблице подобно предыдущему заданию - см. образец - рис.12.

Рис.12

3. Чтобы найти сумму продаж каждого продукта за I полугодие текущего года (в тоннах), достаточно применить функцию суммирования для соответствующих диапазонов. Начнем с товара «Сахар»: выделим диапазон F5:K5 и нажмем значок автосуммирования ? на вкладке Редактирование ленты Главная. Далее скопируем полученную формулу в ячейки L6 - L9, используя прием автозаполнения. В ячейку L3 введем поясняющую надпись: «Сумма продаж за I полугодие (т)». Отформатируем получившиеся данные по образцу - рис.13.

Рис.13

4. Чтобы получить сумму продаж в рублях за полугодие, надо в ячейку M5 ввести формулу D5*L5*1000. Установим курсор в ячейку M5 и введем знак равенства = (с этого знака должна начинаться любая формула). Введем формулу и скопируем ее во весь нужный диапазон. Введем поясняющую надпись и отформатируем получившиеся данные по образцу - см. рис.14.

Рис.14

5-6 пункты выполните аналогично пункту 4. Подсказка: в ячейку N5 введите формулу =СРЗНАЧ(F5:K5), а в ячейку О5 =МИН (F5:K5).

7. Чтобы найти прирост продаж для товара «Сахар», надо в ячейку P5 ввести формулу:

(продажи_за_1_полугодие_прошлого_года/продажи_за_Iполугодие_текущего_года - 1), т.е. =L5/Е5 - 1

Обязательно для ячейки Р5 задайте процентный формат, число десятичных знаков после запятой - 1.

Получившаяся таблица будет иметь вид как на рис.15.

Рис.15

Вопросы для самоконтроля

1. Что такое относительный адрес ячейки, абсолютный адрес и чем они отличаются?

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

Задания по вариантам к лабораторной работе №2

Примечание Работа выполняется на основе данных, полученных при выполнении лабораторной работы №1

Таблица 1 Задания по вариантам к л/р №2

№ варианта	Задание (что необходимо найти)
1	1. Общее число больных за каждый месяц текущего года. 2. Среднее число больных за каждый месяц текущего года. 3. Максимальное и минимальное число больных за 1 полугодие текущего года по каждой болезни. 4. Прирост заболевания каждой болезнью по сравнению с предыдущим полугодием.
2	1. Общее количество изделий за каждый день текущей недели. 2. Среднее количество изделий за каждый день текущей недели. 3. Минимальное количество каждого изделия за текущую неделю. 4. Прирост выпуска каждого изделия по сравнению с предыдущей неделей.
3	1. Количество жителей на 1.01.2013 г. по каждому городу. 2. Максимальный прирост населения за пятилетие по каждому городу. 3. Средний прирост населения за пятилетие по каждому городу. 4. Общий прирост населения по всем городам за каждый год.
4	1. Среднее количество пропущенных занятий каждым студентом. 2. Общее количество пропущенных занятий всеми студентами за каждый семестр. 3. Минимальное и максимальное количество пропущенных занятий за каждый семестр. 4. Количество максимальных пропусков занятий в каждом семестре.
5	1. Общее число травмированных работников за каждый квартал. 2. Среднее число травмированных работников за каждый квартал. 3. Общее число травмированных работников за год по каждой шахте. 4. % травмированных работников по каждой шахте за год.
6	1. Общую сумму выданных ссуд за каждый год. 2. Среднюю сумму выданных ссуд за каждый год. 3. Сумму полученных процентов каждым банком за пять лет (в руб.). 4. Сумму полученных процентов всеми банками за каждый год (в руб.).
7	1. Общую сумму з/платы каждой фирмы за пять лет. 2. Среднюю з/плату работника каждой фирмы за пять лет. 3. Общую сумму з/платы рабочих всех фирм за каждый год. 4. Миним-ую и максим-ую среднюю з/плату работников за пять лет.
8	1. Среднее количество путевок в каждую страну за полугодие. 2. Общую стоимость путевок в каждую страну за полугодие. 3. Общее количество путевок по месяцам. 4. Общую стоимость путевок во все страны по месяцам.
9	1. Суммарную добычу всех бригад за каждый день недели. 2. Среднее количество угля, добываемое 1 рабочим каждой бригады за неделю. 3. Среднее количество угля, добываемое бригадами в каждый день недели. 4. Минимальную и максимальную добычу угля за неделю каждым рабочим.
10	1. Общее количество пассажиров, перевезенных в целом во все города за каждый месяц. 2. Стоимость проданных билетов в каждый город за полугодие. 3. Общую стоимость всех билетов за каждый месяц. 4. Среднее количество перевезенных пассажиров во все города за каждый месяц.
11	1. Среднее количество изделий за полугодие по каждому наименованию. 2. Общее количество всех изделий за каждый месяц. 3. Общая стоимость изделий по каждому виду за полугодие. 4. Стоимость всех изделий за каждый месяц.
12	1. Среднюю продолжительность переговоров с каждым городом за неделю. 2. Стоимость переговоров за неделю с каждым городом. 3. Общую стоимость переговоров со всеми городами за каждый день недели. 4. Общую и среднюю продолжительность переговоров по дням недели.
13	1. Заработок каждого продавца за полугодие. 2. Общий заработок всех продавцов за каждый месяц. 3. Сумму продажи товаров всеми продавцами за каждый месяц. 4. Среднюю сумму продажи товаров всеми продавцами за каждый месяц.
14	1. Сумму выплат по больничным листам по каждому цеху. 2. Сумму выплат по больничным листам по всем цехам за каждый месяц. 3. Общее и среднее количество дней болезни за каждый месяц. 4. Минимальное и максимальное количество дней болезни за полугодие по заводу.
15	1. Общее количество койко-дней за каждый месяц. 2. Среднее количество койко-дней за каждый месяц и в целом за полугодие. 3. Стоимость питания для каждого отделения за полугодие. 4. Стоимость питания за каждый месяц по всем отделениям.
16	1. Общее количество деталей за каждый день недели. 2. Среднее количество изделий за каждый день недели. 3. Минимальное и максимальное количество деталей для каждого рабочего за неделю. 4. Прирост выпуска деталей каждым рабочим по сравнению с предыдущей неделей.
17	1. Минимальное и максимальное время рекламы за текущий год для каждой радиостанции. 2. Сумму рекламного времени по месяцам и в целом за полугодие. 3. Прирост рекламного времени за текущий год каждой радиостанции по сравнению с предыдущим годом. 4. Среднее количество рекламного времени по месяцам и за год.
18	1. Общее количество проданного товара каждого наименования за текущий год. 2. Среднее количество проданного товара каждого наименования за текущий год. 3. Сумму продажи каждого товара за предыдущий и текущий год. 4. Общую сумму продажи всех товаров за каждый квартал.
19	1. Минимальную и максимальную продажи в декабре. 2. Общее количество проданных книг за каждый месяц 2-го полугодия. 3. Среднее количество проданных книг за каждый месяц 2-го полугодия. 4. % прироста продажи книг каждой отрасли по сравнению с 1-м полугодием.
20	1. Общее количество рейсов в каждый город за полугодие. 2. Общее количество пассажиров, перевезенных в каждый город за полугодие. 3. Общее количество пассажиров, перевезенных во все города за каждый месяц. 4. Среднее количество рейсов во все города за каждый месяц.
21	1. Общее количество проданной мебели по каждому наименованию. 2. Среднее количество проданной мебели по каждому наименованию. 3. Сумму продажи по каждому наименованию за полугодие. 4. Сумму продажи всей мебели за каждый месяц и в целом за полугодие.
22	1. Сумму (тыс. руб.) на 1.01.2008 г. по каждому банку. 2. Минимальную и максимальную прибыль по каждому банку за 5 лет, количество банков с максимальной прибылью. 3. Общую прибыль всех банков за каждый год. 4. Среднюю прибыль всех банков за каждый год.
23	1. Среднее количество пассажиров, перевезенных в каждую страну. 2. Количество пассажиров, перевезенных каждый месяц во все страны. 3. Стоимость проданных билетов в каждую страну за полугодие. 4. Стоимость всех билетов за каждый месяц.
24	1. Количество проданных автомобилей каждой марки за 2-е полугодие и за год. 2. Выручку от продажи каждой марки автомобиля за год. 3. Выручку от продажи всех автомобилей за каждый месяц 2-го полугодия. 4. Среднее количество проданных автомобилей за каждый месяц.
25	1. Среднюю стоимость 1 дня лечения каждой болезни за 5 лет. 2. Стоимость лечения каждой болезни в 2008 и 2012 годах. 3. Минимальное и максимальное стоимость лечения в 2011 году. 4. Общую стоимость лечения всех болезней по годам.
26	1. Общее количество родившихся и умерших за каждый квартал. 2. Среднее количество родившихся и умерших по районам. 3. Прирост населения по районам. 4. Минимальное и максимальное количество родившихся за год по всем районам.
27	1. Среднее число проданных компьютеров за квартал по каждой фирме. 2. Общее число компьютеров, проданных всеми фирмами, за каждый квартал. 3. Количество проданных компьютеров одним продавцом каждой фирмы. 4. Минимальное и максимальное количество проданных компьютеров каждой фирмой за год.
28	1. Общую сумму реализации за каждый день текущей недели. 2. Минимальный и максимальный объем реализации каждого магазина за текущую неделю. 3. Средний объем реализации каждого магазина за текущую неделю. 4. Прирост объема реализации каждого магазина по сравнению с предыдущей неделей.
29	1. Общее количество контрактов для всех фирм по каждому месяцу. 2. Минимальное и максимальное количество контрактов в текущем году для каждой фирмы. 3. Среднее количество контрактов за полугодие для каждой фирмы. 4. Прирост количества контрактов по сравнению с предыдущим годом.
30	1. Минимальное, максимальное и среднее количество проданных телевизоров по каждому наименованию. 2. Сумму продажи всех телевизоров за полугодие. 3. Стоимость одного телевизора каждой марки. 4. Прирост продажи телевизоров в июне по сравнению с январем.

ЛАБОРАТОРНАЯ РАБОТА № 3

Тема: Microsoft Excel: табулирование функции, заданной условно и построение графика заданной функции

Цели: ознакомиться с логической функцией ЕСЛИ и получить первоначальные практические навыки в построении графиков средствами MS Excel.

Содержание работы:

1. На первом листе заполнить таблицу согласно формуле (протабулировать функцию, заданную условно). При выполнении данного пункта будет использоваться встроенная условная функция =ЕСЛИ(логическое_выражение;значение_если_истина;значение_если_ложь).

2. Для полученных данных построить график. Отформатировать полученный график, задав подписи по оси х и, если надо, изменить минимальное и максимальное значения вертикальной оси.

3. Сохранить таблицу под своим именем - Лаб-3-Иванов.

4. На втором листе выполнить задание своего варианта.

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

Технология выполнения заданий

Рассмотрим выполнение задания на примере. Функция задана условно:

Таким образом, надо протабулировать функцию, которая зависит от х, значения х меняются от -0,4 до 1,6 с заданным шагом 0,2.

1. В ячейки А1, В1 введите соответствующие надписи - X, Y. В ячейку А2 введите -0,4, в ячейку А3 - -0,2 и, используя прием автозаполнения, введите значения для Х в диапазон А4:А12.

В ячейку В2 введите формулу для вычисления функции Y для первого значения Х (ячейка А2).. Формула будет иметь вид:

=ЕСЛИ(A2<0;0;ЕСЛИ(A2>1;1;A2))

Полученную формулу скопируйте в диапазон В3:В12. Для диапазона В2:В12 установите формат ячеек: Числовой - Число десятичных знаков - 1. Оформите получившуюся таблицу, задав границы и размер шрифта - 12 пт. (см. рис.16)

Рис.16

2. Чтобы построить график надо:

выделить фрагмент таблицы, для которого создается график - это значения функции, т.е. диапазон В2:В12;

на ленте Вставка в группе Диаграммы щелкнуть по кнопке с нужным типом диаграмм, в нашем случае - График, получим график примерно как на рис.17.

Рис.17

Далее форматируем график. Легенду удаляем. Изменяем, при необходимости, минимальное и максимальное значения вертикальной оси, для этого надо:

вызывать контекстно-зависимое меню для вертикальной оси Формат оси;

минимальное значение задать равное -0,5, максимальное в данном случае, не изменяем;

закрыть диалоговое окно;

Добавим подписи для горизонтальной оси, для этого надо:

выделить область подписей, активизировать ленту Конструктор и щелкнуть на кнопке Выбрать данные. Появится диалоговое окно - рис.18

Рис.18

В правой части диалогового окна щелкнуть на кнопку Изменить и в диалоге Диапазон подписей оси мышкой выделить диапазон в таблице А2:А22. Далее ОК, ОК.

Теперь изменим формат появившихся подписей, для этого надо:

вызвать контекстно-зависимое меню горизонтальной оси Формат оси;

выбрать вкладку Выравнивание, в диалоговом окне изменить два параметра: Направление текста установить горизонтальное, Другой угол - 0 градусов; закрыть диалоговое окно.

В редакторе Word создайте формулу вашего варианта и скопируйте эту формулу на область диаграммы в Excel, предварительно уменьшив область построения. У вас должен получиться график с подписью в виде формулы и все это должно быть максимально похожим на изображение рис.19.

Сохраните работу под своим именем - Лаб-3-Иванов

Рис.19

Вопросы для самоконтроля

1. Как построить диаграмму (график) в Excel?

2. Как отформатировать вертикальную (горизонтальную) оси?

Задания по вариантам к лабораторной работе №3

Таблица 2 Задания по вариантам к л/р №3

№ варианта	Функция
1, 21	для x= -2 ч 2, шаг = 0,1
2, 22	для x= -2 ч 2, шаг = 0,1
3, 23	для x= -10 ч 10, шаг = 0,1 а = 3
4, 24	для x= -10 ч 10, шаг = 0,2
5, 25	для x= -10 ч 10, шаг = 0,1 а = 2
6, 26	для x= -2 ч 2, шаг = 0,1
7, 27	для x= -2 ч 2, шаг = 0,1 b = 0,5
8, 28	для x= -12 ч 12, шаг = 0,2 a = 3
9, 29	для x= -2 ч 2, шаг = 0,1
10, 30	для x= -20 ч 20, шаг = 0,4
11	для x= -12 ч 12, шаг = 0,2
12	для x= -10 ч 10, шаг = 0,1
13	для x= 0 ч 20, шаг = 0,1
14	для x= 0 ч 20, шаг = 0,1
15	для x= 0 ч 8, шаг = 0,1
16	для x= 0 ч 20, шаг = 0,2 a = 4
17	для x= -1 ч 12, шаг = 0,1 b = 5
18	для x= -10 ч 10, шаг = 0,2
19	для x= -12 ч 12, шаг = 0,2
20	для x= -12 ч 12, шаг = 0,2 b = 2

ЛАБОРАТОРНАЯ РАБОТА № 4

excel таблица формула расчет

Тема: Сортировка и фильтрация данных в Excel

Цели: Изучение возможностей MS Excel по организации сортировки и фильтрации данных, использование логических операций при организации фильтрации.

Краткие теоретические сведения

Операции сортировки и фильтрации в MS Excel выполняются над списками.

Список - это упорядоченный набор данных, база данных на рабочем листе.

Столбцы списка называются полями, строки - записями. Столбцы списка должны содержать однородную информацию (например, только даты, только текстовые строки).

Над списками можно выполнять такие операции, как фильтрация и сортировка.

В процессе сортировки списка переупорядочиваются строки в соответствии с содержимым одного, двух или более столбцов.

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

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

Фильтр бывает двух типов - обычный (Автофильтр) и расшитенный.

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

Содержание работы:

1. Загрузить программу Excel. На листе рабочей книги (Лист1) создать таблицу с исходными данными (см. рис.20). Сохранить таблицу под именем Кадры.xlsx и под именем Кадры-1.xlsx (второй файл будет использован в л/р №5).

2. Открыть файл Кадры.xlsx, скопировать таблицу «Кадры» на листы 2, 3 и 4.

3. В рабочей книге Кадры.xlsx на листе 1 отсортировать данные таблицы “Кадры” по полю «Пол».

4. На листе 2 Выполнить многоуровневую сортировку по двум ключам: сначала по полю «Фамилия», потом по полю «Оклад» в порядке возрастания значений этих ключей.

5. На листе 3 использовать автофильтр для вывода информации только о работниках отдела АПС. На этом же листе использовать автофильтр для вывода информации только о работниках отдела АПС с окладом большим чем 12000 руб., но меньшим, чем 22000 руб.

6. На листе 4 использовать расширенный автофильтр для вывода информации о работниках родившихся до 1989 г. и имеющих оклад менее 20000 руб. В отчете отобразить поля «Фамилия», «Имя», «Отчество», «Дата рождения», «Отдел» и «Оклад».

Рис.20

Технология выполнения заданий

1. Основные приемы по созданию и заполнению таблиц средствами MS Excel рассмотрены в лабораторной работе №1.

2. Чтобы скопировать таблицу на другой лист, воспользуйтесь способом перетаскивания при нажатой клавише Ctrl.

3. Чтобы отсортировать таблицу по полю «Пол», надо:

установить курсор мыши внутри таблицы и нажать кнопку Сортировка на панели Сортировка и фильтр ленты Данные;

в диалоговом окне Сортировка (рис.21) выбрать параметр Пол, порядок сортировки от А до Я, ОК.

Рис.21

4. Перейдите на лист 2 Выполните сортировку по двум ключам: сначала по полю «Фамилия», потом по полю «Оклад» в порядке возрастания значений этих ключей. Для этого надо:

установить курсор мыши внутри таблицы и нажать кнопку Сортировка на панели Сортировка и фильтр ленты Данные;

в диалоговом окне Сортировка выбрать параметр Фамилия, порядок сортировки от А до Я;

щелкнуть на кнопке Добавить уровень и выбрать параметр Оклад, порядок сортировки от А до Я.

5. Перейдите на лист 3. Используйте Автофильтр для вывода информации о работниках отдела АПС, для этого надо:

установить курсор мыши внутри таблицы и нажать кнопку Фильтр на панели Сортировка и фильтр ленты Данные; В столбцах списка появятся кнопки со стрелочками, нажав на которые можно настроить параметры фильтра (рис.22);

установить фильтр по полю Отдел, выбрав отдел АПС;

Поля, по которым установлен фильтр, отображаются со значком воронки. Если подвести указатель мыши к такой воронке, то будет показано условие фильтрации. Чтобы отменить фильтр, достаточно повторно нажать на кнопку Фильтр.

Рис.22

Примените более сложный фильтр для отображения информации о работниках отдела АПС с окладом больше 12000 руб., но меньше 22000 руб., для этого надо:

установите фильтр по полю Отдел, выбрав отдел АПС;

по полю Оклад выберите в диалоговом окне Числовые фильтры - Настраиваемый фильтр, появится диалоговое окно Пользовательский автофильтр (рис. 23), в котором необходимо ввести условия отбора.

При вводе условия 12000 руб. > Оклад < 22000 руб. будут использованы операции меньше, больше и логическая функция «И».

Рис.23

Результат выполнения задания:

5. Перейдите на лист 4 и выполните операцию фильтрации с расширенным фильтром, для этого надо:

скопировать и вставить на свободное место шапку списка;

в соответствующих полях задать критерии фильтрации (рис.24);

Рис.24

справа от таблицы разместить (копированием) необходимые поля для результирующей таблицы (рис.25);

установить курсор мыши внутри таблицы;

нажать кнопку Фильтр на панели Сортировка и фильтр ленты Данные, на той же панели нажать кнопку Дополнительно;

в появившемся окне Расширенный фильтр задать необходимые диапазоны ячеек (каждый диапазон выделять указателем мыши);

радиокнопкой выбрать команду Скопировать результат в другое место (рис.25), нажать кнопку ОК.

Рис.25

Результат выполнения задания:

Рис.26

Вопросы для самоконтроля

1. Чем отличается операция сортировки от операции фильтрации?

2. Чем отличается Автофильтр от расширенного фильтра?

Задания по вариантам к лабораторной работе №4

В соответствии с вариантом выберите из табл. 3.1 предметную область. Создайте на отдельном листе список. Который должен содержать не менее 20-30 записей. Над созданным списком необходимо выполнить следующие действия:

сортировку;

поиск информации с помощью автофильтра;

поиск информации с помощью расширенного фильтра.

Рекомендуется каждое задание выполнять на отдельном листе, для этого понадобится копировать список на нужное количество листов.

Формулировка заданий дается в общем виде. Для сортировки задания приведены в табл. 3.2, для автофильтра в табл. 3.3 и для расширенного фильтра в табл. 3.4.

Таблица 3.1 Предметная область

№ варианта	Предметная область	Примечания
1-6	Отдел кадров (Фамилия, Имя, Отчество, Отдел, Оклад, Пол, Дата рождения, Дата приема на работу)
7-12	Деканат (Фамилия, Имя, Отчество, Дата рождения, Группа, Предмет, Дата сдачи экзамена, Оценка)	Значения поля Оценка: Отлично, Хорошо и т.д.
13-18	Нагрузка преподавателя (Фамилия, Имя, Отчество, Ученая степень, Должность, Кафедра, Название предмета, Специальность, Группа, Вид занятия, Количество часов)	Значения поля Вид занятия: Лекции, Лабораторные работы, Курсовая работа и т.д.
19-24	Продажи (Менеджер, Клиент, Вид сделки, Товар, Количество, Цена, Сумма, Дата)	Значения поля Вид сделки: Покупка, Продажа
25-30	Поставки (Дата поставки, Поставщик, Количество поставленной продукции, Способ перевозки, Транспортные издержки на единицу товара, Цена единицы продукции без транспортных издержек, Стоимость перевозимого товара, Общие транспортные расходы)	Значения поля Способ перевозки: Ж.Д., Самолет и т.п.

Таблица 3.2 Сортировка

№ варианта	Сортировка по трем и более полям
1	Фамилия, Имя, Отчество, Дата рождения
2	Отдел, Фамилия, Имя, Отчество
3	Дата рождения, Фамилия, Имя, Отчество,
4	Оклад, Фамилия, Имя, Отчество, Отдел
5	Пол, Фамилия, Имя, Отчество, Дата рождения
6	Фамилия, Имя, Дата рождения, Дата приема на работу
7	Предмет, Дата сдачи экзамена, Фамилия, Имя, Отчество, Оценка
8	Фамилия, Имя, Отчество, Дата рождения, Группа
9	Предмет, Оценка, Фамилия, Имя, Отчество
10	Группа, Предмет, Дата сдачи экзамена, Оценка Фамилия, Имя
11	Дата сдачи экзамена, Предмет, Оценка, Фамилия, Имя
12	Оценка, Предмет, Фамилия, Имя, Группа
13	Ф.И.О., Ученая степень, Должность, Кафедра
14	Кафедра, Ф.И.О., Название предмета, Группа
15	Ф.И.О., Название предмета, Группа, Вид занятия, Количество часов
16	Количество часов, Название предмета, Ф.И.О.
17	Группа, Название предмета, Ф.И.О., Количество часов
18	Название предмета, Группа, Количество часов Ф.И.О.
19	Менеджер, Клиент, Товар, Количество
20	Клиент, Менеджер, Товар, Дата
21	Товар, Менеджер, Клиент, Сумма
22	Дата, Менеджер, Клиент, Количество
23	Сумма, Дата, Менеджер, Клиент
24	Вид сделки, Товар, Количество, Цена, Менеджер
25	Поставщик, Способ перевозки, Дата поставки
26	Количество поставленной продукции, Поставщик, Стоимость перевозимого товара
27	Поставщик, Количество поставленной продукции, Способ перевозки
28	Дата поставки, Поставщик, Количество поставленной продукции,
29	Общие транспортные расходы, Поставщик, Способ перевозки
30	Способ перевозки, Поставщик, Количество поставленной продукции

Таблица 3.3 Автофильтр

№ варианта	Запрос
1	Получить информацию о сотрудниках двух отделов, родившихся после Дата1 и принятых на работу позднее даты Дата2
2	Получить информацию о мужчинах, родившихся до Дата1 и имеющих оклад ниже значения Оклад
3	Получить информацию о женщинах, работающих либо в отделе Отдел1, либо в отделе Отдел2 с окладом выше значения Оклад
4	Получить информацию о мужчинах, родившихся после Дата1 и имеющих оклад ниже значения Оклад1, но больше значения Оклад2
5	Определить есть ли в отделах Отдел1 и Отдел2 мужчины с окладом выше среднего
6	Получить информацию о женщинах, родившихся до 1989 г. и имеющих оклад ниже среднего
7	Отобразить информацию о студентах групп Группа1 и Группа2 по предмету Предмет с оценками Хорошо и Отлично
8	Найти информацию о студентах, сдавших экзамены по предмету Предмет с оценкой Отлично либо раньше Дата1, либо позже Дата2
9	Найти информацию о студентах-отличниках, родившихся в период [Дата1; Дата2]
10	Найти информацию о студентах групп Группа1 и Группа2, сдавших экзамены по предметам Предмет1 и Предмет1 на Неудовлетвор-но
11	Найти информацию о студентах, родившихся после Дата и сдавших экзамены по трем предметам на Отлично
12	Найти информацию о студентах групп Группа1 и Группа2, сдавших экзамены по предметам Предмет1 и Предмет1 на Удовлетворительно и Неудовлетворительно
13	Определить читают ли лекции по предмету Предмет в Группе1 и Группе2 профессора
14	Определить в каких группах читает лекции и ведет лабораторные работы преподаватель Преподаватель
15	Найти всех доцентов и ассистентов с кафедры Кафедра, которые ведут занятия в группах Группа1 и Группа2
16	Найти всех доцентов и ассистентов, которые ведут лабораторные работы или практические занятия в группах Группа1 и Группа2
17	Найти всех профессоров и доцентов, которые ведут Семинары или Курсовые работы для специальности Специальность
18	Найти всех преподавателей с кафедры Кафедра, которые ведут лекции и лабораторные работы в группах Группа1 и Группа2
19	Найти информацию о менеджере Менеджер в период [Дата1; Дата2]
20	Определить клиентов, покупающих или поставляющих товары Товар1 и Товар2 в количестве больше Количество
21	Определите четыре самые крупные сделки за последний месяц
22	Найти информацию, связанную с покупкой или продажей товаров Товар1 и Товар2 клиентом Клиент на сумму Сумма и выше
23	Определите три самые крупные сделки за период [Дата1; Дата2]
24	Найти информацию, связанную с покупкой или продажей товаров Товар1 и Товар2 клиентом Клиент в количестве больше Количество
25	Найти информацию о поставках от поставщика Поставщик в период с Дата1 по Дата2
26	Найти информацию о поставках от поставщика Поставщик способом перевозки Способ_перевозки после Дата
27	Определить, какими способами перевозки поставлялся товар от поставщиков Поставщик1 и Поставщик2 в период с Дата1 по Дата2
28	Определить, какие поставщики использовали способы перевозки Способ_перевозки1 и Способ_перевозки2 с общими транспортными расходами меньше Сумма
29	Найти информацию о поставках от поставщиков Поставщик1 и Поставщик2 в период с Дата1 по Дата2
30	Найти информацию о поставках от поставщика Поставщик с общими транспортными расходами больше Сумма в период до Дата

Таблица 3.4 Расширенный фильтр

№ варианта	Запрос
1	Найти работников отделов Отдел1 и Отдел2 с фамилиями, начинающимися на буквы Буква1 и Буква2, и окладами выше Оклад1 (Столбцы, выводимые в отчет: Фамилия, Имя, Отчество, Отдел, Оклад, Пол)
2	Найти работников отделов Отдел1 и Отдел2 с фамилиями, начинающимися на буквы Буква1 и Буква2, и окладами выше Оклад1 (Столбцы, выводимые в отчет: Фамилия, Имя, Отчество, Отдел, Оклад, Пол)
3	Определить, принимались ли на работу в отделы Отдел1 и Отдел2 несовершеннолетние. (Столбцы, выводимые в отчет: Фамилия, Имя, Отчество, Отдел, Дата рождения, Дата приема на работу)
4	Найти женщин из отдела Отдел1, родившихся в период [Дата1, Дата2] и мужчин из отдела Отдел2, родившихся в период [Дата3, Дата4]. (Столбцы, выводимые в отчет: Фамилия, Имя, Отчество, Отдел, Пол, Дата рождения)
5	Найти работников отделов Отдел1 и Отдел2 принятых на работу до даты Дата и с окладом меньше Оклад. (Столбцы, выводимые в отчет: Фамилия, Имя, Отчество, Отдел, Оклад, Дата приема на работу)
6	Определить работают ли в отелах Отдел1 и Отдел2 люди старше 60-ти лет (Столбцы, выводимые в отчет: Фамилия, Имя, Отчество, Отдел, Пол, Дата рождения)
7	Найти информацию о студентах группы Группа1, сдавших экзамены по предмету Предмет1 и Предмет2 на оценку Отлично. (Столбцы, выводимые в отчет: Фамилия, Имя, Отчество, Группа, Предмет, Оценка)
8	Найти информацию о студентах групп Группа1 и Группа2, сдавших экзамены по предмету Предмет на оценку Удовлетворительно и Хорошо. (Столбцы, выводимые в отчет: Фамилия, Имя, Отчество, Группа, Предмет, Оценка)
9	Найти информацию о студентах в возрасте от Возраст1 до Возраст2, сдавших экзамены по предмету Предмет1 и Предмет2 на оценку Отлично. (Столбцы, выводимые в отчет: Фамилия, Имя, Отчество, Дата рождения, Группа, Предмет, Оценка)
10	Найти информацию о студенте Фамилия, сдавшего экзамен по предмету Предмет на оценку выше Оценки. (Столбцы, выводимые в отчет: Фамилия, Имя, Отчество, Группа, Предмет, Оценка)
11	Найти информацию о студентах групп Группа1 и Группа2, сдавших экзамены по предмету Предмет1 и Предмет2 на оценку Неудовлетворительно. (Столбцы, выводимые в отчет: Фамилия, Имя, Отчество, Группа, Предмет, Оценка)
12	Найти информацию о студентах, сдавших экзамены по предмету Предмет1 и Предмет2 на оценку Хорошо и Отлично. (Столбцы, выводимые в отчет: Фамилия, Имя, Отчество, Группа, Предмет, Оценка)
13	Найти лекции, которые обеспечивает кафедра Кафедра, на которые отводится часов больше Количество_часов. (Столбцы, выводимые в отчет: Фамилия, Имя, Отчество, Кафедра, Название предмета, Вид занятия, Количество часов)
14	Найти ассистентов и доцентов кафедр Кафедра1 и Кафедра2, которые проводят лабораторные работы и семинары в группах Группа1 и Группа2 (Столбцы, выводимые в отчет: Фамилия, Имя, Отчество, Ученая степень, Кафедра, Название предмета, Группа, Вид занятия)
15	Найти дисциплины, которые изучаются в группах Группа1 и Группа2 (Столбцы, выводимые в отчет: Фамилия, Имя, Отчество, Название предмета, Группа, Вид занятия)
16	Найти группы, которые относятся к специальностям Специальность1 и Специальность2 (Столбцы, выводимые в отчет: Кафедра, Название предмета, Специальность, Группа, Вид занятия, Количество часов)
17	Найти дисциплины, которые изучаются в группах Группа1 и Группа2 с количеством часов по лекциям больше Количество_часов. (Столбцы, выводимые в отчет: Фамилия, Имя, Отчество, Название предмета, Группа, Вид занятия, Количество часов)
18	Найти профессоров кафедр Кафедра1 и Кафедра2, которые читают лекции в группах Группа1 и Группа2 (Столбцы, выводимые в отчет: Фамилия, Имя, Отчество, Ученая степень, Кафедра, Название предмета, Группа, Вид занятия)
19	Найти информацию о сделках, проведенных менеджером Менеджер, с суммой больше Суммы. (Столбцы, выводимые в отчет: Менеджер, Вид сделки, Сумма)
20	Найти информацию о деятельности менеджеров Менеджер1 и Менеджер2 в период [Дата1, Дата2]. (Столбцы, выводимые в отчет: Менеджер, Вид сделки, Товар, Сумма, Дата)
21	Отобразить информацию о сделках за период [Дата1, Дата2], проведенных менеджерами Менеджер, Менеджер2 и Менеджер3 (Столбцы, выводимые в отчет: Менеджер, Вид сделки, Товар, Сумма, Дата)
22	Найти сведения о товарах Товар1, Товар2 и Товар3 проданных в период [Дата1, Дата2]. (Столбцы, выводимые в отчет: Менеджер, Клиент, Вид сделки, Товар, Дата)
23	Найти информацию о деятельности менеджеров Менеджер1 по товару Товар1 и Менеджер2 по товару Товар2 (Столбцы, выводимые в отчет: Менеджер, Клиент, Вид сделки, Товар, Количество, Цена, Сумма, Дата)
24	Отобразить сведения по товарам Товар1, Товар2 и Товар3, цена которых меньше Цены (Столбцы, выводимые в отчет: Менеджер, Клиент, Товар, Цена)
25	Найти поставки от поставщиков Поставщик1 и Поставщик2 в период [Дата1, Дата2] на суммы выше Суммы_поставки. (Столбцы, выводимые в отчет: Дата поставки, Поставщик, Цена единицы продукции без транспортных издержек, Стоимость перевозимого товара, Общие транспортные расходы)
26	Найти поставки способами перевозки Способ_перевозки1 и Способ_перевозки2 от поставщиков Поставщик1 и Поставщик2 (Столбцы, выводимые в отчет: Поставщик, Способ перевозки, Стоимость перевозимого товара, Общие транспортные расходы)
27	Найти поставки способами перевозки Способ_перевозки1 и Способ_перевозки2 в период [Дата1, Дата2] с количеством поставленной продукции больше Количество_поставленной_продукции. (Столбцы, выводимые в отчет: Дата поставки, Поставщик, Количество поставленной продукции, Способ перевозки,)
28	Найти поставки совершенные в период [Дата1, Дата2] с транспортными расходами выше Общие_транспортные_расходы. (Столбцы, выводимые в отчет: Дата поставки, Поставщик, Способ перевозки, Транспортные издержки на единицу товара, Общие транспортные расходы)
29	Найти поставки в период [Дата1, Дата2] с транспортными издержками на единицу товара меньше Транспортные_издержки_на_единицу_товара. (Столбцы, выводимые в отчет: Дата поставки, Поставщик, Способ перевозки, Транспортные издержки на единицу товара, Цена единицы продукции без транспортных издержек, Общие транспортные расходы)
30	Найти поставки от поставщиков Поставщик1, Поставщик2 и Поставщик3 в период [Дата1, Дата2] на суммы ниже Суммы_поставки. (Столбцы, выводимые в отчет: Дата поставки, Поставщик, Количество поставленной продукции, Способ перевозки, Стоимость перевозимого товара, Общие транспортные расходы)

ЛАБОРАТОРНАЯ РАБОТА № 5

Тема: Сводные таблицы. Консолидация данных в Excel.

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

Краткие теоретические сведения

Сводная таблица - это средство получения обобщенной информации из списка. Создается сводная таблица только при помощи специального средства “Мастер сводных таблиц”.

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

Содержание работы:

1. Открыть файл Кадры-1.xlsx, созданный в л/р №4

2. Построить сводную таблицу для получения информации о суммарных окладах по отделам. Отформатировать сводную таблицу.

3. Построить сводную таблицу для получения информации о количестве мужчин и женщин в отделах.

4. Создать новый файл в MS Excel и сохранить его под именем Консолидация-Иванов.xlsx. Переименовать в созданной рабочей книге листы, задав имена «январь», «февраль», «март» и заполнить каждый лист данными - см.рис.32. Произвести консолидацию данных, расположенных на получившихся трех листах рабочей книги (сводка за первый квартал).

Технология выполнения заданий

1. Откройте файл Кадры-1.xlsx, созданный в л/р №4 и скопируйте таблицу «Кадры» на лист 2.

2. Задание 2 выполняем на первом листе, для этого надо: