Виды формул связи (для справки)

Связь с ячейкой этого же листа	Связь с ячейкой другого листа	Связь с ячейкой в другой книге
=i4	=Лист3! i9	=[Книга2]Лист3!$i$4

Зависимая таблица

Общая выручка
Январь	Февраль	Март	Итого

Задание 3.6. Экономические показатели магазина Книги

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

1. Создайте на новом листе «Показатели магазина» таблицу по образцу, приведенному ниже.

 

Показатели	Январь	Февраль	Март.	Всего
Выручка от продаж книг
Себестоимость
Прибыль
Административ-ные расходы
Накладные расходы
Доход
Налог
Чистый доход

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

3. Рассчитайте в новой таблице следующие показатели:

· Себестоимость, как 30% от выручки.

· Прибыль: разность между выручкой и себестоимостью.

· Административные расходы: 7,25% от выручки.

· Накладные расходы: 500р. на рекламу + административные расходы.

· Доход: разность между прибылью и расходами.

· Налог: 34% от дохода.

· Чистый доход: доход минус налог.

 

Задание 3.7. Построение диаграмм

1. Выделите на листе Показатели магазина всю таблицу без столбца Всего и щелкните на ПИ Стандартная по кнопке Мастер диаграмм. В появившемся окне выберите тип диаграммы Гистограмма, а вид диаграммы – Объемная (1-я в первом ряду). Далее.

2. Следуя указаниям Мастера диаграмм, введите данные для построения диаграммы. Переход к последующим шагам мастера кнопкой Далее, а заключительный шаг – кнопкой Готово.

3. Для быстрого создания диаграммы на выделите в таблице требуемую для построения диаграммы область (определить самостоятельно) и нажмите клавишу F11. В книге появится отдельный лист под именем Диаграмма 1, а на нем диаграмма, построенная по выделенной области таблицы.

4. Размеры диаграммы. Перейдите на лист с таблицей для диаграммы. Выделите построенную диаграмму и измените ее расположение на листе. Для этого поместите указатель мыши внутри обрамления диаграммы возле внешнего края (подсказка “ Область диаграммы ”) и перетащите мышью диаграмму ниже таблицы.

5. Буксировкой маркеров выделения задайте диаграмме необходимый размер.

6. Измените в одной из ячеек таблицы данные для построения и просмотрите изменение в диаграмме. Отмените это изменение.

7. Изменение типа диаграммы:

Ø Щелкните правой клавишей мыши по диаграмме и исполните команду Тип диаграммы. Задайте тип - Конусы.

Ø Измените тип диаграммы Конусы на «Вырезанные сектора».

8. Работа с заголовком.

Ø Выполните команду Диаграммы – Параметры диаграммы и выберите вкладку Заголовоки. В поле Название диаграммы введите Продажи мороженого. Enter.

Ø Щелкните правой кнопкой мыши по новому заголовку и выполните команду Формат заголовка диаграммы.

Ø С вкладки Шрифт задайте заголовку размер, шрифт, цвет, фон.

9. Щёлкните правой кнопкой мыши в область диаграммы и в меню исполните: «Формат области диаграммы…- Вид - Рамка - Толщина (третья линия сверху) - Флажок с тенью - ОК ”.

10. Сетки на диаграмме.

Ø Не все диаграммы имеют сетку. Задавая сетку диаграммам разных типов, убедитесь в этом.

Ø Задайте тип диаграммы — График. Щелкните правой кнопкой мыши по диаграмме. Появится окно - Параметры диаграммы.

Ø Использую разные вкладки окна Параметры диаграммы, определите окончательный вид вашей диаграммы.

11. Просмотр.

Ø Поместите диаграмму на 5 строк ниже таблицы.

Ø Нажмите кнопку Предварительный просмотр на ПИ Форматирование

Ø Задайте: Страница - Поля – Верхнее = 5, центрировать на странице = Горизонтально, «ОК», «Закрыть».

12. Скопируйте созданную диаграммы в документ Word (свой, ранее созданный).

 

Задание. Определите отпускную цену товара, себестоимость которого приведена в таблице. Отпускная цена больше себестоимости на величину НДС (14%). Постройте диаграмму, показав разницу цены по себестоимости и отпускной ценой. Диаграмма должна иметь заглавие и обозначение осей.

 

Артикул	Себестоимость	Отпускная цена	Разница цен	НДС
Т1001				14%
Е1202
Т1003
Т1005
Т1006

 

Задание 3.8. Обработки данных списка

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

Сортировка

Сортировка данных.

1. Отсортировать данные списка на листе Задания_списки:

Ø По фамилиям (по возрастанию);

Ø По классу и успеваемости;

Ø По полу, классу, и прогулам.

Сортировка с использованием списка пользователя.

Задание: Отсортировать данные в списке
по полю Класс. Для этого:

Ø Создать список пользователя из элементов 7-а, 8-а, 9-а, 10-а, 11-а. (Сервис – Параметры… – Списки)

Ø Данные – Сортировка… – Сортировать по классу;

Ø Щелкнуть в окне Сортировка по кнопке Параметры… и раскрыть в окне Параметры сортировки список Сортировка по первому ключу.

Ø Выбрать ключ сортировки: 7-а, 8-а, 9-а, 10-а, 11-а;

Ø Ок. Ок.

Фильтр

Ø Сделать активной любую ячейку в поле таблицы З адания_списки.

Ø Данные – Фильтр – Автофильтр.

Ø Отобрать при помощи автофильтра учащихся 10-а класса.

Ø Выбрать фильтром учениц 10-а класса.

Ø Восстановить таблицу в исходное состояние (Данные – Фильтр – Отобразить все).

Ø Отобрать при помощи фильтра учащихся, не имеющих прогулов, мужского пола, с успеваемостью большей или равной 4,2.

Ø Отобрать при помощи фильтрамальчиков, у которых количество прогулов попадает в интервал от 4 до 15 включительно. Результат скопировать на новый лист. Таблицу привести к следующему виду:

 

Фамилия	Имя	Отчество	Прогулы

 

Форма

Используя инструмент Форма (Данные – Форма …), внести в список учащихся в 10-а класс Сидорову Ольгу Ивановну, 1982 года рождения, проживающую по адресу Жукова 23, успеваемость - 3,9, пропуски по болезням - 25, прогулы – 5.

Стиль

1. Создайте новый лист в своей книге и присвойте ему имя Стиль.

2. Скопируйте на лист Стиль одну из ранее созданных таблиц, например, таблицу с листа Мороженое или другую.

3. Выделите на листе поле с заголовками столбцов и с первыми тремя строками таблицы. Выполните Формат – Стиль ипосмотрите имя используемого стиля для выделенного поля. Замените стиль Обычный на Финансовый. Проанализируйте отличие нового стиля.На других полях этой же таблицы попробуйте другие виды стиля из списка Имя стиля.

4. Выделите в таблице поле с данными и присвойте ему стиль Денежный.

5. Измените стиль Денежный, назвав его Денежный 10. Новому стилю определите:

Ø Число десятичных знаков: 1;

Ø Шрифт: полужирный, 8 пт;

Ø Граница: Внешние, тип линии - двойная, синего цвета;

Ø Вид: Заливка ячеек – желтый цвет, Узор - 6,25% серый.

6. Примените измененный стиль для других полей таблицы и других таблиц.

7. Сохраните изменения в книге.

Задание 3.9. Анализ данных

1. Вставьте в книгу новый лист «Анализ проката». и создайте на нем следующую таблицу для определения чистого дохода от проката видеомагнитофонов,

2. Данные в ячейках D6 и D10 определите по формулам:
(D6): = D4*D5 и (D10): = D6 - D8.

По данным из этой таблице выполните анализ возможной прибыли пункта проката:

 

	A	B	C	D
	Прокат видеомагнитофонов
	Плата за прокат, р.
	Количество предметов, шт.
	Суммарный доход	F
	Суммарные расходы
	Чистый доход	F

 

 

Подбор параметров:

Таблица подстановки данных с одной переменной

Задание: Определить в табличной форме зависимость чистого дохода от платы за прокат.

Решение. Создаем таблицу подстановки данных. Для этого:

1. В ячейки F1 и G1 введите заголовки " Плата за прокат " и " Чистый доход ".

2. В область (F3:F10) введите исследуемые варианты платы за прокат, например, 1000; 1250; 1500; и т.д., (вводить автозаполнением).

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

4. Выделите область (F2: G10) и выполните: Данные - Таблица подстановки...

5. В окне Таблица подстановки в поле " Подставлять значения по строкам", введите адрес ячейки, в которую будут вводиться варианты оплаты за прокат из области (F3: F10), т.е. D4.

6. Ок и проанализируйте полученные результаты.

Таблица подстановки данных с двумя переменными

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

1. На листе Анализ данных левый верхний угол будущей таблицы подстановки. расположите в ячейке F14.

2. Задайте входные параметры таблицы. В ячейки G14 - K14 введите варианты возможного количества предметов в прокате. В ячейки левого столбца таблицы подстановки (F15 - F22) – введите возможные варианты стоимости проката. (рекомендуется задать восемь вариантов цен проката и пять вариантов количества предметов).

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

4. Выделите всю область таблицы подстановки и выполните: Данные - Таблица подстановки.

5. Заполните поля окна Таблица подстановки и Ок, (ввести в них D4 и D5).

6. Проанализируйте полученную таблицу.

7. Подготовите таблицу подстановки к печати: добавьте в таблицу заголовки столбцов и строк, примените стиль, создайте внешние границы и др.

8. Сохраните изменения в книге.

 

Задание 3.10. Построение сводных таблиц

Для создания сводных таблиц используется мастер создания сводных таблиц.

В сводной таблице работают с полями: строки, столбцы, данные и страницы. Эти поля заполняются при работе с мастером сводных таблиц данными из исходной таблицы.

Создание сводной таблицы

1. Выделите исходную таблицу (лист Задание-итоги) и выполните команду: Данные – Сводная таблица.

2. На первом шаге работы мастера сводных таблиц определите источник данных для сводной таблицы.

3. На втором шаге из исходной таблицы задайте данные для построения сводной таблицы.

4. На третьем шаге определите место для построения сводной таблицы (на отдельном листе). Здесь же надо щелкнуть по кнопке Макет…. Появится макет для формирования полей будущей сводной таблицы.

5. На макете определите структуру будущей сводной таблицы. Это делается буксировкой кнопок с названиями столбцов исходной таблицы на соответствующие поля сводной таблицы. Для выбранной исходной таблицы рекомендуем в поле Строки — ввести Номер точки; в поле Столбцы - Вид товара; в поле Данные - Объем реализации, В поле Страницы - Месяц.)

6. Ок. Готово.

7. В полученной сводной таблице, раскрывая соответствующие списки, задайте месяц октябр ь, пятый ларек и вид продукции – Молоко.

8. Создайте другие сводные таблицы из этой же исходной таблицы. При этом поменяйте местами содержимое полей Строка и С толбец. А затем полей С толбцов и С траниц.

Работа со сводной таблицей

Различные операции со сводными таблицами удобно выполнять через ПИ Сводные таблицы. Выполним некоторые из возможных операций со сводными таблицами.

1. Добавить данные:

· Войти в сводную таблицу и 1щп.

· Выполнить команду Мастер сводных таблиц. - Макет …

· В макете перетащить в поле Данные кнопку Объем продаж, руб илидругую.

· Ок, Готово.

2. Вычисление поля сводной таблицы.

· Вызвать Мастер сводных таблиц и в окне Мастера выполнить команду Макет …

· В Макете сделать 2щл по кнопке в поле Данные.

· В поле Операция выбрать вид расчета, например, Максимум ( или другую операцию). Ок.

· Ок. Готово. Просмотреть результаты.

3. Скрыть и показать данные.

В сводной таблице в полях с заголовками есть стрелки для раскрытия списков этих полей. Каждый элемент этих списков имеет флажок. Удаление такого флажка равносильно скрытию соответствующих данных из сводной таблицы. Восстановление удаленного флажка – восстановлению скрытых данных.

· В сводных таблице оставить только два ларька и только три вида продукции.

· Восстановить в сводной таблице скрытые данные.

4. Убрать данные из сводной таблицы.

· Выполнить команду Мастер... - Макет …

· Перетащите кнопки из поля Данные с удаляемыми данными за пределы области таблицы. Кнопку - выбрать самостоятельно, но хотя бы одна кнопка в этом поле должна остаться.

· Ок. Готово

5. Форматирование сводной таблицы:

· Выбрать сводную таблицу.

· На ПИ Сводная таблица щелкнуть по кнопке Формат отчета.

· В ДО Автоформат выбрать вид отчета и Ок.

6. Задание форматов данным в сводной таблице.

· Выполните команду Мастер... - Макет …

· Выберите кнопку в поле Данные для задания формата этим данным и 2щл по ней.

· В ДО Вычисление поля сводной таблицы щелкните по кнопке Формат… и задайте в поле Числовые форматы требуемый.

· Ок. Ок. Ок. Готово.

7. Построение диаграмм сводных таблиц.

Перед построением диаграммы следует удалить из таблицы все данные общих итогов. Для этого:

· Выполнить команду Мастер... - Параметры…

· Удалить флажки Общая сумма по строкам и Общая сумма по столбцам.

· Сделайте активной одну из ячеек таблицы и щелкните на ПИ по кнопке Мастер диаграмм. Правка - Перейти - Выделить…- Текущая область.

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

· Измените тип диаграммы (1щп по полю диаграммы, Тип диаграммы …, и выбрать другой, Ок).

· Переместите диаграмму на другой лист с изменением ее размеров. Для этого, через БО скопируйте полученную диаграмму на лист со сводной таблицей и уменьшите площадь диаграммы примерно вдвое.

· Сохраните изменения в книге.

 

Функции

Задание 3.11. Функции СУММ, СРЗНАЧ, МИН, МАКС, РАНГ

Задание: По таблице подвести итоги соревнований по гимнастике и определить последовательно: сумму баллов каждого участника; средний балл на 1-м снаряде; самый низкий балл на 2-м снаряде; самый высокий балл на 3-м снаряде; занятое место по сумме баллов. Для расчетов применить функции СУММ, СРЗНАЧ, МИН, МАКС, РАНГ.

 

Результаты соревнований по гимнастике
Фио	1-й снаряд	2-й снаряд	3-й снаряд	Сумма баллов	Место
Давыдов Н.Н.	8,7	8,9	9,1
Самойлов В.В.	9,2	8,7	8,9		"
Бусаргин А.Н.	8,1	9,5	8,7		"
Грошев В.Л.	8,9	9,3	9,2		"
Власов В.В.	8,7	9,2	8,6		"
Брякалов Г.А.	9,2	8,1	7,9		"
Кожин А.А.	9,6	8,9	8,3		"
Кулешов П.А.	8,9	9,2	8,2		"
Буршанов В.А.	8,7	6,9	9,1		"
Молчанов О.Е.	8,5	7,9	6,9		"
	Средний балл для 1-го снаряда
	Балл чемпиона по сумме (максимум)
	Минимальный балл для 3-го снаряда

 

 

Примерный вид формул: =РАНГ(K4;$K$4:$K$13) =СРЗНАЧ(K4:K13)

=МАКС(K4:K13) =МИН(K4:K13)

Задание 3.12. Функция СЕГОДНЯ

Задание: Определите возраст и стаж сотрудников по известной дате их рождения и дате начала трудовой деятельности.

 

1. Подготовьте таблицу с данными на сотрудников. В отдельную ячейку введите функцию СЕГОДНЯ.

 

Сегодня	ХХ.ХХ.ХХ
№ п/п	Фамилия И.О.	Дата рождения	Возраст	Принят на работу	Стаж
1.	Давыдов Н.А.	25.05.39		24.05.68
2.	Баринов В.Г.	20.01.40		18.05.66
3.	Петров В.С.	15.07.36		20.09.76
4.	Кромский Б.В.	25.09 41		1.09.69

 

2. В ячейку Возраст для первого сотрудника введите формулу для определения возраста – разность между текущей датой и датой рождения (= «Текущая дата» — «Дата рождения»). Не забывайте о виде используемых в формуле ссылок.

3. В столбце Дата рождения использовать формат ячеек Дата, тип 15.01.03. Для столбца Возраст определить формат ячеек самостоятельно следующим образом:

4. Формат – Ячейки… - Число

5. В списке Числовые форматы выбрать «Все форматы», после чего в поле тип ввести ГГ. Ок.

6. Буксировкой созданной формулы определите в таблице возраст всех сотрудников.

7. Стаж сотрудников определите аналогично.

 

Задание 3.13. Работа с массивами

Определить общий объем продаж отдела по двум таблицам, используя формулы для работы с массивами.

Примерный вид формул приведен ниже.

Особенность работы с формулами для массивов: вместо Enter выполнять CTRL+SHIFT+ENTER

Алгоритм расчета:

1. Выбрать ячейку для расчета и вставить в нее функцию СУММ.

2. Для первой таблицы выделите буксировкой столбец Кол-во и умножьте его на выделенный буксировкой столбец Цена, (для второй таблицы выделять и перемножать матрицы).

3. CTRL+SHIFT+ENTER

 

Объем продаж отдела
№	Наименование товара	Кол-во, кГ	Цена единицы продукции
	Помидоры		100,00р.
	Огурцы		85,00р.
	Яблоки		28,00р.
	Груши		50,00р.
	Апельсины		48,00р.
	Сливы		74,40р.
Итого

 

Объем продаж отдела за квартал
№	Наименование товара	Количество, кГ	Цена ед. продукции
Янв.	Фев.	Мар.	Янв.	Фев.	Мар.
	Помидоры
	Огурцы
	Яблоки
	Груши
	Апельсины
	Сливы
ИТОГО ЗА КВАРТАЛ

 

=СУММ(В5:В15*С5:С15) и = СУММ(В5:D15*E5:G15)

: Задание 3.14. Табель. Функции СУММ; СЧЁТ; СЧЕТЕСЛИ СЧИТАТЬ ПУСТОТЫ

Используя функции СЧЕТ, СЧЕТЕСЛИ, СЧИТАТЬПУСТОТЫ и СУММ определите показатели в таблице Табель. При выборе нужной функции используйте таблицу – справку.

 

 

 

Для расчета использовать формулы
Всего часов	СУММ
Отработано дней	СЧЕТ
По болезни	СЧЕТЕСЛИ
Командировка
Отпуск
Прогулы
Выходные	СЧИТАТЬ ПУСТОТЫ

 

Задание 3.15. Функции СЧЕТЕСЛИ; СЧЕТСУММ

В таблице 3.15.2 используя функции СЧЕТЕСЛИ и СУММЕСЛИ определить по данным таблицы 3.15.1 количество сотрудников, суммарную зарплату и доход для каждого отдела.

Таблица 3.15.1

 

 

Таблица 3.15.2

 

Задание 3.16. Функция ПРОСМОТР

Задача. Определить категорию и дайте оценку работы кинотеатров Центрального района СПб по их валовому доходу. Категория и оценка зависят от валового дохода (см. справочную таблицу).

 

1. Создайте на листе Кинотеатры таблицу расчета валового дохода кинотеатров

Кинотеатр	Вместимость зрительного зала	К-во сеан- сов	Цена билета	Вало- вый доход	Оценка	Катего- рия
Баррикада
Аврора
Молодежный
Колизей

 

 

На листе Справка создайте оценочную таблицу и таблицу стоимости билетов и введите в неё следующую информацию:

 

Оценочная таблица

Валовой доход	Оценка	Категория
400000,00 р	плохо
500000,00 р	удовлетворительно
550000,00 р	хорошо
600000,00 р	отлично

Стоимость билета

Цена билета

250,00 р.

 

 

На этом же листе выделите ячейку для ввода в неё цены билета.
Установите связь между ячейками Цена билета листа Кинотеатр и ячейкой Цена билета листа Справка.

2. Выполните расчет валового дохода кинотеатров. Он зависит от вместимости кинотеатра, количества сеансов и стоимости билета.

3. Определите оценку и категорию кинотеатров в зависимости от их валового дохода. Используя для этого функцию ПРОСМОТР.

4. Вызываем функцию ПРОСМОТР в ячейку для вычисления оценки кинотеатра Баррикада. Далее работаем с мастером функций.

5. На 1-м шаге Мастера выбрать вид аргументов (в нашем задании: Искомые значения; Вектор просмотра; Вектор результата).

6. На втором шаге Мастера:

Ø В поле Искомое значение ввести ссылку на ячейку с рассчитанным значением валового дохода кинотеатра Баррикада.

Ø В поле Вектор просмотра ввести ссылки на ячейки с возможными значениями валового дохода из оценочной таблицы на листе Справка.

Ø В поле Вектор результата ввести ссылки на ячейки с возможными значениями Оценки из оценочной таблицы на листе Справка. Ок.

7. Буксировкой функции получить оценку работы и других кинотеатров.

Внимание! Ссылки на ячейки листа Справка должны быть абсолютными.

9. Аналогичным образом определите и категории всех кинотеатров.

 

Задание 3.17. Функция ЕСЛИ

Расчет коммунальных услуг. Подготовьте и заполните данными таблицу расчета коммунальных услуг за текущий месяц в зависимости от наличия льгот у жильцов. Создайте таблицу на 5 квартиросъемщиков и таблицу с тарифами за услуги.

 

 

Тариф, р.
Свет, квт/ч,	1,42
Квартплата, 1 м2,
Телефон
Антенна

Таблица для расчета

ФИО	Льготы	Площадь квартиры, м2	Показания счетчика в текущем месяце	Показания счетчика в прошлом месяце	Сумма за электроэнергию, р.	Квартплата, р.	Телефон, р.	Антенна, р.	Итого, р.
Иванов А.	льгота
Кожин А.	нет
…

 

Для расчета использовать логическую функцию ЕСЛИ.

Оплата по льготе наполовину меньше тарифа.

 

Примерный вид формулы для расчета оплаты электроэнергии:

=ЕСЛИ(В8= «льгота»;(D8 – E8)*$B$2/2;(D8 - E8)* $B$2)

Задание 3.18. Составная функция ЕСЛИ - И

Выбрать из таблицы партию изделий для покупки, при условии, что ее цена находится в диапазоне от 2000 р. до 2300 р.

 

Номер партии изделия	Цена, р	Решение о покупке
С000020
С000024
С000025
С000026
С000027
С000030

 

Примерный вид формулы:

=ЕСЛИ(И(В3>=2000;В3<=2300); «Покупать!»; «Не покупать»)

 

Задание 3.19.Составная функция ЕСЛИ – ИЛИ – И

 

Примерный вид формулы:

=ЕСЛИ(ИЛИ(И(C50="М";D50<=50);И(C50="Ж";D50<=35));$G$51;$G$52)

Задание 3.20. Защита в Excel

Защита паролем позволяет ограничить доступ к книге.

Пароль - секретное слово. Это комбинация букв, цифр и других символов (до 15 символов), без пробелов, с учетом регистра. Если забыли пароль, то не сможете вызвать файл. Способов как узнать забытый пароль нет! Его надо помнить и хранить. Поэтому, дабы не забыть введенный вами пароль, выполняя данное задание, всем использовать только пароль 1 2 3.

Существует несколько способов защиты в Excel.