за объявленную ценность равна произведению объявленной ценности на
0,03 руб.;
d.За крупногабаритную посылку (с отметкой «Осторожно») взимается надбавка в размере стоимости 5 кг с учетом пояса;
e.Сумма налогового платежа равна 13% от суммы всех сборов;
f.Сумма оценки посылки равна сумме всех сборов плюс сумма налогового платежа.
3.Условное форматирование: выделить цветом фамилии тех работников, у кого сумма оценки посылки не менее 1000 руб.
4.Построить объемную гистограмму по фамилии и суммарной оценки посылки. В область диаграммы включить подписи данных – значения. Вывод оси Z отключить. Диаграмму отформатировать, вставить название диаграммы, подписи данных, осей и рядов.
§14. Работа со списками (базами) данных. Подбор параметров, консолидация, сводная таблица
Цель работы: научиться создавать формы, работать со списками, фильтрами, выполнять промежуточные итоги. Научиться работать с подбором параметров, консолидацией, сводными таблицами.
Базы данных предназначены для хранения и обработки больших объемов структурированной информации. Столбцы в таблице данных называются полями, строки – записями. Для облегчения ввода данных используются формы. Обработка данных включает сортировку, фильтрацию данных.
Работа с формой и списками
Подготовительный этап:
Щёлкнуть левой кнопкой
мыши по вложенному меню панели быстрого доступа и выбрать
Другие команды (см. рисунок
120).
В появившемся окне Параметры
Excel в поле Выбрать команды из
установить Все команды и в списке ниже выбрать команду Форма…, затем нажать кнопку Добавить>>, а затем ОК (см. рисунок 121). В результате на панели быстрого до-
ступа появиться кнопка 
. При щелчке на нем отображается
Рисунок 120 – Этапы установки команды Форма
111
форма для ввода данных.
Задание 1: Данная электронная таблица предназначена для начинающего бизнесмена. Это пример деловых расчетов по продаже бытовой техники.
Рисунок 121 – Окно выбора требуемой команды
1.Создайте три листа: Лист
Продажи, Лист Покупатели, Лист Товары.
2.Заполните таблицу Покупатели на соответствующем ли-
сте (см. рисунок 122); |
Рисунок 122 - Лист Покупатели |
3. Сделайте заготовку таблицы Продажи на листе Продажи, указав только название столбцов Покупатель, № договора, Цена, Количество;
3.Заполните стол-
бец покупатель, пользуясь Автозаполнением, как на рисунке 123.
4.Используя форму, введите необходимые данные таблицы, вызвав форму кнопкой из меню
быстрого доступа.
Рисунок 123 – Образец заготовки таблицы
112
Рисунок 124 – Окно Формы
Появляется окно, представленное |
|
на рисунке 124. После ввода каждой за- |
|
писи, необходимо нажать на кнопку |
Рисунок 125 - Образец заполнения |
Добавить. |
данными таблицу на листе Продажи |
6. В результате должна быть сформирована таблица, как на рисунке
125;
7.На листе Товары введите список товаров (см. рисунок 127);
8.На листе Продажи выделите столбец С и, используя контекстное меню добавьте столбец Наименование;
9.Скопируйте список товаров на лист Продажи в ячейки Н1:Н5;
10.На листе Продажи выделите диапазон ячеек С:С (щелкнуть по
букве столбца) и выполните команду на вкладке Данные→ меню Проверка данных → Проверка данных…(см. рисунок 126);
Рисунок 127 - Данные листа Товары
Рисунок 126 – Окно проверки данных
10. Выбирая из списка нужное значение для каждой ячейки, заполните таблицу, используя полученный список (см. рисунок 128)
113
Рисунок 128 – Ввод данных с помощью списка
11. Дополните таблицу столбцом Сумма (F) на листе Продажи. В результате будет таблица, представленная ниже на рисунке 129.
Рисунок 129 – Таблица результатов
12.С помощью формулы посчитайте сумму товара, которая равна произведению цены на количество;
13.На листе Покупатели подсчитайте общую стоимость товаров, проданных каждому покупателю, используя функцию СУММЕСЛИ, в которой указать диапазон покупателей на листе Продажи, затем – по какому критерию (здесь – покупатель) подсчитывается сумма, а потом диапазон суммируемых ячеек на листе Продажи.
114
14.На листе Товары дополните табли-
цу столбцами Количество и Стоимость (см.
рисунок 130).
15.На листе Товары, используя функцию СУММЕСЛИ выполните подсчеты количества и стоимости товара. Формула выглядит почти также, как на листе Покупатели.
Промежуточные итоги
Рисунок 130 – Образец листа Товары
Результаты, полученные на
листах Покупатели и Товары, с
помощью функции СУММЕСЛИ, можно получить, используя промежуточные итоги внутри интервала суммирования. Для этого на листе Продажи выделить диапазон А1:F17, выполнить команду
на вкладке Данные → Промежу-
точный итог. В появившемся диалоговом окне (см. рисунок 131) выполнить настройки, как показаны на рисунке.
Рисунок 131 – Окно настроек Промежуточные итоги
В результате должна таблица Продажи принять вид как на рисунке 132.
Рисунок 132 – Окончательный вид таблицы промежуточных итогов
16.По результатам постройте диаграммы и отформатируйте их.
a.Постройте круговую объемную диаграмму процентного соотношения стоимости закупки бытовой техники четырьмя магазинами. В область диаграммы включить заголовок, подписи данных – доли; легенду расположить внизу.
b.Построить кольцевую разреженную диаграмму процентного соотношения объемов закупок бытовой техники по наименованиям. В об-
115
ласть диаграммы включить заголовок, подписи данных – доли, легенду расположить внизу (см. рисунок 133).
Рисунок 133 – Вид диаграмм по полученным результатам
Фильтрация и сортировка
Сортировка – это систематизация данных, которая может устанавливаться по одному или нескольким полям.
Для сортировки по одному полу необ-
ходимо установить курсор на требуемую ячейку-заголовок и на вкладке Главная в
меню Сортировка и фильтр нажать требуе-
мый вид сортировки (см. рисунок 134). Для сортировки по нескольким полям
(максимум по трем полям, при этом результат будет виден в том случае, если в полях есть совпадающие данные) необходимо выделить требуемую таблицу и на вкладке
Главная в меню Сортировка и фильтр нажать команду Настраиваемая сортировка…
Рисунок 134 – Меню сортировки и фильтрации
Задание 2. Скопируйте таблицу с листа Продажи и вставьте на новый лист Продажи_1, удалите итоговые строки.
1.Отсортируйте данные в таблице на листе Продажи_1, по номеру договора в порядке возрастания. Обязательно расширьте диапазон сортировки на всю таблицу при запросе ЭТ.
2.Отсортируйте данные в таблице на листе Продажи_1, по наименованию в порядке возрастания, а затем по убыванию в зависимости от количества товара, пользуясь настраиваемой сортировкой (см. рисунок
135).
116
Рисунок 135 – Окно сортировки
3.Создайте таблицу на следующем листе Должности, представленную на рисунке 136.
4.Отсортируйте по полю Фамилия (по возрастанию), затем по полю Оклад (по возрастанию), по полю Дата приема (по убыванию).
Рисунок 136 – Результат сортировки
5. Отсортировать сразу по 3-м полям: Фамилия, Имя (по возрастанию), Оклад (по убыванию).
Фильтрация – это отбор данных. Используется три вида фильтров: автофильтр, числовой фильтр и настраиваемый фильтр.
Для фильтрации необходимо выделить ячейки-заголовки и на вклад-
ке Главная в меню Сортировка и фильтр нажать кнопку фильтр. Для снятия фильтра, необходимо выполнить все те же действия, только отжать соответствующую кнопку.
Задание 3.
1.Установите фильтр в таблице на листе Продажи_1
2.Используя фильтр, отобразите данные по СВЧ печам и отсортируйте данные в порядке возрастания по полю Количество. Скопируйте, полученный результат на следующий лист.
3.Используя фильтр, отобразите данные, цена изделий которых менее 1000 рублей.
117
4.Перейдите на лист Должности и отобразите данные только о водителях; о тех, кто был принят на работу до 2011 года включительно; о тех, у кого оклад не превышает 5000 рублей.
5.Отобрать данные о тех, у кого оклад от 4000 до 8000 рублей.
6.Отобрать данные о тех, у кого оклад менее 4000 и более 8000
рублей.
Подбор параметра
Средства Ms Excel Подбор параметра позволяет определить значение одной входной ячейки, которое требуется для получения желаемого результата в зависимой ячейке (ячейке результата).
Задание 4. «Штатное расписание»
Расчет по формулам с использованием абсолютных и относительных ссылок для составления штатного расписания
Дан месячный фонд зарплаты 180000 руб. Для работы отдела нужны: один уборщик, один вахтер, четыре контролера, два кассира, два старших кассира, два старших контроллера и один заведующий отделом. Зарплата уборщика устанавливается самостоятельно (любое число), которое excel подбирает с учетом формул и предельного ограничения. Зарплата сотрудника равняется зарплате уборщика, умноженной на коэффициент К, плюс доплата Д. Надо ввести таблицу, задаться зарплатой уборщика, рассчитать по формуле зарплату остальных сотрудников, определить суммарную зарплату и, изменить зарплату уборщика так, чтобы получить фонд месячной
зарплаты ≈ 180000 руб.
№ |
Должность |
К |
Д |
Зарплата |
Количество |
Суммар- |
сотруд- |
сотрудников |
ная зар- |
||||
|
|
|
|
ника |
плата |
|
|
|
|
|
|
||
1 |
Уборщик |
1 |
0 |
|
1 |
|
2 |
Вахтер |
1.5 |
2000 |
|
1 |
|
3 |
Кассир |
3 |
3000 |
|
2 |
|
4 |
Контролер |
4 |
0 |
|
4 |
|
5 |
Ст. кассир |
5 |
3000 |
|
2 |
|
6 |
Ст. контро- |
6 |
0 |
|
2 |
|
лер |
|
|
||||
|
|
|
|
|
|
|
7 |
Зав. отделом |
7 |
0 |
|
1 |
|
|
|
Итого |
|
|
|
|
118
В этой работе зарплату уборщика можно подгонять вручную, но можно воспользоваться пунктом меню Дан-
ные→Анализ что если…. В соответ-
ствующем диалоговом окне (см. рисунок 137) надо указать ячейку, содержащую подбираемый результат, подбираемое значение и ячейку, значение в которой должно изменяться при подборе. В этом случае Excel сам подберет такую зарпла-
ту уборщика, при которой фонд месячной зарплаты получится равным
180000 руб.
Консолидация данных Консолидация данных используется в том случае. Если необходимо
подытожить данные, расположенные в разных областях таблицы (на одном или разных листах, а также в разных книгах).
Результатом консолидации является итоговая таблица.
Задание 5. Скопируйте таблицу с листа Продажи 1 на новый лист
Консолидация.
1.Установите курсор в ячейку В19;
2.На вкладке Данные → Консолидация вызвать диалоговое окно
Консолидация;
3.В появившемся диалоговом окне (см. рисунок 138) установить
ивыделить данные как показано на рисунке ниже. После каждого выделенного диапазона необходимо нажимать на кнопку Добавить.
Рисунок 138 – Окно консолидации
4. В результате должны получить данные вида как на рисунке 139.
119
Рисунок 139 – Результат консолидации
5. Самостоятельно получите консолидирующие данные по наименованию изделий.
Сводная таблица Сводные таблицы – вспомогательные таблицы, которые содержат
часть данных анализируемой таблицы, отобранных так, чтобы зависимости между ними отображались наилучшим образом.
Таблицы, на основе которых строится сводная таблица, должны содержать заголовки строк или столбцов.
Задание 6:
1.Установите курсор в ячейку А30 листа Консолидация.
2.На вкладке Вставка → Сводная таблица, в появившемся диалоговом окне указать диапазон исходной таблицы (см. рисунок 140).
Рисунок 140 – Выбор диапазона для сводной таблицы
120
3. В появившемся справа макете (см. рисунок 141), методом перетаскивания сформировать таблицу, представленную на рисунке 142.
Рисунок 141 – Создание макета сводной таблицы
Рисунок 142 – Создание сводной таблицы
4.Итоговый результат сводной таблицы, см. рисунок 143.
121