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

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

Такая процедура называется в Excel промежуточными итогами. Для подсчета промежуточных итогов нужно выделить таблицу и нажать на кнопку «Промежуточный итог» на вкладке «Данные».

Упражнение 21.

Откройте с сетевого диска файл Продукты.

Подведите промежуточные итоги по продаже товаров каждым продавцом. Для этого:

1. Выделите всю таблицу и отсортируйте ее попродавцам.

2. Нажмите на кнопку «Промежуточный итог». В появившемся окне выберите:

ü При каждом изменении в: – Продавец;

ü Операция – Сумма;

ü Добавить итоги по: – Сбыт (т.р.)

И нажмите кнопку ОК.

В результате слева от таблицы появятся кнопки навигации, которые позволяют скрыть подробную информацию (кнопки –) или наоборот отобразить ее (кнопки +). Поэкспериментируйте с кнопками навигации по списку. Примерный вид таблицы должен быть таким:

Задание: Подсчитать среднее значение сбыта каждого наименования продуктов.

Перед этой процедурой следует произвести отмену вывода предыдущих итогов (в окне «Промежуточные итоги» нажать на кнопку «Убрать все»).

Сохраните результат в своей папке под именем Упражнение 21.

Проверьте свои знания:

1. Чем отличаются в Excel сортировка от фильтрации?

2. Можно ли сортировать данные в таблицах более чем по 3 уровням?

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

4. Для чего нужны сводные таблицы?

5. Как отменить фильтрацию?

6. Почему необходима сортировка перед подсчетом промежуточных итогов?

Работа с листами

Как уже говорилось выше, книга Excel состоит из нескольких листов. При создании новой книги их три: Лист1, Лист2 и Лист3. В Excel можно выполнять следующие операции с листами: добавление новых, удаление, переименование и др. Формулы, функции, диаграммы могут использовать данные, как со своего листа, так и с других.

Упражнение 22.

Откройте с сетевого диска файл Видео.

Задача: Допустим, что в конце каждого рабочего дня в центральном офисе менеджер подводит итоги объёмов продаж. Создайте для него сводную таблицу, используя средства связывания. Для этого:

Рассчитайте и отформатируйте таблицу на Листе1 в соответствии с образцом и аналогично на Листе2.

Переименуйте Лист1 в пл. Ленина, Лист2 в Б. Покровская, Лист3 в Итого. Для этого щелкните правой клавишей мыши по ярлыку листа и в контекстном меню выберите команду «Переименовать».

Подготовим итоговую таблицу на листе Итого. Для этого сделаем так, чтобы данные на этом листе были связаны с информацией на листах пл. Ленина, Б. Покровская:

§ Ручное связывание. Выделите ячейку – В3. Наберите знак «=». Перейдите на лист пл. Ленина и щелкнете по ячейке С8, затем нажмите Enter. В ячейке должна появиться формула «=пл. Ленина!C8». Аналогично следует поступить с другими ячейками этой строки.

§ Автоматическое связывание. Выделите исходный диапазон ячеек на листе Б. Покровская – С8:G8. Скопируйте его в буфер обмена. Выделите целевую ячейку на листе Итого – В4. Затем нажмите на стрелку кнопки «Вставить» и появившемся окне нажмите на кнопку «Вставить связь» в группе «Другие параметры вставки».

Отформатируйте таблицу. В результате вы получите следующую таблицу:

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

Сохраните результат под именем Упражнение 22.

Смешанные ссылки

При копировании формул иногда возникает ситуация, когда ссылка на ячейку должна изменяться при копировании вниз и не должна при копировании вправо. Для этого в формуле используются, так называемые, смешанные ссылки. Смешанные ссылки бывают двух видов, например В$2 – не меняется при копировании вниз, $B2 – не меняется при копировании вправо.

Смешанная ссылка получается преобразованием относительной, для этого в формуле выделяется относительная ячейка и нажимается несколько раз клавиша F4.

Упражнение 23.

Задание: Подготовьте шпаргалку для продавца мороженого.

Введите первоначальные данные:

Введите в ячейку С2 формулу для расчета: =В2*С1

Скопируйте эту формулу в ячейки С3:С7. Результат получился неверным. Проанализируем почему. Для этого посмотрите формулу в ячейке С3 (двойной щелчок мыши): =В3*С2, а должно быть =В3*С1.

Обратите внимание, что при копировании ячейка С1 изменилась на С2, что и привело к ошибке, а если быть совсем точным – цифра 1 изменилась на 2. Сделаем так, чтобы она не менялась – выделим снова формулу в ячейке С2, поставим курсор на ячейку С1

и нажимая несколько раз клавишу F4 измените формулу на =В2*С$1 и нажмите Enter.

Скопируйте заново эту формулу в ячейки С3:С7. Теперь результат верен.

Теперь скопируем эту формулу в ячейки D2:G2. Результат опять получился неверным. Проанализируйте почему (см. выше). Измените формулу. В результате вы должны получить формулу: =$В2*С$1

Эту формулу можно копировать и вниз, и вправо.

Отформатируйте таблицу по образцу.

Сохраните результат в свою папку под именем Упражнение 23.

Упражнение 24.

Задание: Подготовьте таблицу умножения.

В ячейку А1 введите текст – ТАБЛИЦА УМНОЖЕНИЯ

Для того, чтобы заполнить строку числами от 1 до 9, введите в ячейкуВ2число 1, и используя маркер заполнения и держа нажатой клавишу Ctrl, растяните 1 до 9 (см. рисунок)

Аналогичную операцию проделайте с ячейками А3:А11, числа от 1 до 9.

Задайте для всех столбцов одинаковую ширину – выделите их, на вкладке «Главная»нажмите кнопку «Формат» и выберите «Ширина столбца». Установите ширину – 5.

Введите в ячейку В3формулу: = А3*В2 Используя клавишу F4, добейтесь того, чтобы эту формулу можно было копировать и вниз, и вправо (см. предыдущее упражнение).

Отформатируйте таблицу согласно образцу.

Сохраните результат в своей папке под именем Упражнение 24.

Упражнение 25.

Переделайте полученную в предыдущем задании таблицу на таблицу квадратов (придётся добавить еще один столбец).

Сначала подготовим таблицу чисел, которые мы будем возводить в квадрат. В ячейке ВЗ должно находиться число из десятков, указанных в столбце и единиц, указанных в строке. Этому соответствует формула =АЗ*10+В2. Введите в ячейку ВЗ указанную формулу и скопируйте ее во все остальные ячейки (не забудьте про смешанные ссылки).

Займёмся возведением в степень.

Запомните формулу, которая у вас получилась в ячейке В3, и удалите содержимое ячейки. Вставьте функций «Степень» из категории«Математические».

В окне ввода аргументов наберите: Число – вашу формулу (не забудьте про $), Степень – 2.

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

Сохраните результат в своей папке под именем Упражнение 25.

Проверьте свои знания:

1. Какие бывают разновидности ссылок?

2. Адрес ячейки имеет вид: $А5. Какая это ссылка?

3. Для каких операций нужны абсолютные и смешанные ссылки?

4. Какая клавиша существует для переключения ссылок?

5. Сколько параметров сохраняется фиксированными в смешанных ссылках?

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

Упражнение 26.

Задание: Подготовьте таблицу для ежемесячного расчёта потребления электроэнергии в квартире при установленном двухтарифном счетчике.

Откройте с сетевого диска файл Оплата электроэнергии

Заполните, используя маркер заполнения, столбец Месяц.

Отформатируйте таблицу согласно образцу.

Рассчитайте количество и оплату потребляемой энергии за месяц (оплата электроэнергии производится по базовому тарифу из расчета до 25 кВт на одного проживающего в дневное время и по перерасходу – свыше 25 кВт, аналогично рассчитывается оплата в ночное время).

Подсказка:используйте функцию «Если» из категории «Логические».

Сохраните результат под именем Упражнение 26.

Упражнение 27.

Задание: Проведите анализ разговоров по мобильному телефону.

Для этого откройте с сетевого диска файл Ноябрь.

1. Переименуйте Лист1 в Ноябрь.

2. Скопируйте информацию, находящуюся в этом файле, в новый файл, для этого выполните следующие действия:

· щелкните правой клавишей мыши по ярлычку листа и выберите команду «Переместить или скопировать лист».

· в появившемся окне выберите цель переноса – новая книга, установить галочку «Создать копию».

3. Аналогично поступите с файлами Декабрь и Январь.

4. Отформатируйте таблицы согласно образцу.

5. Для удобства перемещения по таблице выполните следующее: выделите ячейку В8 и на вкладке «Вид»выберите команду «Закрепить области».

6. Рассчитайте ячейки С2-С5: общее количество звонков – функция ЧСТРОК из категории Ссылки и массивы; Местная связь, Междугородняя составляющая и Сумма (у.е.) – Автосумма.

7. Используя фильтрацию, удалите строки с нулевым балансом.

8. Определите сумму оплаты по каждому телефону. Для этого сначала отсортируйте таблицу по столбцу Номер телефона, а затем используйте команду Промежуточные итоги.

9. Аналогичную работу проделайте с остальными листами.

10. Постройте на отдельном листе график оплаты телефона за три месяца.

Сохраните результат под именем Упражнение 27.

Литература

1. Г.И. Сингаевская. Функции в Microsoft Office Excel 2010. –М.: Диалектика, 2011

2. Джон Уокенбах. Microsoft Excel 2010. Библия пользователя. – М.: Диалектика, 2011.

3. Джон Уокенбах. Формулы в Microsoft Excel 2010. – М.: Диалектика, 2011.

4. Кертис Д. Фрай. Microsoft Excel 2010. Русская версия. – М.: ЭКОМ Паблишерз, 2011.

5. www.microsoft.com