Практические работы Excel

1. Электронные таблицы.

Назначение и основные функции

Программы обработки электронных таблиц (ЭТ) служат для оперативного выполнения различного рода расчетов, математических операций и манипуляций с ними.

Excel - самая популярная программа ЭТ. Она сумеет вычислить суммы по столбцам и строкам таблиц, взять %, посчитать среднее арифметическое, в нем можно использовать множество финансовых, математических, логических, статических функций, формировать данные, строить графики и диаграммы, вставлять картинки и т.д.

Окно программы Microsoft Excel

Элементами окна являются:

• заголовок;

• строка меню;

• лента;

• строка формул;

• поле имени;

• рабочее поле в виде таблицы;

• ярлыки листов.

В файле находится не одна, а несколько таблиц, образуя рабочую книгу.

Число листов в рабочей книге можно корректировать.

Рабочий лист – основной тип документа, используемый в Microsoft Excel для хранения и обработки данных. Рабочий лист состоит из ячеек, организованных в столбцы и строки, и всегда являлся частью рабочей книги.

Структура электронных таблиц

ЭТ состоит из столбцов, направленных сверху вниз, и строк, ориентированных слева направо. Заголовки столбцов обозначаются буквами, заголовки строк – числами.

Ячейка – место пересечения столбца и строки, это основная рабочая единица в ЭТ.

Адрес ячейки – составляется из заголовка столбца и заголовка строки, например: A1, F123, R7. Ячейка, с которой производятся какие-то работы, обычно выделена рамкой и называется активной.

2. Типы и формат данных

В работе с ЭТ можно выделить 3 основных типа данных: текст, число, формула.

Число в Excel может состоять только из следующих символов: 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, +, -, ( ), /, %, Е е, выравнивается число по правому краю.

Все остальное считывается как текст.

Текстом является любая последовательность из цифр, пробелов и нецифровых символов – например: 987$$78, 100 рублей.

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

Формула может содержать следующие элементы: операторы, ссылки на ячейки, значение, функции, имена и должна начинаться со знака равенства (=). Например, «=А3*В8+18».

Типы данных Excel

число	формула	текст (алфавитно-цифровой)
последовательность цифр, а также знаки +, - 231 -154,2 +489,0 4,8Е+06 (4,8*106)	арифметическое выражение, начинающееся со знака = и содержащее числа, функции, адреса ячеек, знаки арифметических операций	последовательность символов, не являющаяся ни числом, ни формулой

3. Оформление таблицы

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

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

• ячейки таблицы должны быть прорисованы при помощи кнопки

Внешние границы

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

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

Выделение элементов ЭТ

1. ячейку – щелкнуть по ней мышью, появится рамка выделения;

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

3. столбец – щелкнуть по заголовку столбца;

4. строку – щелкнуть по заголовку строки;

5. всю таблицу – кнопка в левом углу таблицы;

6. несмежные ячейки – выделить первую ячейку, нажать клавишу Ctrl, выделить остальные.

Средства Автозаполнения

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

1. Набрать 1 , Enter , 2 .

2. Выделить обе ячейки с цифрами 1 и 2.

3. Установить указатель мыши на маркер заполнения (нижний правый угол ячейки), при этом указатель мыши примет форму крестика.

1. П еретащить мышью маркер заполнения до нужной ячейки или нажать кнопку Заполнить

Копирование формул

Excel позволяет скопировать готовую формулу в смежные ячейки. Для этого:

1. Выделить ячейку, содержащую формулу.

2. Установить указатель линии на черный квадратик в правом нижнем углу курсорной рамки (крестик).

3. Нажать левую кнопку и сместить указатель по горизонтали (вертикали).

При смещении по горизонтали изменяется в формуле идентификатор столбца. При смещении по вертикали уменьшается или увеличивается номер строки. Это удобно, если ячейки расположены рядом. А если нет, то встаем мышкой в нужную ячейку, ставим курсор на вертикальную или горизонтальную границу (курсор превратится в стрелку) и, нажав Ctrl (возле стрелки появится мелкий плюсик, означающий, что мы будем копировать, а не перемещать), перетаскиваем формулу туда, куда нам нужно.

При копировании меняются адреса всех аргументов!

Задание формата ввода

Не все соглашения о вводе устраивают пользователя. Например, вводя 25.10, вы могли иметь в виду дату, имя, какой-то пункт в договоре. Вы можете принудительно изменить правила ввода, выделив ячейки. Главная –Число – Формат ячеек в поле Числовые форматы выбрать Числовой или другой (если Текстовый, то раннее введенная формула будет считаться текстом).

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

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

ПРАКТИЧЕСКАЯ РАБОТА № 1

Тема: Электронные таблицы MICROSOFT EXCEL

Ввод данных

Задание 1: Освоить приёмы работы в ЭТ по алгоритму:

1. Запустить программу MICROSOFT EXCEL.

2. Создать файл под именем Расчеты в Личной папке.

3. Установить курсор в ячейке А1, набрать текст Ввод данных, нажать Enter.

4. В вести в ячейки с А3 по А8 числа по порядку от 1 до 6, используя Автозаполнение. Для этого ввести в ячейку А3 число 1, нажать Enter, в ячейку А4 число 2, выделить обе ячейки, установить указатель мыши на маркер заполнения (нижний правый угол ячейки), при этом указатель примет форму крестика, перетащить маркер до нужной ячейки или нажать кнопку Заполнить

5. Ввести в ячейки с В3 по В8 числа 10, 20, 30, 40, 50, 60 применяя маркер автозаполнения.

6. В ячейку С2 ввести текст Сумма.

7. Ввести в ячейку С3 формулу суммы двух чисел = А3+В3. Аналогично введите формулу в ячейки с С4 по С8.

8. Оформить заголовок, для этого выделить строку заголовка во всю длину таблицы, нажать кнопку Объединить и поместить в центре , применить полужирное начертание шрифта (вкладка ГЛАВНАЯ).

9. Выделить ячейку С2, выровнять текст по ширине, применить полужирное начертание шрифта.

10. Выполнить обрамление таблицы, для этого выделить ячейки А3:С8 и применить кнопку Границы (вкладка ГЛАВНАЯ).

В итоге должны получить таблицу следующего вида:

1. Сдать работу преподавателю, сохранить изменения в файле.

Задание 2: Курс воздушных ванн предполагает увеличения продолжительности процедуры с 15 минут до 1 часа 45 минут. Через сколько дней стоит прекратить процедуры, если каждый день курс должен увеличиваться на 10 минут?

ПРАКТИЧЕСКАЯ РАБОТА № 2

Тема: Электронные таблицы MICROSOFT EXCEL

Ввод данных

Задание 1: Создать заготовку для пункта обмена валюты на листе 2 рабочей книги Расчеты.

Выполнить действия по алгоритму:

1. В ячейки А1, А2 и а3 ввести соответствующий текст;

2. В ячейку В1 ввести значение текущего курса $;

3. В ячейку В2 ввести обмениваемую сумму в $;

4. В ячейку В3 ввести формулу =В1*В2 для вычисления суммы в рублях следующим образом:

• выделить ячейку В3;

• ввести с клавиатуры знак равенства =;

• щелкнуть по ячейке В1 (первый множитель), в формуле появится ссылка на эту ячейку (ее адрес);

• ввести с клавиатуры знак умножения «*»;

• щелкнуть по ячейке В2 (второй множитель), в формуле появится ссылка на эту ячейку (ее адрес). В этот момент в ячейке В3 набрана вся формула =В1*В2;

• зафиксировать формулу нажатием клавиши Enter. В ячейке В3 вместо введенной формулы появится число.

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

2. Изменить название листа рабочей книги Лист1 на Обмен валюты, для этого щелкнуть правой кнопкой мыши по ярлыку листа, выбрать команду Переименовать, вписать новое имя листа, нажать ОК.

3. Выделить данные и оформить в виде таблицы, т.е. применить обрамление границ ячеек.

4. Сдать работу преподавателю, сохранить изменения в файле.

^{Образец}

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

Лабораторно–практическая работа № 3

Тема: Электронные таблицы MICROSOFT EXCEL. Ввод данных

Выполните задание 1, 2 по своей профессии.

Задания к практической работе № 3 по профессии «Повар, кондитер»

Задание 1: выполнить действия по алгоритму:

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

2. Добавить строку для ввода нового продукта «Ряженка».

3. Ввести данные по продукту «Ряженка».

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

5. Определить оставшиеся продукты и выручку.

6. Сдать работу преподавателю, сохранить изменения в файле.

Задание 2: Создать таблицу «Накладная» по образцу, произвести необходимые вычисления.

Накладная №235
№	Наименование товара	Цена (1кг)	Кол-во (кг)	Стоимость
1	Яблоки	102	25
2	Мандарины	150	15
3	Сливы	180	12
4	Груши	130	10
5	Вишня	200	13
6	Бананы	100	12
7	Апельсины	120	14

Итого:

Задания к практической работе № 3 по профессии «Автомеханик»

Задание 1: выполнить действия по алгоритму:

1. Создать таблицу на листе 3 рабочей книги Расчеты, изменить имя листа на Автозапчасти, выполнить ввод данных в ячейки, чтобы таблица приняла следующий вид:

   Диски тормозные
   №	Наименование	Цена, руб.	Кол-во	Кол-во на складе	Стоимость, руб.
   1	Барабан тормозной ВАЗ-2101	655,00	шт.
   2	Барабан тормозной ВАЗ-2108	510,00	шт.
   3	Диск передний тормозной 2101-07 (2шт) (Fenox)	1508,00	комп.
   4	Диск передний тормозной 2108-09 (2шт) (АSP MENSAN) 260201	1042,00	комп.
   5	Диск передний тормозной 2108-09 (2шт) (Fenox)	1305,00	комп.
   6	Диск передний тормозной 2112 R14 (2шт) (АSP MENSAN) 260206	1330,00	комп.
   7	Диск передний тормозной 2112 R14 (2шт) вентилируемый (Самара)	1510,00	комп.
   8	Запорное кольцо полуоси 2101	29,00	шт.
   9	Запорное кольцо полуоси 2121	33,00	шт.
   10	Запорное кольцо полуоси 3110 Волга (г. Миасс)	75,00	шт.
   				Итого:

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

3. Произвольно ввести данные в столбец Количество на складе.

4. Определить стоимость автозапчастей и итоговую сумму.

5. Сдать работу преподавателю, сохранить изменения в файле.

Задание 2: Создать таблицу «Автозапчасти» по образцу, произвести необходимые вычисления.

Автозапчасти
№	Наименование	Цена (руб.)	Поставлено (шт.)	Продано (шт.)	Осталось (шт.)	Выручка (руб.)
1	Поршень	2500	75	50
2	Тормозной диск	2000	125	100
3	Аккумулятор	4000	100	75
4	Радиатор	4500	50	30
5	Стартер	5000	60	40
				итого:

Задания к практической работе № 3 по профессии «Машинист лесозаготовительных и трелевочных машин»

Задание 1: выполнить действия по алгоритму:

1. Создать таблицу на листе 3 рабочей книги Расчеты, изменить имя листа на Лесоматериалы, выполнить ввод данных в ячейки, чтобы таблица приняла следующий вид:

Продажа лесоматериалов
№	Наименование продукции	Цена (1 куб.м)	Поставлено	Продано	Осталось	Выручка
1	Сосна	3000	100	35
2	Кедр	4000	10	7
3	Осина	2500	210	200
4	Берёза	2700	64	64
5	Дуб	4500	871	840
Всего:

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

3. Произвольно ввести данные в столбец Количество на складе.

4. Определить оставшиеся лесоматериалы, выручку и итоговую сумму.

5. Сдать работу преподавателю, сохранить изменения в файле.

Задание 2: Создать таблицу «Брус» по образцу, произвести необходимые вычисления.

Брус

Размеры бруса	Стоимость кубометра бруса в рублях	Кол-во кубометров	Цена
100 х 100 х 6000	4000	12
100 х 150 х 6000	4500	16
100 х 200 х 6000	5000	10
150 х 150 х 6000	5200	15
150 х 200 х 6000	5500	17
200 х 200 х 6000	6000	4
Итого:

ПРАКТИЧЕСКАЯ РАБОТА № 4

Тема: Электронные таблицы MICROSOFT EXCEL

Ввод формул

Задание 1: Создать таблицу для вычислений зарплаты и отчислений работникам фирмы.

Выполнить действия по алгоритму:

1. Создать таблицу на листе 4 рабочей книги Расчеты, выполнить ввод данных в ячейки по образцу:

1. Задать формат числа Денежный для ячеек С5:J9.

2. В столбце Оклад произвольно ввести числа.

3. Ввести формулы для расчета северной надбавки (Оклад*50/100).

4. Ввести формулы для расчета коэффициента (Оклад*60/100).

5. Ввести формулы для расчета суммы начисления.

6. Ввести формулы для расчета отчислений в профсоюз (Начислено/100), единого социального налога (Начислено*13/100).

7. Ввести формулы для расчета удержаний из зарплаты.

8. Ввести формулы для расчёта суммы к выдаче.

9. Присвоить Листу имя Зарплата, для этого щелкнуть правой кнопкой мыши по ярлыку листа, выбрать команду Переименовать.

10. Сдать работу преподавателю, сохранить изменения в файле.

Ссылки на ячейки другого листа рабочей книги

(Создание связей между рабочими листами и рабочими книгами)

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

Связывание данных отражает любые изменения, вносимые в исходные данные.

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

Внешняя ссылка может ссылаться на ячейку из другого рабочего листа той же книги или любого другого рабочего листа, другой рабочей книги.

Использование в формуле связывания ссылки

на другой рабочий лист

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

Чтобы сослаться на ячейку в другом рабочем листе, необходимо поставить ! между именем листа и именем ячейки. Например:

= Начисления ! В3

ПРАКТИЧЕСКАЯ РАБОТА № 5

Тема: Электронные таблицы MICROSOFT EXCEL

Связь листов рабочей книги

Задание 1: Создать ведомость на выдачу премии из расчета 30% от начисленной зарплаты на основе таблицы Зарплата в книге Расчеты.

Выполнить действия по алгоритму:

1. Создать таблицу на листе 5 (Изменить имя листа на Связь листов) рабочей книги Расчеты, в таблицу ввести данные по образцу:

2. Выделить ячейку А3 и ввести формулу: =Зарплата!В5, (В5 – адрес ячейки, в которой размещена первая фамилия сотрудника).

3. Выделить ячейку В3 и ввести формулу: =Зарплата!F5*30%.

4. Сдать работу преподавателю, сохранить изменения в файле.

Задание 2: Создать ведомость на выдачу материальной помощи сотрудникам фирмы из расчета 80% от оклада на основе таблицы Зарплата.

Выполнить действия по алгоритму:

Создать таблицу на листе Связь листов рабочей книги Расчеты.

Выполнить расчеты по формулам.

Сдать работу преподавателю, сохранить изменения в файле.

5. Относительная и абсолютная адресация

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

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

Для указания абсолютной адресации вводится символ $.

Различают два типа абсолютной ссылки:

• полная;

• частичная.

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

$D$5.

Частичная - указывает, если при копировании или перемещении не меняется номер строки или наименование столбца: $B6; D$12.

Знак $ можно писать вручную, с клавиатуры, а можно написать F4 и Excel сделает это сам: А2 $А$2 (первое нажатие)

А$2 (второе нажатие)

$А2 (третье нажатие)

А2 (следующее нажатие)

ПРАКТИЧЕСКАЯ РАБОТА № 6

Тема: Электронные таблицы MICROSOFT EXCEL

Абсолютная и относительная ссылка

Задания к практической работе № 6 по профессии «Повар, кондитер»

Задание 1: Приготовить шпаргалку для продавца напитками, по которой можно быстро определить стоимость нескольких бутылок одноименного товара.

Выполнить действия по алгоритму:

1. Создать таблицу на листе 4 (изменить имя листа на Ссылки) рабочей книги Расчеты по образцу:

Напиток	Емкость	Количество бутылок
	литр	1	2	3	4	5
Пепси-кола	0,5	5,00
Пепси-кола	1	8,00
Пепси-кола	1,5	12,00
Буратино	1,5	7,50
Дюшес	1,5	7,90
Квас	1,5	5,60

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

2. Задать последовательность чисел от 1 до 5 или более (количество бутылок) с помощью маркера заполнения.

3. Ввести стоимость одной бутылки для каждого напитка (столбец 1). Задать формулу в первой ячейке столбца 2 (стоимость одной бутылки умножить на количество бутылок).

4. Применить к формуле смешанные ссылки. Примерный вид формулы: = $В2*С$1.

5. Скопировать формулу по столбцам и строкам.

6. Изменить ширину столбцов, выполнить обрамление таблицы.

7. Сдать работу преподавателю, сохранить изменения в файле.

Задание 2: Создать таблицу по образцу на листе Ссылки для расчета стоимости овощей в зависимости от количества килограммов.

Стоимость овощей

Наименование	Количество килограммов
	1	2	3	4	5	6
Картофель
Морковь
Свекла
Лук

Выполнить расчеты по формулам. Сдать работу преподавателю.

Задание 3 (дополнительное): Создать таблицу по образцу для расчета количества акций приобретенных акционерами и их доли в % отношении к капиталу общества.

Выполнить расчеты по формулам.

Сдать работу преподавателю.

Задания к практической работе № 6 по профессии «Автомеханик»

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

Выполнить действия по алгоритму:

1. Создать таблицу на листе 4 (изменить имя листа на Ссылки) рабочей книги Расчеты по образцу:

Расчет стоимости ремонта двигателя автомобиля ВАЗ

в СТО ИЛИМ-Сервис за сентябрь 2015 года

Марка автомобиля	Количество деталей
ВАЗ 2101-2107	18
ВАЗ 2108-21099	22
ВАЗ 2110	14

№	Авторемонт	ВАЗ 2101-2107	ВАЗ 2108-21099	ВАЗ 2110	Итого на сумму	Доля в %
	Замена маслосъёмных колпачков с регулировкой клапана	2250	2650	2650
	Ремонт головки блока	4000	4000	4500
	Замена прокладки клапанной крышки	350	150	150
	Замена прокладки картера	1000	1000	1000
	Замена переднего сальника коленвала	950	950	950
	Замена заднего сальника коленвала	2300	2300	2300
	Замена распределительного вала	1500	1500	1500
	Замена масляного насоса	1450	1350	1350
	Замена бензонасоса	250	250	650
	Экспресс-замена масла	300	300	300
Итого:

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

2. К 3, 4, 5 столбцам применить кнопку Денежный формат .

3. Задать последовательность чисел от 1 до 10 в 1 столбце с помощью маркера заполнения.

4. Ввести формулу и применить к ней абсолютные ссылки. Скопировать формулу.

5. Произвести расчёты долей в % с применением абсолютной ссылки.

6. Изменить ширину столбцов, выполнить обрамление таблицы.

7. Сдать работу преподавателю, сохранить изменения в файле.

Задание 2: Создать таблицу по образцу на листе Ссылки для расчета стоимости ГСМ в зависимости от количества литров.

Стоимость ГСМ

Наименование	Количество литров
	1	2	3	4	5	6
Бензин 92	31,17
Бензин 95	34,4
Топливо дизельное ЕВРО, летнее	28,13
Керосин «КТ»	26,5
Масло Лукойл МС-20	12,8
Дизельное топливо Евро-4 "Роснефть"	30,0
Керосин «ТС-1»	29,30

Выполнить расчеты по формулам. Сдать работу преподавателю.

Задания к практической работе № 6 по профессии «Машинист лесозаготовительных и трелевочных машин»

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

Выполнить действия по алгоритму:

1. Создать таблицу на листе 4 (изменить имя листа на Ссылки) рабочей книги Расчеты по образцу:

Продажа технических жидкостей
Тех. жидкости	Емкости		Количество бутылок
	Цена 1 литра (руб)	2	3	4	5	6
Диз.топливо	36,7
Тосол	150
Масло	800
Антифриз	230
Электролит	120

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

1. Задать последовательность чисел от 1 до 5 или более (количество литров) с помощью маркера заполнения.

2. Ввести стоимость одного литра для каждого вида жидкости (столбец 1). Задать формулу в первой ячейке столбца 2 (стоимость одной бутылки умножить на количество бутылок).

3. Применить к формуле смешанные ссылки. Примерный вид формулы: = $В2*С$1.

4. Скопировать формулу по столбцам и строкам.

5. Изменить ширину столбцов, выполнить обрамление таблицы.

6. Сдать работу преподавателю, сохранить изменения в файле.

Задание 2: Создать таблицу по образцу на листе Ссылки для расчета стоимости лесозаготовительной техники в зависимости от количества машин.

Продажа лесозаготовительной техники
Техника	1	2	3	4	5
John Deere 1110E	20 000 000,00р.
John Deere 1510E	21 000 000,00р.
John Deere 1910E	19 000 000,00р.
John Deere 1210E	19 500 000,00р.
John Deere 1010E	24 999 999,00р.

Выполнить расчеты по формулам. Сдать работу преподавателю.

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

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

Чтобы отсортировать необходимо:

• выделить таблицу или выделить ячейку таблицы (желательно, чтобы эта ячейка находилась в том столбце, по которому производится сортировка);

• во вкладке Главная группы Редактирование выбрать команду Сортировка и фильтр;

• в раскрывающемся списке выбрать Настраиваемая сортировка

• в диалоговом окне Сортировка выбрать Столбец/Сортировать по, затем название или номер столбца; Сортировка/Значение выбрать Значение,

Порядок - По возрастанию или По убыванию или от А до Я (от Я до А)

• нажать кнопку ОК.

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

• числа сортируются от наименьшего отрицательного до наибольшего положительного числа;

• текст сортируется в следующем порядке: 0 1 2 3 4 5 6 7 8 9 ' - (пробел) ! " # $ % & ( ) * , . / : ; ? @ [ \ ] ^ _ ` { | } ~ + A B C D E F G H I J K L M N O P Q R S T U V W X Y Z А Б В Г Д Е Ё Ж З И Й К Л М Н О П Р С Т У Ф Х Ц Ч Ш Щ Ъ Ы Ь Э Ю Я;

• логическое значение ЛОЖЬ предшествует значению ИСТИНА;

• все ошибочные значения равны;

• пустые ячейки всегда помещаются в конец списка.

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

8. Фильтрация

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

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

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

Чтобы снова увидеть таблицу целиком, необходимо выбрать в списке строку "Выделить все". Для отмены режима фильтрации повторно выбрать команду Фильтр (кнопки со стрелками исчезнут).

ПРАКТИЧЕСКАЯ РАБОТА № 7

Тема: Электронные таблицы MICROSOFT EXCEL

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

Задания к практической работе № 7 по профессии «Повар, кондитер»

Задание 1:

Выполнить действия по алгоритму:

1. Создать таблицу Сортировка данных на листе 6 (изменить имя листа на Сортировка) рабочей книги Расчеты по образцу:

.

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

Салат "Легкий"

Продукты	Кол-во	Цена (руб) столовая	Цена (руб) кафе	Цена (руб) ресторан
Лук зеленый	30 г	15	17	25
Чеснок	2 зуб.	0,5	0,8	1,2
Сыр твердый	200г	90	120	150
Яйцо куриное	2 шт.	12	15	18
Свекла запечённая	1шт.	30	35	42
Соль		0,1	0,4	0.6
Перец		0,2	0,3	0,5
Майонез	50г	9	12	15

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

2. Дать имя листу Сортировка.

3. Скопировать таблицу и вставить ниже 2 раза.

4. В первой таблице сделать сортировку по 1 столбцу по алфавиту по возрастанию.

5. Во второй таблице выполнить сортировку по 1 столбцу по алфавиту по убыванию.

6. В третьей таблице выполнить сортировку по 3 столбцу по возрастанию.

7. П одобрать диаграммы к каждой таблице, согласно отсортированным данным.

8. В любую из 3 таблиц добавить строки минимальное, среднее и максимальное значение и произвести расчеты по 3 столбцу, используя кнопку, изображенную на рисунке (группы Редактирование вкладки Главная)

9. Сдать работу преподавателю, сохранить изменения в файле.

Задания к практической работе № 7 по профессиям «Автомеханик», «Машинист лесозаготовительных и трелевочных машин»

Задание 1:

Выполнить действия по алгоритму:

1. Создать таблицу Сортировка данных. Таблица ГСМ на листе 6 (изменить имя листа на Сортировка) рабочей книги Расчеты по образцу:

.

Сортировка данных. Таблица ГСМ

Наименование	Цена за литр (в Москве)	Цена за литр (в Иркутске)
Бензин 92	31,17	31,54
Бензин 95	34,4	37,2
Топливо дизельное ЕВРО, летнее	28,13	27,15
Керосин «КТ»	26,5	25,5
Масло Лукойл МС-20	12,8	13,0
Дизельное топливо Евро-4 "Роснефть"	30,0	29,8
Керосин «ТС-1»	29,30	29,00
Минимальное значение
Среднее значение
Максимальное значение

1. Ввести данные в таблицу, оформить согласно образцу (без минимального, среднего и максимального значения).

2. Дать имя листу Сортировка.

3. Скопировать таблицу и вставить 2 раза.

4. В первой таблице сделать сортировку по алфавиту по возрастанию.

5. Во второй таблице выполнить сортировку по 2 столбцу по убыванию.

6. В третьей таблице выполнить сортировку по 3 столбцу по возрастанию.

7. Подобрать диаграммы к каждой таблице, согласно отсортированным данным.

8. В любую из 3 таблиц добавить строки минимальное, среднее и максимальное значение и произвести расчеты, используя кнопку, изображенную на рисунке (группы Редактирование вкладки Главная).

9. Сдать работу преподавателю, сохранить изменения в файле.

ПРАКТИЧЕСКАЯ РАБОТА № 8 Тема: Электронные таблицы MICROSOFT EXCEL Фильтрация данных Задание 1: Произвести фильтрацию данных. Выполнить действия по алгоритму: 1. Создать таблицу Фильтрация на листе 7 (изменить имя листа на Фильтрация) рабочей книги Расчеты, ввести данные по образцу: Автоматическая обработка данных. Фильтрация Модель Стоимость (руб.) Кузов ВАЗ 2121 Нива 456000 Внедорожник ВАЗ 2131 Нива 519000 Внедорожник Лада Гранта 505900 Седан Лада Ларгус 617000 Универсал Лада Приора 543000 Универсал Лада Калина 572500 Универсал Лада Икс Рэй 600000 Кроссовер Нажать во вкладке Главная кнопку Сортировка и фильтр, выбрать команду Фильтр. В каждой ячейке верхней строки появится стрелка. Щелкнуть левой кнопкой мыши по стрелке в столбце Модель. Из списка выбрать автомобили ВАЗ, выделить цветом все ячейки таблицы. Вернуться в общий список. В третьем столбце выбрать Кузов, Универсал и выделить все ячейки таблицы другим цветом. Вернуться в общий список. Ответить на вопросы: что такое фильтрация, для чего она нужна. Задание 2: Произвести фильтрацию данных. Выполнить действия по алгоритму: На листе Фильтрация рабочей книги Расчеты, ввести данные по образцу: Произвести сортировку данных по столбцу B (Фамилия, имя). Посчитать среднее значение в столбце С (итог). Произвести фильтрацию списка отличников по полю итог. Сдать работу преподавателю, сохранить изменения в файле.

9. Мастер функций

E xcel имеет несколько сотен функций, которые подразделяются на категории: Финансовые, Дата и время, Математические, Статистические и т.д. На панели инструментов Стандартная мастер функций обозначен кнопкой . При нажатии на эту кнопку появляется окно Мастера функций, разделённое на две части: Категория и Функция.

Функции EXCEL

Все функции объединены в 11 категорий. Наиболее часто используются:

1. Первые две категории, в общем-то, и не категории, а 10 недавно использовавшихся и Все (Полный алфавитный перечень).

2. Категория Финансовые – множество спец. функций, вычисляющих % по вкладу или кредиту, норму прибыли и т.п.

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

1. Категория Дата и время – преобразует дату и время в различные форматы.

Функция СЕГОДНЯ и ТДАТА вставляют в ячейку текущую дату

IV. Округление и суммирование.

1. Категория Статистические (70 команд)

Функция Автосуммирования

На ленте Главная расположена кнопка Автосуммирование, которая используется для автоматического суммирования чисел с помощью функции СУММ.

Для вычисления суммы необходимо выполнить:

1. Выделить блок ячеек, в которых следует посчитать сумму.

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

10. Диаграммы

Элементы диаграммы

Ряд данных – столбики, полосы, секторы, предоставляющие числовые значения. Соответствуют как строкам, так и столбцам.

Ось Х – горизонтальная ось (ось категории) соответствует, как правило, столбцам.

Ось у – вертикальная ось (ось значения).

Легенда – указывает отдельные категории диаграмм (список маркеров, данных на диаграмме; список обозначений, соответствующих названий категории).

Линии сетки – отображают осевые шкалы рядов данных (помогают найти нужную точку на оси).

Порядок создания диаграммы с помощью Мастера диаграмм

1. Для построения диаграммы выделяют диапазон ячеек. Несоприкасающиеся ячейки выделить, удерживая клавишу Ctrl.

2. Во вкладке Вставка в группе Диаграммы выбрать нужную.

ПРАКТИЧЕСКАЯ РАБОТА № 9

Тема: Электронные таблицы MICROSOFT EXCEL

Диаграммы

Задание 1: Пользуясь данными, приведенными в таблице, построить диаграмму, характеризующую соотношение русских единиц длины.

Выполнить действия по алгоритму:

1. Создать таблицу Диаграммы на листе 12 (изменить имя листа на Диаграммы) рабочей книги Расчеты, в таблицу ввести данные по образцу:

Неметрические русские единицы длины

Единицы	Значение в мм
сотка	21,336
аршин	711,20
четверть	177,80
вершок	44,45
фут	304,80
дюйм	25,40
линия	2,54

2. Выделить таблицу.

3. Щёлкнуть по вкладке Вставка/Диаграммы

4. Выбрать в группе Диаграммы/ Круговая.

5. Щелкнуть правой кнопкой по диаграмме и в контекстном меню выбрать команду Добавить подписи данных.

6. На этом же листе постройте еще 3 разных диаграммы (аналогично).

7. Сдать работу преподавателю, сохранить изменения в файле.

дополнительные задания

I. Решение задач (профессия «Автомеханик», «Машинист лесозаготовительных и трелевочных машин»)

Задание 1: Рассчитать количество обслуженных автомобилей по факту у всех работников цеха ТО и Р станции технического обслуживания, если известно, что в январе факт был равен 3% от плана, в феврале на 0,75 больше плана, а в марте меньше на 0,25.

Выполнить действия по алгоритму:

1. Создать рабочую книгу Задачи в Личной папке, листу1 присвоить имя Факт.

2. Оформить таблицу по образцу:

3. Выполнить расчеты в столбцах E, G, I.

4. Сохранить изменения в файле, сдать работу преподавателю.

Задание 2: По используемому на предприятии автомобилю ВАЗ – 2101 линейная норма расхода топлива на 100 км составляет 8,5 л. По данным маршрутного листа показания спидометра на начало и конец рабочего дня составляют соответственно 41800 км и 41910 км. Стоимость бензина равна 18,2 рублей за 1 литр. Произвести расчет расхода ГСМ.

Выполнить действия по алгоритму:

1. В рабочей книге Задачи Лист2 переименовать в Расход ГСМ легковых а/м.

2. Оформить таблицу по образцу:

1. Выполнить расчеты по формулам.

2. Сохранить изменения в файле, сдать работу преподавателю.

Задание 3: Автомобиль ЗИЛ-4314-10 при пробеге 200 км выполнил транспортную работу, равную 600 тонно-километров. Линейная норма расхода топлива на 100 км пробега равна 31 л/100 км. Норма расхода топлива на транспортную работу для грузовых автомобилей с карбюраторными двигателями составляет 2 л/100 т∙км, с дизельными – 1,3 л/100 т∙км, для газобаллонных автомобилей – 2,5 л/100 т∙км. Определить общий расход топлива на пробег и выполнение транспортной работы.

Выполнить действия по алгоритму:

1. В рабочей книге Задачи Лист3 переименовать в Расход топлива грузовых а/м.

2. Оформить таблицу по образцу:

   Расход топлива для грузовых автомобилей
   Автомобиль	Норма расхода топлива для грузовых а/м с двигателем:			Пробег, км	Линейная норма расхода топлива на 100 км пробега, л/100км	Расход топлива по норме		Общий расход топлива
   	Карбюра-торный	Дизель-ный	Газобаллон-ный			на транспортную работу	на пробег а/м
   ЗИЛ-4314-10

3. Выполнить расчеты по формулам.

4. Сохранить изменения в файле, сдать работу преподавателю.

Задание 4: Автомобиль-самосвал ЗИЛ-ММЗ-555 за пробег 150 км выполнил 10 ездок с грузом. Линейная норма расхода топлива на 100 км пробега с учетом транспортной работы равна 37 л/100 км. Расход топлива на заезды автомобиля-самосвала на погрузку и разгрузку, подъем кузова составляет 0,25 л на каждую ездку с грузом. Определить общий расход топлива на пробег и выполнение транспортной работы.

Выполнить действия по алгоритму:

1. В рабочей книге Задачи Лист4 переименовать в Расход топлива.

2. Оформить таблицу по образцу:

   Расход топлива для автомобилей-самосвалов
   Автомобиль	Норма расхода топлива на заезды на погрузку и разгрузку, подъем кузова	Пробег, км	Линейная норма расхода топлива на 100 км пробега, л/100км	Расход топлива по норме		Общий расход топлива
   				на транспортную работу	на пробег а/м
   ЗИЛ-ММЗ-555

3. Выполнить расчеты по формулам.

4. Сохранить изменения в файле, сдать работу преподавателю.

Задание 5: Автопоезд в составе автомобиля КамАЗ-5320 с дизельным двигателем и прицепом ГКБ-8350 (масса прицепа 3,5 т) за 250 км пробега выполнил транспортную работу, равную 2600 т∙км. Линейная норма расхода топлива на 100 км пробега равна 25 л/100 км. Если автомобиль работает в составе автопоезда с прицепом или полуприцепом, то норма его расхода топлива на 100 км пробега увеличивается на 2,0 л на каждую тонну собственной массы прицепа (полуприцепа) для а/м с карбюраторным двигателем, на 1,3 л – для а/м с дизельными двигателями и на 2,5 – для газобаллонных автомобилей. Определить общий расход топлива на пробег и выполнение транспортной работы.

Выполнить действия по алгоритму:

1. В рабочей книге Задачи Лист5 переименовать в Расход топлива автопоезда.

2. Оформить таблицу по образцу:

   Расход топлива для автопоезда
   Автомобиль	Норма расхода топлива для грузовых а/м с двигателем:			Пробег, км	Линейная норма расхода топлива на 100 км пробега, л/100км	Расход топлива по норме		Общий расход топлива
   	карбюраторный	дизельный	газобаллонный			на транспортную работу	на пробег а/м
   КамАЗ-5320
   Прицеп ГКБ-8350 (масса 3,5 т)
   Итого:

3. Выполнить расчеты по формулам.

4. Сохранить изменения в файле, сдать работу преподавателю.

Задание 6: Определить техническую и эксплутационную скорость автомобилей по формулам, если известно, что автомобиль ЗИЛ-4314-10 за один рейс выполнил 32,4 км пробега, находясь в рейсе 1,2 ч, а автомобиль КамАЗ-555 находился в наряде 7 ч, из которых в движении был 5,7 ч и совершил пробег 154 км. (Техническая скорость ν _т = пробег / время нахождения в движении. Эксплутационная скорость ν_э = пробег / время нахождения в рейсе).

Выполнить действия по алгоритму:

1. В рабочей книге Задачи Лист6 переименовать в Скорость а/м.

2. Оформить таблицу по образцу:

Скорость автомобиля
Автомобиль	Пробег	Время нахождения а/м		Скорость
		в пути	в рейсе (наряде)	техническая	эксплутационная
ЗИЛ-4314-10
КамАЗ-555

1. Выполнить расчеты по формулам.

2. Сохранить изменения в файле, сдать работу преподавателю.

Решение задач (профессия «Повар – кондитер, официант – бармен»)

Задание 1: Определить массу нетто 80 кг картофеля для блюда «Картофель отварной», если дата его приготовления – 27 октября, 7 февраля, 17 января по формуле:

М_брутто * (100% - % отходов)

М _нетто =

100%

(Нормы отходов установленные в Сборнике рецептур блюд и кулинарных изделий для предприятий общественного питания для плодов и овощей:

картофель – до января - 25%, в январе – 30%, в феврале – 35%, в марте -40%;

морковь, свекла - до января - 25%, в январе – 30%, в феврале – 35%, в марте -40%;

капуста, петрушка, лук – не зависит от сезона.)

Выполнить действия по алгоритму:

1. В рабочей книге Задачи Лист1 переименовать в Нетто овощей.

2. Оформить таблицу по образцу:

   Определение массы нетто овощей или плодов
   Наименование овощей или плодов	Масса брутто, кг	% отходов	Масса нетто, кг
   Картофель

3. Выполнить расчеты по формулам.

4. Сохранить изменения в файле, сдать работу преподавателю.

Задание 2: Определить количество овощей (массой брутто) для приготовления 100 порций борща в октябре, в феврале, в марте. Потребность в очищенный овощах на одну порцию: свеклы 0,08 кг, моркови -0,02 кг, лука репчатого – 0,02 кг по формуле:

М_нетто * кол-во порций

М _бруто =

100% - % отходов

Выполнить действия по алгоритму:

1. В рабочей книге Задачи Лист2 переименовать в Борщ.

2. Оформить таблицу по образцу:

Потребность в овощах для борща
Наименование овощей или плодов	Масса брутто, кг	% отходов	Масса нетто, кг	Кол - во порций	Потребность на 1 порцию	Итого
Свекла
Морковь
Лук репчатый

1. Выполнить расчеты по формулам.

2. Сохранить изменения в файле, сдать работу преподавателю.

Задание 3: Определить сколько порций рыбы «по- русски» можно приготовить, если в наличии 30 кг судака неразделанного мелкого и крупного. Закладка рыбы по рецептуре: масса нетто судака крупного на одну порцию – 122г, мелкого – 235 г.

Выполнить действия по алгоритму:

1. В рабочей книге Задачи Лист3 переименовать в Рыба.

2. Оформить таблицу по образцу:

Определение количества порций рыбы
Наименование рыбы	Общая масса брутто, кг	Масса нетто на 1 порцию, г	Масса нетто, кг	Кол - во порций
Судак мелкий
Судак крупный

1. Выполнить расчеты по формулам.

2. Сохранить изменения в файле, сдать работу преподавателю.

Задание 4: Определить сколько котлет рубленых можно приготовить, если в наличии 150 кг говядины II категории. Процентное содержание котлетного мяса в полутуше говядины II категории – 41,1%. На одну порцию котлет массой нетто потребуется 56 г.

Выполнить действия по алгоритму:

1. В рабочей книге Задачи Лист4 переименовать в Котлеты.

2. Оформить таблицу по образцу:

   Определение количества порций котлет рубленых
   Наименование изделий из говядины	Масса говядины, кг	% содержание котлетного мяса в полутуше говядины	Кол - во котлетного мяса нетто, кг	Масса нетто на 1 порцию, кг	Кол-во котлет
   Котлеты рубленые

3. Выполнить расчеты по формулам.

4. Сохранить изменения в файле, сдать работу преподавателю.

Задание 5: Рассчитать, сколько порций плова из кур можно приготовить, если в наличии 30 кг кур потрошенных II категории. Масса нетто кур потрошенных – на 9% меньше массы брутто. На одну порцию плова массой нетто идет 145 г кур.

Выполнить действия по алгоритму:

1. В рабочей книге Задачи Лист5 переименовать в Плов.

2. Оформить таблицу по образцу:

   Определение порций плова из кур
   Наименование	Масса кур брутто, кг	Масса кур нетто, кг	Расход массы нетто на 1 порцию плова, кг	Количество порций
   Курица потрошенная II категории

3. Выполнить расчеты по формулам.

4. Сохранить изменения в файле, сдать работу преподавателю.

Задание 6: Сколько порций маринада овощного с томатом можно приготовить из 8 кг моркови в апреле. В апреле морковь имеет 25% отходов. Количество моркови по массе нетто на 1 кг соуса составляет 500 г. На одну порцию маринада идет 75 г по рецептуре.

Выполнить действия по алгоритму:

1. В рабочей книге Задачи Лист6 переименовать в Маринад.

2. Оформить таблицу по образцу:

   Определение порций маринада овощного
   Наименование	Масса брутто, кг	Масса нетто, кг	Расход моркови по массе нетто на 1 кг соуса, кг	Количество порций
   Морковь

3. Выполнить расчеты по формулам.

4. Сохранить изменения в файле, сдать работу преподавателю.

Задание 7: Сколько потребуется продуктов для приготовления гарнира к 200 порциям рыбы припущенной (в декабре)? К этому блюду на гарнир предусмотрен картофель отварной из расчета 150 г на одну порцию рыбы. Масса картофеля для приготовления 1 кг готового гарнира по рецептуре -0,990 кг. % отходов картофеля в декабре – 30%.

Выполнить действия по алгоритму:

1. В рабочей книге Задачи Лист7 переименовать в Гарнир.

2. Оформить таблицу по образцу:

Определение количества гарнира к рыбе
Наименование	Количество порций рыбы	Кол-во гарнира, кг	Масса картофеля для приготовления 1 кг готового гарнира по рецептуре, кг	Масса картофеля нетто, кг	Масса картофеля брутто, кг
Картофель отварной

1. Выполнить расчеты по формулам.

2. Сохранить изменения в файле, сдать работу преподавателю.

Задание 8: Определить общее количество продуктов для приготовления 50 порций отварной курицы с гарниром и соусом в кг.

Выход готовой отварной птицы на одну порцию - 100 г. Масса брутто одной порции от нетто составляет на 99% больше.

Для приготовления гарнира: по рецептуре на одну порцию отварной птицы требуется 150 г гарнира. Для приготовления 1 кг риса припущенного требуется: крупы рисовой – 345 г, маргарина – 45 г.

Для приготовления соуса: по рецептуре на одну порцию отварной птицы требуется 75 г готового соуса. Для приготовления 1 кг соуса требуется продуктов:

жир – 25г;

мука пшеничная – 50г;

томатное пюре – 150г;

морковь – 100г;

лук репчатый – 36г;

петрушка, корень – 20г;

сахар – 20г.

Выполнить действия по алгоритму:

1. В рабочей книге Задачи Лист8 переименовать в Курица с гарниром и соусом.

2. Оформить таблицу по образцу:

Определение общее количества продуктов для приготовления отварной курицы с гарниром и соусом
	Наименование продуктов	Выход на одну порцию отварной птицы, г	Масса брутто 1 порции, кг	Кол-во порций	Итого, кг
Курица	Курица полупотрошенная I категории
Гарнир
	Крупа рисовая
	Маргарин
Соус
	жир
	мука пшеничная
	томатное пюре
	морковь
	лук репчатый
	петрушка, корень
	сахар

1. Выполнить расчеты по формулам.

2. Сохранить изменения в файле, сдать работу преподавателю.

47