Логические функции «если», «и если», «или если», «счетесли», «суммесли».

 

Цель работы: получить практические навыки по работе с логическими функциями.

Порядок выполнения работы:

Задача 1

1. Заполнить таблицу, отформатировать ее, согласно образцу.

Анализ заработной платы сотрудников
Месяц	Январь
Отдел	ФИО	Оклад	Премия		Средний оклад
Первый	Иванов	4 000р.			Максимальный оклад
Первый	Петров	4 000р.			Минимальный оклад
Первый	Сидров	4 000р.			Количестово выданных премий
Второй	Яковлев	3 000р.
Второй	Александров	4 444р.			Отдел	Первый	Второй
Второй	Сергеев	6 000р.			Количество сотрудников
Второй	Ильин	8 000р.	1 600р.		Фонд заработной платы по отделам
Второй	Павлов	9 000р.	1 800р.
Второй	Николаев	5 000р.	1 000р.

 

2. Определить средний. максимальный и минимальный оклад сотрудников (Функции МАКС, МИН, СРЗНАЧ)

3. Определить скольким сотрудникам была выписана премия (Функция СЧЕТЗ)

4. Определить количество сотрудников и объем заработной платы по каждому отделу. (Функции СЧЕТЕСЛИ, СУММЕСЛИ)

5. Показать работу преподавателю.

Задача 2

1. Заполнить таблицу, отформатировать ее, согласно образцу.

№ п/п	Фамилия	№ зачетной книжки	Оценка	Подпись экзаменатора
	Иванов		отлично
	Петров		хорошо
	Сидров		удовл
	Яковлев		хорошо
	Александров		отлично
	Сергеев		отлично
	Ильин		удовл
	Павлов		неявка
	Николаев		отлично
		отлично
		хорошо
		удовл
		неудовл
		неявка

 

2. В экзаменационной ведомости по результатам сдачи экзаменов подсчитать количество хороших, отличных, удовлетворительных и неудовлетворительных оценок, а также число неявок.

3. Использовать функцию СЧЕТЕСЛИ.

Примечание: При копировании формул использовать абсолютную адресацию.

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

Контрольные вопросы:

1. Какие логические функции вы знаете и как они работают?

Лабораторная работа №12

Работа с таблицей как с базой данных

Цель работы: получить практические навыки по работе с таблицей как с базой данных.

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

Порядок выполнения работы:

1. Откройте ранее созданную книгу.

2. Перейдите на лист Ассортимент.

3. Составьте список персональной оргтехники, используя команду Фильтр. Скопируйте его

4. Составьте список персональных ксероксов.

5. Восстановите список.

6. Составьте список десяти наиболее доходных товаров, т.е. у которых максимальная сумма продаж. Восстановите список.

7. Составьте список товаров, у которых цена не превышает 3000 руб. Восстановите список.

8. Составьте список товаров, у которых цена находится в диапазоне от 3000 руб. до 5000 руб. Восстановите список.

9. Найдите товары с названиями Персональный и Профессиональный. Восстановите список.

10. Найдите товары с названиями Профессиональный и Профессиональный Плюс. Восстановите список.

11. Найдите товары, содержащие в названии слово Плюс. Восстановите список.

12. Выключите Фильтр.

13. Отсортируйте таблицу по колонке Товар.

14. Отсортируйте список по колонкам Товар и Название оргтехники. Товары с одинаковым названием отсортируйте по возрастанию цены.

15. Найдите значения суммарного количества и сумм продаж для каждого названия товара, используя команду Промежуточные Итоги.

16. Найдите средние значения стоимости и цены для каждого товара.

17. Сверните строки таблицы

· до промежуточных итогов;

· до общего итога.

18. Уберите итоги.

Требования к оформлению отчета:

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

 

Контрольные вопросы:

1. Как упорядочить данные в таблице?
2. Как выбрать из таблицы необходимые данные?
3. Как быстро подвести итоги по столбцам таблицы?
4. Как работать с формами?

Лабораторная работа №13

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

Цель работы: Освоить основные приемы расчетов с использованием сводных таблиц в EXCEL.

Задание на работу: Построить сводные таблицы по заданной.

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

Создание сводных таблиц позволяет экономить много времени и сил при обработке и анализе данных.

Сводная таблица – это таблица, полученная из первоначальной таблицы в соответствии с заданными критериями и содержащая обобщенные данные.

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

Расход по складу стройматериалов
ФИО	Наименование материала	Цена	Кол-во	Сумма	Дата
Иванов	кирпич				15.01.02
Петров	вагонка				18.01.02
Сидоров	вагонка				12.03.02
Иванов	кирпич				15.03.02
Петров	вагонка				19.04.02
Иванов	кирпич				22.05.02
Иванов	цемент				24.06.02
Сидоров	вагонка				24.07.02
Петров	кирпич				24.10.02

 

 

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

Над пользовательским интерфейсом отображается контекстная вкладка Работа со сводными таблицами. Вкладка Конструктор позволяет выбрать структуру сводной таблицы, применить стили, например, к заголовкам столбцов и строк, а также выбрать дизайн таблицы в галерее Стили сводной таблицы.

В окне справа находятся кнопки с названиями столбцов исходной таблицы. Эти названия можно перетаскивать в поля Названия строк, Названия столбцов, Значения, создавая необходимый макет сводной таблицы. После появления таблицы можно сократить количество столбцов, выполнив команду Группировать в контекстном меню на ячейке Дата.

После построения сводная таблица будет иметь вид:

Порядок выполнения работы:

1. Дана таблица значений. Ввести данные, отформатировать таблицу по образцу.

Дата	Сумма	На что потрачено	Категория
январь		ОСАГО	авто
март		техосмотр	авто
январь		квартира	квартира
январь		электричество	квартира
февраль		квартира	квартира
февраль		электричество	квартира
март		квартира	квартира
март		электричество	квартира
январь		кино	отдых
март		кино	отдых
март		театр	отдых
март		оформление ЗП	проч
январь		интернет	связь
февраль		интернет	связь
март		интернет	связь

2. Упорядочить данные таблицы по столбцу Дата.

3. Используя Промежуточные итоги п одвести итоги (вычислить расходы) в каждом месяце.

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

5. Построить гистограмму расходов по месяцам

6. Построить сводную таблицу, разместив поле Категория в строках,

7. а поле Дата - в столбцах, поле Сумма - в поле значений.

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

9. Убрать Промежуточные итоги.

Содержание отчета: Отчет должен содержать цель работы, задание на работу и скриншот результатов работы.

 

Контрольные вопросы:

1. Для каких целей используются сводные таблицы?
2. Как создать сводную таблицу?

 

Лабораторная работа №14

Создание диаграмм

Цель работы: получить практические навыки по работе с диаграммами.

Задание на работу: Построить различного типа диаграммы по данным таблицы Бюджет. Отформатировать диаграммы, используя команды контекстного меню и готовые стили.

Порядок выполнения работы: