Задание 1. Построение таблицы, расчеты по формулам, применение автосуммы, сортировка данных

В табличном процессоре Excel построить таблицу данного вида, рассчитать сумму продаж, остаток на конец дня, общий остаток товара на начало дня, на конец дня, общую сумму продаж, общий объем продаж. Упорядочить данные в таблице по виду товара в алфавитном порядке.

Товарооборот мясного отдела ООО " Садко"
№ п\п	Код товара	Наименование	Вид товара	Остаток на начало дня, кг	Цена, руб.	Продано, кг	Сумма продаж, руб.	Остаток на конец дня, кг
		Ветчина	Колбаса вареная		175, 00
		Докторская	Колбаса вареная		205, 00
		Краковская	Колбаса полукопченая		290, 00
		Любительская	Колбаса вареная		160, 00
		Молочная	Колбаса вареная		185, 00
		Молочные	Сосиски		195, 00
		Московская	Колбаса варено-копченая		300, 00
		Обыкновенные	Сосиски		180, 00
		Свиные	Сардельки		167, 50
		Сервелат	Колбаса варено-копченая		330, 00
		Сливочные	Сосиски		185, 00
		Столичная	Колбаса варено-копченая		315, 00
		Таллиннская	Колбаса полукопченая		300, 00
		Шпикачки	Сардельки		205, 00
			Итого:

Построение таблицы

В табличном процессоре Excel для оформления таблицы используют меню Формат, команда Ячейки…

и панель Форматирования

1. Ввести общий заголовок таблицы в ячейку А1.

2. Чтобы названия колонок в каждой ячейке вводились в несколько строк, необходимо: выделить ячейки от А2 до I2 (обозначается А2: I2) и выполнить команды Формат – Ячейки… - закладка Выравнивание – включить режим переносить по словам. В этом же окне установить Выравнивание по вертикали по верхнему краю – ОК. В панели инструментов нажать кнопку По центру и включить полужирный шрифт . Для заливки ячеек в панели инструментов нажать кнопку Цвет заливки , сделать щелчок по выбранному цвету. В ячейку А2 ввести заголовок первой колонки, в ячейку В2 – заголовок второй колонки и т.д.

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

4. Для ввода порядковых номеров в колонку № п/п ввести значения 1 и 2 в ячейки А3 и А4 соответственно, выделить эти две ячейки, подвести указатель мыши к маркеру заполнения (правому нижнему углу выделенной области), нажать левую кнопку мыши, протянуть мышь вниз до ячейки А16.

5. Ввести данные в таблицу. Чтобы в цене отображались два знака после десятичной запятой, необходимо выделить ячейки F3: F16 и два раза нажать кнопку Увеличить разрядность в панели инструментов (или выполнить команды меню Формат – Ячейки… - закладка Число – формат Числовой – установить Число десятичных знаков: 2 – ОК).

6. Выделить данные в колонках № п/п и Код товара. Затем, удерживая на клавиатуре клавишу Ctrl, выделить данные в колонках Остаток на начало дня, Продано и остаток на конец дня. Установить выравнивание По центру.

7. Чтобы оформит общий заголовок таблицы по центру относительно всей таблицы, необходимо: выделить ячейки вдоль таблицы в строке 1 А1: I1, в панели инструментов нажать кнопку Объединить и поместить в центре . Установить для него полужирный шрифт 12 размера.

8. Установить границы в таблице. Для этого выделить всю таблицу от ячейки А2 до I17 (без общего заголовка), на панели инструментов нажать треугольную кнопку справа от кнопки Границы и выбрать Все границы (или меню Формат – Ячейки… - закладка Граница – нажать внешние и внутренние – ОК).

Расчеты в таблице по формулам, автосумма

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

9. Чтобы вычислить Сумму продаж, необходимо умножить цену на проданное количество товара, т.е. для строки номер 3 написать формулу =F3*G3. Для этого перейти в ячейку Н3, ввести с клавиатуры знак =, щелкнуть левой клавишей мыши по ячейке F3, с клавиатуры ввести знак *, щелкнуть по ячейке G3, нажать на клавиатуре Enter.

10. Чтобы выполнить аналогичный расчет в остальных строках таблицы, нужно вернуться в ячейку Н3, подвести указатель мыши к маркеру заполнения (правому нижнему углу ячейки), нажать левую клавишу мыши и протянуть мышь до ячейки Н16 (включая её).

11. Для расчета остатка на конец дня нужно из остатка на начало дня вычесть проданное количество товара, т.е. для строки номер 3 написать формулу =E3-G3. Для этого перейти в ячейку I3, ввести с клавиатуры знак =, щелкнуть левой клавишей мыши по ячейке E3, с клавиатуры ввести знак -, щелкнуть по ячейке G3, нажать на клавиатуре Enter.

12. Чтобы выполнить аналогичный расчет в остальных строках таблицы, нужно вернуться в ячейку I3, подвести указатель мыши к маркеру заполнения, нажать левую клавишу мыши и протянуть мышь до ячейки I16 (включая её).

13. Для расчета итоговой строки используют автосуммирование. Для этого перейти в ячейку Е17, в панели инструментов нажать кнопку Автосумма , нажать Enter.

14. Аналогично рассчитать итоговые значения в ячейках G17, Н17 и I17.

15. Выделить ячейки Е17, G17 и I17 (удерживая на клавиатуре клавишу Ctrl), установить для них выравнивание По центру.

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

Сортировка данных в таблице

В сортировке не участвуют данные итоговой строки и колонки с порядковым номером.

17. Выделить всю таблицу без колонки № п/п и итоговой строки, т.е. от В2 до I16.

18. Выполнить команду меню Данные – Сортировка…

19. В поле Сортировать по выбрать поле Вид товара, справа от него включить режим по возрастанию, нажать ОК.

 

 

Задание 2. Расчеты в таблицах с применением функций

Выполнить расчеты показателей, расположенных под таблицей.

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

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

Кроме этого можно использовать кнопку списка у автосуммы.

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

· категория Математические – СУММ (суммирует значения указанных ячеек), СУММЕСЛИ (суммирует значения указанных ячеек, если они удовлетворяют заданному условию).

· категория Статистические – СРЗНАЧ (вычисляет среднее значение указанных ячеек), МИН (вычисляет минимальное значение указанных ячеек), МАКС (вычисляет максимальное значение указанных ячеек), СЧЕТЕСЛИ (считает количество ячеек в указанном диапазоне, если они удовлетворяют заданному условию).

1. Для расчета средней цены нужно перейти в свободную ячейку строки 19, например в Е19, нажать кнопку , выбрать категорию Статистические, выбрать функцию СРЗНАЧ, нажать ОК, в поле Число1 нажать цветную кнопку справа от поля (кнопка выхода в таблицу), в таблице выделить данные в колонке Цена F3: F16, нажать цветную кнопку (кнопка возврата) в окне Аргументы функции, нажать ОК.

 

2. Для расчета общей суммы продаж перейти в ячейку Е20, нажать кнопку , выбрать категорию Математические, выбрать функцию СУММ, ОК, в поле Число1 нажать кнопку выхода в таблицу, выделить данные Н3: Н16, нажать кнопку возврата, ОК.

3. Для расчета минимальной цены перейти в ячейку Е21, нажать кнопку , выбрать категорию Статистические, выбрать функцию МИН, ОК, в поле Число1 нажать кнопку выхода в таблицу, выделить данные F3: F16, нажать кнопку возврата, ОК.

4. Для расчета максимального остатка на конец дня перейти в ячейку Е22, нажать кнопку , выбрать категорию Статистические, выбрать функцию МАКС, ОК, в поле Число1 нажать кнопку выхода в таблицу, выделить данные I3: I16, нажать кнопку возврата, ОК.

5. Для расчета количества видов сосисок перейти в ячейку Е23, нажать кнопку , выбрать категорию Статистические, выбрать функцию СЧЕТЕСЛИ, ОК. В поле Диапазон нажать кнопку выхода в таблицу, выделить данные в колонке Вид товара D3: D16, нажать кнопку возврата, в поле Критерий (это проверяемое в диапазоне условие) с клавиатуры ввести слово " сосиски";, ОК. Функция считает, сколько раз в указанном диапазоне встречается заданное условие.

6. Для расчета проданного количества сарделек перейти в ячейку Е24, нажать кнопку , выбрать категорию Математические, выбрать функцию СУММЕСЛИ, ОК. В поле Диапазон нажать кнопку выхода в таблицу, выделить данные в колонке Вид товара D3: D16, нажать кнопку возврата, в поле Критерий (это проверяемое в диапазоне условие) с клавиатуры ввести слово " сардельки";, в поле Диапазон_суммирования (это ячейки, значения которых будут суммироваться) нажать кнопку выхода в таблицу, выделить данные в колонке Продано G3: G16, нажать кнопку возврата, ОК.

7. Для расчета количества видов товара проданного полностью, нужно посчитать, сколько ячеек в колонке Остаток на конец дня имеют значение ноль. Для этого перейти в ячейку Е25, нажать кнопку , выбрать категорию Статистические, выбрать функцию СЧЕТЕСЛИ, ОК. В поле Диапазон нажать кнопка выхода в таблицу, выделить данные в колонке Остаток на конец дня I3: I16, нажать кнопку возврата, в поле Критерий с клавиатуры ввести 0, ОК.

8. Для расчета количество видов товара, проданных менее 20 кг перейти в ячейку Е26, нажать кнопку , выбрать категорию Статистические, выбрать функцию СЧЕТЕСЛИ, ОК. В поле Диапазон нажать кнопку выхода в таблицу, выделить данные в колонке Продано G3: G16, нажать кнопку возврата, в поле Критерий с клавиатуры ввести условие < 20, ОК.

9. Для расчета суммы продаж вареной колбасы перейти в ячейку Е27, нажать кнопку , выбрать категорию Математические, выбрать функцию СУММЕСЛИ, ОК. В поле Диапазон нажать кнопку выхода в таблицу, выделить данные в колонке Вид товара D3: D16, нажать кнопку возврата, в поле Критерий с клавиатуры ввести " колбаса вареная";, в поле Диапазон_суммирования нажать кнопку выхода в таблицу, выделить данные в колонке Сумма продаж Н3: Н16, нажать кнопку возврата, ОК.