Jak mogę „grupować według” i sumować kolumnę w programie Excel?


144

Próbuję dowiedzieć się, jak „grupować według” wartości w arkuszu kalkulacyjnym Excel.

Biorąc pod uwagę następujący przykład (jest to arkusz zapasów, którego używam w mojej restauracji :)

Przykładowy arkusz kalkulacyjny

Po prawej stronie arkusza kalkulacyjnego znajduje się kolumna o nazwie Koszt sprzedanych towarów.

Chciałbym móc przedstawić całkowitą liczbę COGS dla każdej kategorii.

Czy jest to możliwe w programie Excel? Co więcej, chciałbym to zrobić w osobnym arkuszu, aby nie wpływać na pierwszy arkusz (na zdjęciu).

Jakieś pomysły?

Dzięki!

Odpowiedzi:



58

Możesz to również zrobić za pomocą funkcji Suma częściowa.

  1. Kliknij kartę Dane na pasku narzędzi wstążki programu Excel
  2. Kliknij przycisk Sortuj i posortuj według kolumny kategorii
  3. Kliknij przycisk Suma częściowa i wypełnij odpowiednie okno dialogowe, a następnie kliknij przycisk OK

Na przykład...

Przy każdej zmianie w: Kategoria

Użyj funkcji: Suma

Dodaj sumę częściową do: Koszt sprzedanych towarów


3
Gdzie jest przycisk sumy częściowej? Czy możesz dodać zrzut ekranu?
Matthew Lock


15

Zakładam, że szukasz całkowitego COGS dla konta takiego jak sklep spożywczy, na przykład w pierwszym tygodniu.

sumifto najprostszy sposób na uzyskanie tych danych. przykład z twojego zdjęcia:

=sumif(d2:d?,"grocery",j2:j?)

Umieściłem znaki zapytania, ponieważ nie widzę całego arkusza. Wybrałbyś całą kolumnę D, w której masz dane konta. D2 do D ?, to samo z kolumną tygodniowego użytkowania od J2 do J ?.

Formuła będzie szukać w kolumnie D tekstu spożywczego, jeśli jest obecny, doda komórki w kolumnie J, które odpowiadają kolumnom D zawierającym słowo spożywczy. (lub jakikolwiek tekst, który wstawiłeś do formuły, aby go szukać)


To świetne i super łatwe rozwiązanie. Tabele przestawne są przesadne w przypadku wielu takich potrzeb.
T. Brian Jones

5

Najbardziej trywialnym podejściem może być operacja sortowania w programie Excel, a następnie operacja podsumowania w programie Excel.

Daje to, czego szukasz, bez zmiany całego układu danych, tak jak w przypadku operacji przestawnej.

Sortowanie

  • Kliknij kartę Dane (lub naciśnij Alt-A na klawiaturze)
  • Kliknij Sortuj (lub naciśnij S na klawiaturze)
  • Kliknij strzałkę rozwijaną obok „Sortuj według” w głównym obszarze wyskakującego okienka.
  • Wybierz „Kategoria”, ponieważ według tego chcesz pogrupować.
  • Kliknij OK!

Suma częściowa

  • Kliknij kartę Dane (lub naciśnij Alt-A na klawiaturze)
  • Kliknij ikonę sumy częściowej (lub naciśnij B na klawiaturze)
  • Teraz, gdy Twoje dane są posortowane, przeczytaj i wypełnij wyskakujące okienko częściowe tak, jakbyś czytał naturalny angielski :
           Przy każdej zmianie w
              Kategoria (kliknij strzałkę menu i wybierz to)
           Użyj funkcji
              Suma (kliknij strzałkę menu rozwijanego i wybierz to)
           Dodaj sumę częściową do
              Koszt dobrej sprzedaży (wybierz kolumny, które chcesz zsumować dla poszczególnych kategorii)

       Click OK!
    


  • 4

    W programie Excel 2013 będzie dostępna opcja „grupowania”.

    1. Wybierz swój stół.
    2. Przejdź do zakładki Dane.
    3. Kliknij opcję Struktura> Grupowanie.

    Zobacz zrzut ekranu poniżej, niestety, jest niemiecki, ale ikony pomogą ci go znaleźć.

    Gliederung> Gruppieren


    4

    Aby to zrobić za pomocą tabeli przestawnej :

    1) Wybierz wszystkie dane (w tym etykiety kolumn)
    2) Wstaw> Tabela przestawna
    3) Przeciągnij wymiar, aby pogrupować według, w obszar kolumn lub wierszy
    4) Przeciągnij metrykę, aby zsumować w obszarze wartości

    wprowadź opis zdjęcia tutaj


    To powinna być zaakceptowana odpowiedź
    Jordan Morris

    1
    Excel v.16.28 ma Data/ Summarize with PivotTablezamiast „Wstaw”.
    węgorz ghEEz

    1

    Robię to cały czas za pomocą VBA. Jestem prawie pewien, że zastosowałem tę samą metodę od pakietu Office 95 ', z niewielkimi zmianami dotyczącymi umieszczania kolumn. Można to zrobić z mniejszą liczbą wierszy, jeśli nie zdefiniujesz zmiennych. Można to zrobić szybciej, jeśli masz wiele linii do przejścia lub więcej rzeczy, za pomocą których musisz zdefiniować grupę.

    Natrafiłem na sytuacje, w których „grupa” opiera się na 2-5 komórkach. Ten przykład dotyczy tylko jednej kolumny, ale można ją łatwo rozszerzyć, jeśli ktoś poświęci czas na zabawę.

    Zakłada to 3 kolumny i musisz sortować według kolumny group_values. Przed uruchomieniem makra wybierz pierwszą komórkę, którą chcesz porównać, w kolumnie wartości_grupy.

    'group_values, some_number, empty_columnToHoldSubtotals
    „(rzeczy idą tutaj)
    „plik cookie 1 pusty
    „ciasteczko 3 puste
    „ciasto 4 puste
    „kapelusz 0 pusty
    „kapelusz 3 pusty
    „...
    'zatrzymać
    
    Suma częściowa ()
      'zdefiniuj dwa ciągi i sumę częściową
      Dim thisOne, thatOne As String
      Dim subCount As Double
    
      „seeduj wartości
      thisOne = ActiveCell.Value
      thatOne = ActiveCell.Offset (1, 0)
      subCount = 0
    
      'skonfiguruj pętlę, która będzie działać, aż osiągnie wartość stop
      Podczas gdy (ActiveCell.Value <> „stop”)
    
        porównuje wartość komórki z komórką pod nią.
        Jeśli (thisOne = thatOne) To
          „jeśli komórki są równe, liczba wierszy jest dodawana do subkwota
          subCount = subCount + ActiveCell.Offset (0, 1) .Wartość
    
        Jeszcze
          „jeśli komórki nie są równe, subkonto jest zapisywane, a suma częściowa resetowana.
          ActiveCell.Offset (0, 2) .Value = ActiveCell.Offset (0, 1) .Value + subCount
          subCount = 0
    
        End If
    
        „wybierz następną komórkę w dół
        ActiveCell.Offset (1, 0). Wybierz
    
        'przypisz wartości aktywnej komórki i tej pod nią do zmiennych
        thisOne = ActiveCell.Value
        thatOne = ActiveCell.Offset (1, 0)
      Wend
    
    Napis końcowy
    

    -2

    1) Wybierz zakres danych.
    2) Skopiuj zakres do schowka.
    3) Otwórz nowy dostęp do bazy danych.
    4) Zaprojektuj nowy zakres tabeli lub wklej.
    5) Utwórz widok, widok SQL.
    6) Utwórz zapytanie „WYBIERZ komórkę, SUMĘ (dane) JAKO SUMOWO Z komórki GROUP GROUP BY”.


    Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
    Licensed under cc by-sa 3.0 with attribution required.