Автоматическое вычисление промежуточных итогов
Функция автоматического вычисления итогов вызывается командой Данные-Итоги. Предварительно данные должны быть представлены в виде списка (базы данных) и отсортированы по полю, при изменении которого надо выдавать итоги.
Пример. Для списка, представленного таблицей 1.6, получить итоговые данные об обороте каждого продавца.
Для решения задачи список сначала отсортируем по полю «Продавец». Далее выполним следующие действия:
- установим указатель ввода внутри таблицы;
- выполним команду Данные-Итоги;
- появится окно Промежуточные итоги (рис. 1.4), в котором надо указать:
Рисунок 1.4 – Окно Итоги
· поле, при изменении которого надо выдавать промежуточный итог (в нашем случае «Продавец»);
· итоговую операцию (это может быть сумма, среднее, максимум, количество значений и т.д.). В нашем случае - это сумма;
· поле, по которому выполняется выбранная операция (это «Оборот»).
После этого исходная таблица будет дополнена итоговыми строками для каждого продавца и общим итогом (таблица 1.13):
Таблица 1.13 – Итоговые строки
| Иванов Всего | |
| Петров Всего | |
| Сидоров Всего | |
| Общий итог |
С помощью кнопок + и -, расположенных слева от таблицы, имеется возможность скрыть (или снова показать) исходные данные, показать только промежуточные итоги или один общий итог.
Лабораторная работа №1 Excel. Форматирование ячеек, простейшие вычисления
Цель работы:
- научиться вводить данные в таблицу;
- уметь производить простейшие вычисления;
- научиться редактировать таблицу;
- освоить работу с командой Формат-Ячейки.
Задание
1 Создайте таблицу 1.14
Таблица 1.14 - Показатели работы завода
| A | B | C | D | E | F | G | H | I | |
| Код изделия | Наименование изделия | Количество изделий, шт. | Цена за единицу, руб. | Объем выручки, руб. | Недополучен-ная выручка, % | ||||
| План | Факт | По себестои-мости | Оптовая | План | Факт |
2 Сделайте вертикальное и горизонтальное выравнивание текста в заголовках «по центру».
3 Столбцы A и B заполнить произвольно (не менее 6-7 записей).
4 Поле «Количество» (План и Факт) вычисляется по формуле: случайное число от 100 до 500.
5 Поле «Цена за единицу (по себестоимости)» вычисляется по формуле: случайное число от 100 до 150.
6 Поле «Цена за единицу (оптовая)» вычисляется: «Цена по себестоимости» + от 30 до 50% от «цены за единицу (по себестоимости)».
7 Поле «Объем выручки (План)» вычисляется: «Количество изделий (План)» * «Цена за единицу (оптовая)».
8 Поле «Объем выручки (Факт)» вычисляется: «Количество изделий (Факт)» * «Цена за единицу (оптовая)».
9 Поле «Недополученная выручка» вычисляется по формуле: («Объем выручки (факт)» - «Объем выручки (План)») / «Объем выручки (Факт)».
10 Округлите числовые данные в столбце «Количество» до целых, а остальные числовые данные до двух знаков после запятой.
11 Для столбцов G и H задайте денежный формат.
12 Для столбцов C и D задайте собственный формат: число с текстом, используя категорию «Все форматы» в команде Формат-Ячейки, вкладка Число.
13 Сделайте вертикальную ориентацию текста заголовка «Код изделия».
14 В таблицу добавить строку «Итого», в которой вычислить суммы для числовых полей.
15 Сделайте внешние границы таблицы двойной чертой, а внутренние одинарной.
16 Выполните заливку заголовков светло-серым цветом.
17 Измените шрифт в заголовках.
Контрольные вопросы
1 Какие числа выдает функция СЛЧИС?
2 Как сделать перенос текста по границе ячейки?
3 Как отформатировать дробные числа в виде целых или дробных с двумя знаками после запятой?
4 Каким образом можно добавить текст к числу, оставляя ячейку числовой?
5 Как создать обрамление таблицы?
6 Как автоматически выполнить суммирование по столбцу?
7 Как изменить фон в ячейках?
Лабораторная работа №2 Excel. Вычисления с использованием функций в связанных таблицах
Цель работы
- размещение таблиц на разных рабочих листах;
- использование функций различных категорий;
- использование табличных формул;
- перемещение, копирование, , удалениие, добавление и связывание рабочих листов;
- применение функции ВПР.
Данные
В техническом отделе завода имеется справочник работ (таблица 1.15). В отделе кадров хранятся цеховые списки (таблица 1.16) и сведения об отработанных днях работниками завода (таблица 1.17). В бухгалтерии производится начисление зарплаты (таблица 1.18).
Таблица 1.15 – Справочник работ
| A | B | |
| Код работы | Название работы | |
| Сварочная | ||
| Слесарная |
Таблица 1.16 – Цеховые списки
| A | В | С | D | E | F | |
| Табельный номер | Ф.И.О. | Дата поступления на завод | День рождения | Стаж работы | Оклад | |
| Петров Т.О. | 12.09.1982 | 06.03.1961 |
Таблица 1.17 – Отработанные дни
| А | В | С | D | E | F | |
| Табельный номер | Количество рабочих дней | Количество отработанных дней | ||||
| в месяце | из них пропущено | |||||
| по больничному | другие причины | всего | ||||
Таблица 1.18 – Расчет
| A | B | C | D | E | F | G | H | I | J | K | |
| РАСЧЕТ ЗАРАБОТНОЙ ПЛАТЫ | |||||||||||
| Табельный номер | Фамилия И.О. | Код работы | Название работы | Повре-менно | Районный коэфф. | Премия | Подохо-дный налог | Пенси-онный взнос | Проф. взнос | Итого на руки | |
| Петров Т.О. |
Требуется:
1) Разместить таблицы 1.15, 1.16 и 1.17 на различных листах.
2) Переименовать листы соответственно в «Справочник», «Кадры» и «Рабочие дни».
3) Составить итоговый отчет (таблица 1.18) на новом листе. Переименовать данный лист в “Расчет”.
4) Выполнить задания.
Заполнение таблиц
Для таблицы 1.15.
Столбцы A и B заполнить произвольно (5 записей).
Для таблицы 1.16.
1 Колонки A, B, C, D заполняются произвольно (8 записей).
2 Колонка А имеет такой числовой формат, при котором недостающие до 5 цифр числа дополняются нулями. Колонки C и D имеют указанный в примере формат даты (применить пользовательский формат).
3 Колонка «Стаж работы» вычисляется по следующей формуле:
текущий год – год от «Даты поступления на завод». Для этого воспользоваться функциями ГОД и СЕГОДНЯ.
4 Колонка «Оклад» задается по следующей формуле:
случайное число от 5000 до 15000. При вычислении использовать функцию ЦЕЛОЕ.
Для таблицы 1.17.
1 Колонка A – копия колонки A таблицы 1.16 (воспользоваться ссылкой на ячейки таблицы 1.16, введя в ячейку A2 формулу =Кадры!A2).
2 Колонки B, C, D заполняются произвольно, причем «Количество рабочих дней в месяце» – одинаково для каждой строки (например, 24).
3 Колонка E высчитывается по формуле: «Количество рабочих дней (из них пропущено по больничному)» + «Количество рабочих дней (из них пропущено по другим причинам)».
4 Поле «Количество отработанных дней» вычисляется по формуле: «Количество рабочих дней (в месяце)» - «Количество рабочих дней (из них пропущено всего)»
Для таблицы 1.18.
1 Колонки A и B – копии колонок A и B таблицы 1.16 (ссылки на соответствующие ячейки).
2 Колонка C заполняется повторяющимися кодами работ из таблицы 1.15.
3 Столбец «Название работы» вычисляется с помощью функции ВПР и значений из таблицы 1.15.
4 Колонка «Повременно» вычисляется по формуле:
«Оклад» / «Количество рабочих дней (в месяце)» * «Количество отработанных дней».
5 Колонка «Районный коэффициент» вычисляется по формуле:
30% от «Оклада» + 10% от «Оклада» за каждые 2 полных года работы.
6 Премия начисляется в размере оклада, если человек не пропустил ни одного рабочего дня (см. таблицу 1.16).
7 Колонка «Подоходный налог» вычисляется по формуле:
(«Повременно» + «Районный коэффициент» + «Премия») * 0,13.
8 Колонка «Пенсионный взнос» высчитывается по формуле:
(«Повременно» + «Районный коэффициент» + «Премия») * 0,01.
9 Колонка «Профсоюзный взнос» высчитывается по формуле:
(«Повременно» + «Районный коэффициент» + «Премия») * 0,01.
10 Колонка «Итого на руки» высчитывается по формуле:
(«Повременно» + «Районный коэффициент» + «Премия») – («Подоходный налог» + «Пенсионный взнос» + «Профсоюзный взнос»).
11 Для колонок «Подоходный налог», «Профсоюзный взнос», «Итого на руки» использовать числовой формат с округлением до двух знаков после запятой.
Задания
Задания следует выполнять на листе «Расчет» под таблицей, вводя пояснительный текст.
1 Подсчитать двумя способами: сколько людей проболело более 10 дней. Для расчета по 1-му способу использовать функцию СЧЁТЕСЛИ, для расчета по 2-му способу - табличный вид формулы и функции СЧЕТ, ЕСЛИ.
2 Подсчитать количество людей пенсионного возраста, т.е. тех, кому за 60 лет. Для расчета предварительно на листе «Кадры» ввести новый столбец «Возраст» и вычислить возраст рабочих. Затем воспользоваться табличныым видом формулы и функциями СУММ, ЕСЛИ.
3 Найти название работы, которую выполнял рабочий по фамилии Иванов (функция ВПР).
4 Найти максимальный оклад.
5 Посчитать двумя способами сумму выплаченной премии, которую получили работники за 20 и более дней работы и районным коэффициентом более 8000 р. Для расчета по 1-му способу использовать функцию СУММЕСЛИ, предварительно создав вспомогательный столбец с метками для упрощения условия. Для расчета по 2-му способу использовать табличный вид формулы и функции СУММ и ЕСЛИ.