Вопрос или проблема
У меня есть таблица транзакций оборудования между магазинами, которая выглядит так:
Предполагается, что она будет обновляться – новые транзакции будут добавляться ниже, с участием других магазинов. Я пытаюсь сделать своего рода сводную таблицу, которая будет учитывать и источник, и получателя, и рассчитывать баланс переданных товаров – если магазин был источником в транзакции, количество должно вычитаться из общего числа, если магазин был получателем, количество должно прибавляться. Также следует учитывать тип оборудования. Я представляю это так:
Вам нужно перестроить данные, чтобы использовать такую сводную таблицу. Вставьте это на новый лист:
| Магазин отправитель | Товар | Магазин получатель | Количество | =IF(COUNTA(A:A)*2-COUNTA(H:H)-1<=0,"СПИСОК ПОКРЫТ","НЕПОЛНЫЙ СПИСОК") |
Магазин | Товар | Количество | ||
|---|---|---|---|---|---|---|---|---|---|
| Shop001 | Офисная лампа | Shop004 | 5 | =INDEX(IF(ISODD(ROWS($H$2:H2)),INDIRECT("A:A"),INDIRECT("C:C")),QUOTIENT(ROWS($H$2:H2)+1,2)+1) |
=INDEX(B:B,QUOTIENT(ROWS($H$2:H2)+1,2)+1) |
=IF(ISODD(ROWS($H$2:H2)),-1,1)*INDEX(D:D,QUOTIENT(ROWS($H$2:H2)+1,2)+1) |
|||
| Shop001 | Офисная лампа | Shop005 | 6 | =INDEX(IF(ISODD(ROWS($H$2:H3)),INDIRECT("A:A"),INDIRECT("C:C")),QUOTIENT(ROWS($H$2:H3)+1,2)+1) |
=INDEX(B:B,QUOTIENT(ROWS($H$2:H3)+1,2)+1) |
=IF(ISODD(ROWS($H$2:H3)),-1,1)*INDEX(D:D,QUOTIENT(ROWS($H$2:H3)+1,2)+1) |
|||
| Shop001 | Складская лампа | Shop004 | 7 | =INDEX(IF(ISODD(ROWS($H$2:H4)),INDIRECT("A:A"),INDIRECT("C:C")),QUOTIENT(ROWS($H$2:H4)+1,2)+1) |
=INDEX(B:B,QUOTIENT(ROWS($H$2:H4)+1,2)+1) |
=IF(ISODD(ROWS($H$2:H4)),-1,1)*INDEX(D:D,QUOTIENT(ROWS($H$2:H4)+1,2)+1) |
|||
| Shop001 | Другая лампа | Shop007 | 1 | =INDEX(IF(ISODD(ROWS($H$2:H5)),INDIRECT("A:A"),INDIRECT("C:C")),QUOTIENT(ROWS($H$2:H5)+1,2)+1) |
=INDEX(B:B,QUOTIENT(ROWS($H$2:H5)+1,2)+1) |
=IF(ISODD(ROWS($H$2:H5)),-1,1)*INDEX(D:D,QUOTIENT(ROWS($H$2:H5)+1,2)+1) |
|||
| Shop001 | Кондиционер | Shop007 | 1 | =INDEX(IF(ISODD(ROWS($H$2:H6)),INDIRECT("A:A"),INDIRECT("C:C")),QUOTIENT(ROWS($H$2:H6)+1,2)+1) |
=INDEX(B:B,QUOTIENT(ROWS($H$2:H6)+1,2)+1) |
=IF(ISODD(ROWS($H$2:H6)),-1,1)*INDEX(D:D,QUOTIENT(ROWS($H$2:H6)+1,2)+1) |
|||
| Shop001 | Обогреватель | Shop007 | 1 | =INDEX(IF(ISODD(ROWS($H$2:H7)),INDIRECT("A:A"),INDIRECT("C:C")),QUOTIENT(ROWS($H$2:H7)+1,2)+1) |
=INDEX(B:B,QUOTIENT(ROWS($H$2:H7)+1,2)+1) |
=IF(ISODD(ROWS($H$2:H7)),-1,1)*INDEX(D:D,QUOTIENT(ROWS($H$2:H7)+1,2)+1) |
|||
| Shop001 | Щитовое оборудование | Shop007 | 2 | =INDEX(IF(ISODD(ROWS($H$2:H8)),INDIRECT("A:A"),INDIRECT("C:C")),QUOTIENT(ROWS($H$2:H8)+1,2)+1) |
=INDEX(B:B,QUOTIENT(ROWS($H$2:H8)+1,2)+1) |
=IF(ISODD(ROWS($H$2:H8)),-1,1)*INDEX(D:D,QUOTIENT(ROWS($H$2:H8)+1,2)+1) |
|||
| Shop002 | Офисная лампа | Shop005 | 5 | =INDEX(IF(ISODD(ROWS($H$2:H9)),INDIRECT("A:A"),INDIRECT("C:C")),QUOTIENT(ROWS($H$2:H9)+1,2)+1) |
=INDEX(B:B,QUOTIENT(ROWS($H$2:H9)+1,2)+1) |
=IF(ISODD(ROWS($H$2:H9)),-1,1)*INDEX(D:D,QUOTIENT(ROWS($H$2:H9)+1,2)+1) |
|||
| Shop002 | Складская лампа | Shop005 | 3 | =INDEX(IF(ISODD(ROWS($H$2:H10)),INDIRECT("A:A"),INDIRECT("C:C")),QUOTIENT(ROWS($H$2:H10)+1,2)+1) |
=INDEX(B:B,QUOTIENT(ROWS($H$2:H10)+1,2)+1) |
=IF(ISODD(ROWS($H$2:H10)),-1,1)*INDEX(D:D,QUOTIENT(ROWS($H$2:H10)+1,2)+1) |
|||
| Shop002 | Другая лампа | Shop005 | 3 | =INDEX(IF(ISODD(ROWS($H$2:H11)),INDIRECT("A:A"),INDIRECT("C:C")),QUOTIENT(ROWS($H$2:H11)+1,2)+1) |
=INDEX(B:B,QUOTIENT(ROWS($H$2:H11)+1,2)+1) |
=IF(ISODD(ROWS($H$2:H11)),-1,1)*INDEX(D:D,QUOTIENT(ROWS($H$2:H11)+1,2)+1) |
|||
| Shop002 | Обогреватель | Shop005 | 3 | =INDEX(IF(ISODD(ROWS($H$2:H12)),INDIRECT("A:A"),INDIRECT("C:C")),QUOTIENT(ROWS($H$2:H12)+1,2)+1) |
=INDEX(B:B,QUOTIENT(ROWS($H$2:H12)+1,2)+1) |
=IF(ISODD(ROWS($H$2:H12)),-1,1)*INDEX(D:D,QUOTIENT(ROWS($H$2:H12)+1,2)+1) |
|||
| Shop002 | Обогреватель | Shop007 | 2 | =INDEX(IF(ISODD(ROWS($H$2:H13)),INDIRECT("A:A"),INDIRECT("C:C")),QUOTIENT(ROWS($H$2:H13)+1,2)+1) |
=INDEX(B:B,QUOTIENT(ROWS($H$2:H13)+1,2)+1) |
=IF(ISODD(ROWS($H$2:H13)),-1,1)*INDEX(D:D,QUOTIENT(ROWS($H$2:H13)+1,2)+1) |
|||
| Shop003 | Складская лампа | Shop007 | 10 | =INDEX(IF(ISODD(ROWS($H$2:H14)),INDIRECT("A:A"),INDIRECT("C:C")),QUOTIENT(ROWS($H$2:H14)+1,2)+1) |
=INDEX(B:B,QUOTIENT(ROWS($H$2:H14)+1,2)+1) |
=IF(ISODD(ROWS($H$2:H14)),-1,1)*INDEX(D:D,QUOTIENT(ROWS($H$2:H14)+1,2)+1) |
|||
| Shop003 | Другая лампа | Shop007 | 5 | =INDEX(IF(ISODD(ROWS($H$2:H15)),INDIRECT("A:A"),INDIRECT("C:C")),QUOTIENT(ROWS($H$2:H15)+1,2)+1) |
=INDEX(B:B,QUOTIENT(ROWS($H$2:H15)+1,2)+1) |
=IF(ISODD(ROWS($H$2:H15)),-1,1)*INDEX(D:D,QUOTIENT(ROWS($H$2:H15)+1,2)+1) |
|||
| Shop003 | Другая лампа | Shop005 | 5 | =INDEX(IF(ISODD(ROWS($H$2:H16)),INDIRECT("A:A"),INDIRECT("C:C")),QUOTIENT(ROWS($H$2:H16)+1,2)+1) |
=INDEX(B:B,QUOTIENT(ROWS($H$2:H16)+1,2)+1) |
=IF(ISODD(ROWS($H$2:H16)),-1,1)*INDEX(D:D,QUOTIENT(ROWS($H$2:H16)+1,2)+1) |
|||
| Shop003 | Другая лампа | Shop004 | 5 | =INDEX(IF(ISODD(ROWS($H$2:H17)),INDIRECT("A:A"),INDIRECT("C:C")),QUOTIENT(ROWS($H$2:H17)+1,2)+1) |
=INDEX(B:B,QUOTIENT(ROWS($H$2:H17)+1,2)+1) |
=IF(ISODD(ROWS($H$2:H17)),-1,1)*INDEX(D:D,QUOTIENT(ROWS($H$2:H17)+1,2)+1) |
|||
| Shop005 | Офисная лампа | Shop008 | 3 | =INDEX(IF(ISODD(ROWS($H$2:H18)),INDIRECT("A:A"),INDIRECT("C:C")),QUOTIENT(ROWS($H$2:H18)+1,2)+1) |
=INDEX(B:B,QUOTIENT(ROWS($H$2:H18)+1,2)+1) |
=IF(ISODD(ROWS($H$2:H18)),-1,1)*INDEX(D:D,QUOTIENT(ROWS($H$2:H18)+1,2)+1) |
|||
| Shop005 | Складская лампа | Shop008 | 2 | =INDEX(IF(ISODD(ROWS($H$2:H19)),INDIRECT("A:A"),INDIRECT("C:C")),QUOTIENT(ROWS($H$2:H19)+1,2)+1) |
=INDEX(B:B,QUOTIENT(ROWS($H$2:H19)+1,2)+1) |
=IF(ISODD(ROWS($H$2:H19)),-1,1)*INDEX(D:D,QUOTIENT(ROWS($H$2:H19)+1,2)+1) |
|||
=INDEX(IF(ISODD(ROWS($H$2:H20)),INDIRECT("A:A"),INDIRECT("C:C")),QUOTIENT(ROWS($H$2:H20)+1,2)+1) |
=INDEX(B:B,QUOTIENT(ROWS($H$2:H20)+1,2)+1) |
=IF(ISODD(ROWS($H$2:H20)),-1,1)*INDEX(D:D,QUOTIENT(ROWS($H$2:H20)+1,2)+1) |
|||||||
=INDEX(IF(ISODD(ROWS($H$2:H21)),INDIRECT("A:A"),INDIRECT("C:C")),QUOTIENT(ROWS($H$2:H21)+1,2)+1) |
=INDEX(B:B,QUOTIENT(ROWS($H$2:H21)+1,2)+1) |
=IF(ISODD(ROWS($H$2:H21)),-1,1)*INDEX(D:D,QUOTIENT(ROWS($H$2:H21)+1,2)+1) |
|||||||
=INDEX(IF(ISODD(ROWS($H$2:H22)),INDIRECT("A:A"),INDIRECT("C:C")),QUOTIENT(ROWS($H$2:H22)+1,2)+1) |
=INDEX(B:B,QUOTIENT(ROWS($H$2:H22)+1,2)+1) |
=IF(ISODD(ROWS($H$2:H22)),-1,1)*INDEX(D:D,QUOTIENT(ROWS($H$2:H22)+1,2)+1) |
|||||||
=INDEX(IF(ISODD(ROWS($H$2:H23)),INDIRECT("A:A"),INDIRECT("C:C")),QUOTIENT(ROWS($H$2:H23)+1,2)+1) |
=INDEX(B:B,QUOTIENT(ROWS($H$2:H23)+1,2)+1) |
=IF(ISODD(ROWS($H$2:H23)),-1,1)*INDEX(D:D,QUOTIENT(ROWS($H$2:H23)+1,2)+1) |
|||||||
=INDEX(IF(ISODD(ROWS($H$2:H24)),INDIRECT("A:A"),INDIRECT("C:C")),QUOTIENT(ROWS($H$2:H24)+1,2)+1) |
=INDEX(B:B,QUOTIENT(ROWS($H$2:H24)+1,2)+1) |
=IF(ISODD(ROWS($H$2:H24)),-1,1)*INDEX(D:D,QUOTIENT(ROWS($H$2:H24)+1,2)+1) |
|||||||
=INDEX(IF(ISODD(ROWS($H$2:H25)),INDIRECT("A:A"),INDIRECT("C:C")),QUOTIENT(ROWS($H$2:H25)+1,2)+1) |
=INDEX(B:B,QUOTIENT(ROWS($H$2:H25)+1,2)+1) |
=IF(ISODD(ROWS($H$2:H25)),-1,1)*INDEX(D:D,QUOTIENT(ROWS($H$2:H25)+1,2)+1) |
|||||||
=INDEX(IF(ISODD(ROWS($H$2:H26)),INDIRECT("A:A"),INDIRECT("C:C")),QUOTIENT(ROWS($H$2:H26)+1,2)+1) |
=INDEX(B:B,QUOTIENT(ROWS($H$2:H26)+1,2)+1) |
=IF(ISODD(ROWS($H$2:H26)),-1,1)*INDEX(D:D,QUOTIENT(ROWS($H$2:H26)+1,2)+1) |
|||||||
=INDEX(IF(ISODD(ROWS($H$2:H27)),INDIRECT("A:A"),INDIRECT("C:C")),QUOTIENT(ROWS($H$2:H27)+1,2)+1) |
=INDEX(B:B,QUOTIENT(ROWS($H$2:H27)+1,2)+1) |
=IF(ISODD(ROWS($H$2:H27)),-1,1)*INDEX(D:D,QUOTIENT(ROWS($H$2:H27)+1,2)+1) |
|||||||
=INDEX(IF(ISODD(ROWS($H$2:H28)),INDIRECT("A:A"),INDIRECT("C:C")),QUOTIENT(ROWS($H$2:H28)+1,2)+1) |
=INDEX(B:B,QUOTIENT(ROWS($H$2:H28)+1,2)+1) |
=IF(ISODD(ROWS($H$2:H28)),-1,1)*INDEX(D:D,QUOTIENT(ROWS($H$2:H28)+1,2)+1) |
|||||||
=INDEX(IF(ISODD(ROWS($H$2:H29)),INDIRECT("A:A"),INDIRECT("C:C")),QUOTIENT(ROWS($H$2:H29)+1,2)+1) |
=INDEX(B:B,QUOTIENT(ROWS($H$2:H29)+1,2)+1) |
=IF(ISODD(ROWS($H$2:H29)),-1,1)*INDEX(D:D,QUOTIENT(ROWS($H$2:H29)+1,2)+1) |
|||||||
=INDEX(IF(ISODD(ROWS($H$2:H30)),INDIRECT("A:A"),INDIRECT("C:C")),QUOTIENT(ROWS($H$2:H30)+1,2)+1) |
=INDEX(B:B,QUOTIENT(ROWS($H$2:H30)+1,2)+1) |
=IF(ISODD(ROWS($H$2:H30)),-1,1)*INDEX(D:D,QUOTIENT(ROWS($H$2:H30)+1,2)+1) |
|||||||
=INDEX(IF(ISODD(ROWS($H$2:H31)),INDIRECT("A:A"),INDIRECT("C:C")),QUOTIENT(ROWS($H$2:H31)+1,2)+1) |
=INDEX(B:B,QUOTIENT(ROWS($H$2:H31)+1,2)+1) |
=IF(ISODD(ROWS($H$2:H31)),-1,1)*INDEX(D:D,QUOTIENT(ROWS($H$2:H31)+1,2)+1) |
|||||||
=INDEX(IF(ISODD(ROWS($H$2:H32)),INDIRECT("A:A"),INDIRECT("C:C")),QUOTIENT(ROWS($H$2:H32)+1,2)+1) |
=INDEX(B:B,QUOTIENT(ROWS($H$2:H32)+1,2)+1) |
=IF(ISODD(ROWS($H$2:H32)),-1,1)*INDEX(D:D,QUOTIENT(ROWS($H$2:H32)+1,2)+1) |
|||||||
=INDEX(IF(ISODD(ROWS($H$2:H33)),INDIRECT("A:A"),INDIRECT("C:C")),QUOTIENT(ROWS($H$2:H33)+1,2)+1) |
=INDEX(B:B,QUOTIENT(ROWS($H$2:H33)+1,2)+1) |
=IF(ISODD(ROWS($H$2:H33)),-1,1)*INDEX(D:D,QUOTIENT(ROWS($H$2:H33)+1,2)+1) |
|||||||
=INDEX(IF(ISODD(ROWS($H$2:H34)),INDIRECT("A:A"),INDIRECT("C:C")),QUOTIENT(ROWS($H$2:H34)+1,2)+1) |
=INDEX(B:B,QUOTIENT(ROWS($H$2:H34)+1,2)+1) |
=IF(ISODD(ROWS($H$2:H34)),-1,1)*INDEX(D:D,QUOTIENT(ROWS($H$2:H34)+1,2)+1) |
|||||||
=INDEX(IF(ISODD(ROWS($H$2:H35)),INDIRECT("A:A"),INDIRECT("C:C")),QUOTIENT(ROWS($H$2:H35)+1,2)+1) |
=INDEX(B:B,QUOTIENT(ROWS($H$2:H35)+1,2)+1) |
=IF(ISODD(ROWS($H$2:H35)),-1,1)*INDEX(D:D,QUOTIENT(ROWS($H$2:H35)+1,2)+1) |
|||||||
=INDEX(IF(ISODD(ROWS($H$2:H36)),INDIRECT("A:A"),INDIRECT("C:C")),QUOTIENT(ROWS($H$2:H36)+1,2)+1) |
=INDEX(B:B,QUOTIENT(ROWS($H$2:H36)+1,2)+1) |
=IF(ISODD(ROWS($H$2:H36)),-1,1)*INDEX(D:D,QUOTIENT(ROWS($H$2:H36)+1,2)+1) |
|||||||
=INDEX(IF(ISODD(ROWS($H$2:H37)),INDIRECT("A:A"),INDIRECT("C:C")),QUOTIENT(ROWS($H$2:H37)+1,2)+1) |
=INDEX(B:B,QUOTIENT(ROWS($H$2:H37)+1,2)+1) |
=IF(ISODD(ROWS($H$2:H37)),-1,1)*INDEX(D:D,QUOTIENT(ROWS($H$2:H37)+1,2)+1) |
Теперь вы сможете использовать сводную таблицу. Необходимо правильно протянуть формулы вашей итоговой таблицы, чтобы она содержала в два раза больше строк, чем оригинальные данные. Ячейка F1 напомнит вам об этом. Если у вас Excel 365, могут быть более подходящие решения, которые автоматически настроят таблицу. Также вы можете перестроить ее вручную; это не так уж и много работы.
В Office 365 вы можете использовать:
=LET(range,A2:D19,GROUPBY(WRAPROWS(TOCOL(CHOOSECOLS(range,1,2,3,2)),2),TOCOL(CHOOSECOLS(range,4)*{-1,1}),SUM,,0))
Мы вводим весь диапазон с именами и значениями как range
Далее мы используем CHOOSECOLS, чтобы соединить 1-й, 2-й, 3-й и 2-й столбцы из range, чтобы получить магазин источник и его товар и магазин получатель и его товар. Комбинация wraprows/tocol объединяет 4 столбца в 2 столбца. Этот массив — первый ввод для GROUPBY.
Далее мы берем 4-й столбец range и делаем колонку, умноженную на -1, и одну, умноженную на 1. TOCOL объединяет эти колонки в один столбец той же высоты, что и первый ввод.
Здесь мы затем суммируем в GROUPBY (и не показываем итогов).
Результат готов.
.
Ответ или решение
Для создания сводной таблицы, которая будет учитывать все транзакции между магазинами и подсчитывать остатки товаров на складе, необходимо учесть несколько аспектов. Процесс создания такой таблицы может показаться сложным, но с правильным подходом можно эффективно справиться с поставленной задачей.
Теория
Сводные таблицы в Excel позволяют обобщать, анализировать и представлять данные в удобном для восприятия виде. В контексте задачи у нас есть таблица, где фиксируются транзакции товаров между магазинами. Каждая запись в таблице содержит исходный магазин, магазин-получатель, наименование товара и количество товара. Основная цель — посчитать итоговый баланс товаров для каждого магазина, где каждая транзакция товара от магазина будет вычитаться из его общего количества, а поступление товара, наоборот, складываться.
Чтобы реализовать это, необходимо трансформировать исходные данные так, чтобы можно было применять функции сводной таблицы, поскольку они не предназначены для обработки вариантов "исходящий магазин" и "магазин-получатель" одновременно в одной строке. На этом этапе необходимо развернуть данные таким образом, чтобы для каждой транзакции получалось две записи: одна для списания, другая для прихода.
Пример
Представьте, что у вас есть следующая таблица транзакций:
| Исходный магазин | Товар | Магазин-получатель | Количество |
|---|---|---|---|
| Магазин001 | Рабочая лампа | Магазин004 | 5 |
| Магазин002 | Обогреватель | Магазин005 | 3 |
| и так далее… |
Чтобы эффективно создавать сводную таблицу, данные должны выглядеть следующим образом:
| Магазин | Товар | Сумма |
|---|---|---|
| Магазин001 | Рабочая лампа | -5 |
| Магазин004 | Рабочая лампа | 5 |
| Магазин002 | Обогреватель | -3 |
| Магазин005 | Обогреватель | 3 |
| и так далее… |
Применение
-
Подготовка данных: Прежде всего, создайте новый лист, куда будут копироваться данные с трансформацией. Добавьте формулы для расстановки данных в столбцах как показано выше. Это предполагает создание парных строк для каждого оригинального элемента данных, одна строка с отрицательным значением количества (для исходного магазина) и одна с положительным значением (для магазина-получателя). Одна из верных методик заключается в использовании таких функций, как
INDEX,IF, иROWS, которые позволяют чередовать значения строк, чтобы извлечь данные из исходной таблицы и трансформировать их. -
Создание сводной таблицы: После преобразования данных, вы можете создать сводную таблицу. Для этого:
- Выделите новую таблицу, включая все данные, которые были преобразованы.
- Вставьте сводную таблицу.
- Перетащите "Магазин" в строки, "Товар" в столбцы, а "Сумма" в значения.
- Убедитесь, что в настройках значений установлена функция
Сумма.
-
Анализ результатов: После создания сводной таблицы вы сможете легко визуализировать итоги по каждому магазину и товару. Это поможет понять, какие товары необходимо снова закупить или переместить между магазинами, чтобы восстановить баланс.
В Office 365 можно использовать более современные функции, такие как LET и GROUPBY, которые позволяют автоматизировать процесс преобразования данных и улучшить производительность расчётов.
Создание полной и корректной сводной таблицы требует начального времени на установку и внедрение необходимых формул, но дальнейшая работа с данными становится значительно проще. Это позволяет компаниям легко отслеживать потоки товаров между магазинами без необходимости постоянного ручного ввода и расчетов.