Работа с несколькими листами
В определенных ситуациях вам, возможно, придется
- Создайте книги, в которых несколько листов имеют одинаковый формат или структуру.
- Получите информацию для этих листов из другого листа.
- Обобщите результаты этих листов в сводный рабочий лист.
Например, вам может потребоваться отслеживать информацию о продажах по регионам и месяцам в отдельных таблицах. Цена каждого продукта берется из каталога продуктов, который создается для всей компании на отдельном листе. Наконец, вы должны суммировать результаты по всем регионам в сводной таблице.
В этой главе вы узнаете, как сделать это за несколько простых шагов. Вы собираетесь подвести итоги с апреля 2015 года по март 2016 года, то есть 2015-16 финансового года.
Первый шаг
Первый шаг - создать каталог продукции. Следуйте инструкциям ниже -
- Начните с пустой книги.
- Создайте рабочий лист каталога продуктов с продуктами и ценами.
- Назовите рабочий лист Product Catalog.
- Предположим, что каталог обновляется первого числа каждого месяца.
- Предоставьте заполнитель для последнего обновления.
Цена продукта на момент продажи определяется текущей стоимостью продукта.
Несколько листов с одинаковой структурой
Затем вы должны настроить рабочие листы для регионов - Восток, Север, Юг и Запад в указанном порядке с той же структурой.
- Добавьте 4 пустых листа.
- Назовите листы Восток, Север, Юг и Запад.
Эти четыре листа должны иметь одинаковую структуру.
- Щелкните вкладку Восток. Откроется рабочий лист Востока.
- Нажмите клавишу Shift и перейдите на вкладку Запад. Будут выбраны все 4 вкладки.
Теперь любое редактирование, которое вы делаете на листе Востока, будет автоматически отражено в трех других выбранных листах.
На листе Востока
- Добавьте заголовки столбцов - Номер S., Месяц, Продукт, Цена, Количество единиц, Общая сумма.
- Добавьте номер S., месяц апрель и 4 названия продукта.
- Отформатируйте таблицу.
Такая же структура появляется на других листах Север, Юг и Запад.
Создание формулы на нескольких листах
Чтобы создать формулу на нескольких листах -
- Задайте имена для значений цен продуктов в листе каталога продуктов.
- Установите Область как рабочую книгу для всех имен.
- Еще раз выберите все четыре листа - Восток, Север, Юг и Запад.
- На листе Восток для каждого продукта в столбце цены укажите формулу в качестве имени значения цены.
Как мы определили ранее, цена продукта указана в каталоге продуктов, который обновляется первого числа каждого месяца.
- Повторяйте те же шаги для каждого месяца.
Следовательно, для рабочих листов для регионов Восток, Север, Юг и Запад вы успешно установили ту же структуру и разместили информацию о ценах для каждого продукта на основе месяца из рабочего листа каталога продуктов.
Каталог продуктов также может находиться в другой книге.
Расчеты в рабочих таблицах
Следующим шагом является заполнение информации о количестве проданных единиц для каждого продукта за каждый месяц и в каждом регионе. Поэтому вам нужно работать над этими листами отдельно.
Для каждого региона, для каждого продукта -
- Заполните количество проданных единиц.
- Рассчитайте соответствующую общую сумму как Цена * №. единиц.
На каждом листе (восток, север, юг и запад) рассчитайте промежуточные итоги по месяцам -
Note- Вы можете использовать «Промежуточный итог» на одном листе, но не на нескольких листах. Следовательно, вы должны повторить этот шаг для листов Север, Юг и Запад.
Щелкните уровень структуры 2. Вы получите все итоги за месяц.
Теперь вы готовы подвести итоги всех четырех рабочих листов - Востока, Севера, Юга и Запада.
Обобщение данных на нескольких листах
Следующие шаги покажут вам, как суммировать данные из нескольких листов.
- Добавьте рабочий лист и назовите его Сводка.
- Создайте структуру для сводного рабочего листа.
В столбце- Total Sales, в ячейке C3 введите =sum(
- Выберите рабочий лист East.
- Выберите ячейку G7.
- При нажатой вкладке Восток щелкните вкладку West.
- Выбираются вкладки с востока на запад.
- Формула в строке формул отображается как
=sum(‘East:West’!G7)
Обратите внимание, что вы все еще в Eastрабочий лист. Нажмите Ввод.
Вы попадете в итоговый рабочий лист. В строке формул вы увидите формулу как
=SUM(East:West!G7)
Рассчитанное значение появится в ячейке C3.
- Скопируйте формулу в ячейки с C4 по C14.
- Щелкните Показать формулы в группе Аудит формул на вкладке ФОРМУЛЫ.
Появятся все формулы в столбце Общий объем продаж.
Именно так вы хотели обобщить результаты по каждому региону.
- Щелкните ячейку C15.
- Тип =sum(C3:C14)
Обобщенные результаты готовы в сводной таблице.