Как я могу «сгруппировать по» и суммировать столбец в Excel?

Я пытаюсь понять, как «группировать по» значениям в электронной таблице Excel.

Рассмотрим следующий пример (это инвентарный лист, который я использую в моем ресторане 🙂

В правом углу таблицы есть столбец называется «Стоимость проданных товаров».

Я хотел бы иметь возможность рассчитать общую сумму COGS для каждой категории.

Возможно ли это в Excel? Более того, я хотел бы сделать это на отдельном листе, чтобы не затрагивать первый лист (изображенный на рисунке).

Есть идеи?

Спасибо !


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


Вы также можете сделать это с помощью функции «Промежуточные итоги».

  1. Щелкните вкладку «Данные» на панели инструментов ленты Excel.
  2. Нажмите кнопку «Сортировка» и выполните сортировку по столбец категория
  3. Нажмите кнопку «Промежуточный итог» и заполните диалоговое окно соответствующим образом, затем нажмите «ОК».

Для пример …

При каждом изменении в: Категория

Использовать функцию: Сумма

Добавить промежуточный итог к: Стоимость проданных товаров


Вы также можете сделать это с помощью функции «Промежуточные итоги».

  1. Щелкните вкладку «Данные» на панели инструментов ленты Excel
  2. Нажмите кнопку «Сортировка» и отсортируйте по столбцу категории
  3. Нажмите кнопку «Промежуточный итог» и заполните диалоговое окно соответствующим образом. , затем нажмите ОК.

Например …

При каждом изменении в: Категория

Использовать функцию: Sum

Добавить промежуточный итог к: Стоимость проданных товаров


Я предполагаю, что вы ищете общие COGS для учетной записи, такой как бакалея, например для недели 1.

sumif — самый простой способ получить эти данные. пример взят с вашего изображения:

  = sumif (d2: d?, "бакалея", j2: j?)  

Я поставил вопросительные знаки, так как не могу видеть весь лист. Вы должны выбрать весь столбец D, в котором у вас есть данные учетной записи. D2 — D ?, То же самое с столбцом еженедельного использования J2 — J?.

Формула будет искать в столбце D текстовый продуктовый магазин, если он присутствует, добавляются ячейки в столбце J, соответствуют столбцам D, в которых есть слово бакалея. (или любой другой текст, который вы поместили в формулу для поиска)


Я предполагаю, что вы ищите общие COGS для учетной записи, такой как бакалея, например, за неделю 1.

sumif — самый простой способ получить эти данные. пример взят с вашего изображения:

  = sumif (d2: d?, "бакалея", j2: j?)  

Я поставил вопросительные знаки, так как не могу видеть весь лист. Вы должны выбрать весь столбец D, в котором у вас есть данные учетной записи. D2 — D ?, То же самое с столбцом еженедельного использования J2 — J?.

Формула будет искать в столбце D текстовый продуктовый магазин, если он присутствует, добавляются ячейки в столбце J, соответствуют столбцам D, в которых есть слово бакалея. (или любой другой текст, который вы поместили в формулу для поиска)


Для этого с помощью сводная таблица :

  1. Выбрать все данные (включая метки столбцов)
  2. Вставить> Сводная таблица
  3. Перетащите поле в измерение (строку или столбец) сводной таблицы для группировки по нему.
  4. Перетащите поле в область «значений», чтобы вычислить для него метрику; в данном случае Sum.


Чтобы сделать это с помощью сводной таблицы :

  1. Выберите все данные (включая метки столбцов).
  2. Вставить> Сводная таблица
  3. Перетащите поле в измерение (строку или столбец) сводной таблицы для группировки по it
  4. Перетащите поле в область «значений», чтобы вычислить для него метрику; в данном случае Sum.


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

Она дает то, что вы ищете без изменения всего макета ваших данных, как это сделала бы операция Pivot.

Сортировка

  • Нажмите Данные (или нажмите Alt-A на клавиатуре)
  • Нажмите Сортировать (или нажмите S на клавиатуре)
  • Щелкните стрелку раскрывающегося списка рядом с «Сортировать по» в основной области всплывающего окна.
  • Выберите «Категория», поскольку это то, по чему вы хотите сгруппировать.
  • Нажмите ОК!

Промежуточный итог

  • Нажмите Данные tab (или нажмите Alt-A на клавиатуре)
  • Щелкните значок Промежуточный итог (или нажмите B на клавиатуре)
  • Сейчас что ваши данные отсортированы, прочитаны и заполнили всплывающее окно промежуточных итогов, как если бы вы читали естественный английский :
     при каждом изменении в категории (щелкните стрелку раскрывающегося списка и выберите эту  s) Используйте функцию Sum (щелкните стрелку раскрывающегося списка и выберите это) Добавить промежуточный итог к стоимости хорошо проданных (выберите столбцы, которые вы хотите суммировать для отдельных категорий) 
      Нажмите OK!  


  • Самый тривиальный подход может быть операция сортировки Excel, за которой следует операция промежуточного итога Excel.

    Он дает то, что вы ищете, без изменения всего макета ваших данных, как при операции Pivot.

    Сортировка

    • Щелкните вкладку Данные (или нажмите Alt-A на клавиатуре).
    • Щелкните Сортировать (или нажмите S на клавиатуре).
    • Щелкните стрелку раскрывающегося списка рядом с «Сортировать по» в основной области всплывающего окна.
    • Выберите «Категория». так как это то, что вы хотите сгруппировать.
    • Нажмите OK!

    Промежуточный итог

  • Щелкните вкладку Данные (или нажмите Alt-A на клавиатуре).
  • Щелкните значок Промежуточный итог (или нажмите B на клавиатуре)
  • Теперь, когда ваши данные отсортированы, прочтите и заполните всплывающее окно Subtotal, как если бы вы читали естественный английский :
     При каждом изменении  в категории (щелкните стрелку раскрывающегося списка и выберите это) Используйте функцию Sum (щелкните стрелку раскрывающегося списка и выберите это) Добавить промежуточный итог к стоимости хорошо проданных (выберите столбцы, которые вы хотите суммировать для отдельных категорий) 
       Нажмите "ОК"!  


  • В Excel 2013 будет опция «группировки».

    1. Выберите свою таблицу.
    2. Перейдите на вкладку «Данные».
    3. Щелкните «Структура»> «Группировка».

    См. Снимок экрана ниже, к сожалению, он немецкий, но значки помогут вам найти его.


    В Excel 2013 будет опция «группировки».

    1. Выберите свою таблицу .
    2. Перейдите на вкладку «Данные».
    3. Щелкните «Структура»> «Группировка».

    См. снимок экрана ниже, к сожалению, это Немецкий, но значки помогут вам его найти.


    Я постоянно делаю это с помощью vba. Я почти уверен, что использовал тот же метод, начиная с Office 95 ‘, с небольшими изменениями, внесенными в размещение столбцов. Это можно сделать с меньшим количеством строк, если вы не определите переменные. Это можно сделать быстрее, если у вас есть много строк, которые нужно пройти, или больше вещей, с помощью которых вам нужно определить свою группу.

    Я сталкивался с ситуациями, когда « группа » основана на 2 -5 ячеек. В этом примере рассматривается только один столбец, но его можно легко развернуть, если кто-нибудь потратит время на то, чтобы поиграть с ним.

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

     'group_values, some_number, empty_columnToHoldSubtotals' (все идет сюда) 'cookie 1 пусто' cookie 3 пусто 'торт 4 пуст  'hat 0 empty'hat 3 empty' ... 'stop 
     Sub subtotal ()' определяет две строки и счетчик промежуточных итогов thingy Dim thisOne, thatOne As String Dim subCount As Double 'засевают значения  thisOne = ActiveCell.Value thatOne = ActiveCell. Offset (1, 0) subCount = 0 'устанавливает цикл, который будет продолжаться до тех пор, пока не достигнет стоп-значения. В то время как (ActiveCell.Value & lt & gt "stop")' сравнивает значение ячейки с ячейкой под ним.  If (thisOne = thatOne) Then 'если ячейки равны, количество строк добавляется к субсчету subCount = subCount + ActiveCell.Offset (0, 1) .Value Else', если ячейки не равны, субсчет записывается,  и промежуточный сброс.  ActiveCell.Offset (0, 2) .Value = ActiveCell.Offset (0, 1) .Value + subCount subCount = 0 End If 'выберите следующую ячейку ниже ActiveCell.Offset (1, 0) .Select' присвойте значения  активная ячейка и ячейка под ней к переменным thisOne = ActiveCell.Value thatOne = ActiveCell.Offset (1, 0) WendEnd Sub 


    Я постоянно делаю это с помощью vba. Я почти уверен, что использовал тот же метод, начиная с Office 95 ‘, с небольшими изменениями, внесенными в размещение столбцов. Это можно сделать с меньшим количеством строк, если вы не определите переменные. Это можно сделать быстрее, если у вас есть много строк, которые нужно пройти, или больше вещей, с помощью которых вам нужно определить свою группу.

    Я сталкивался с ситуациями, когда « группа » основана на 2 -5 ячеек. В этом примере рассматривается только один столбец, но его можно легко развернуть, если кто-нибудь потратит время на то, чтобы поиграть с ним.

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

     'group_values, some_number, empty_columnToHoldSubtotals' (все идет сюда) 'cookie 1 пусто' cookie 3 пусто 'торт 4 пуст  'hat 0 empty'hat 3 empty' ... 'stop 
     Sub subtotal ()' определяет две строки и счетчик промежуточных итогов thingy Dim thisOne, thatOne As String Dim subCount As Double 'засевают значения  thisOne = ActiveCell.Value thatOne = ActiveCell.Offset (1, 0) subCount = 0 'устанавливает цикл, который будет продолжаться до тех пор, пока он не достигнет стоп-значения. В то время как (ActiveCell.Value & lt & gt "stop")' сравнивает значение ячейки с ячейкой ниже  Это.  If (thisOne = thatOne) Then 'если ячейки равны, количество строк добавляется к субсчету subCount = subCount + ActiveCell.Offset (0, 1) .Value Else', если ячейки не равны, субсчет записывается,  и промежуточный сброс.  ActiveCell.Offset (0, 2) .Value = ActiveCell.Offset (0, 1) .Value + subCount subCount = 0 End If 'выберите следующую ячейку ниже ActiveCell.Offset (1, 0) .Select' присвойте значения  активная ячейка и ячейка под ней к переменным thisOne = ActiveCell.Value thatOne = ActiveCell.Offset (1, 0) WendEnd Sub 

    Оцените статью
    techsly.ru
    Добавить комментарий