Работа с несколькими листами

В определенных ситуациях вам, возможно, придется

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

Например, вам может потребоваться отслеживать информацию о продажах по регионам и месяцам в отдельных таблицах. Цена каждого продукта берется из каталога продуктов, который создается для всей компании на отдельном листе. Наконец, вы должны суммировать результаты по всем регионам в сводной таблице.

В этой главе вы узнаете, как сделать это за несколько простых шагов. Вы собираетесь подвести итоги с апреля 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)

Обобщенные результаты готовы в сводной таблице.