Przejdź do głównej zawartości

Jak liczyć wiersze do osiągnięcia określonej wartości sumy w programie Excel?

Na przykład istnieje zakres danych, kolumna A zawiera numer produktu, a kolumna B zawiera kwotę zamówienia i masz pewną wartość, taką jak 80, która jest sumą pierwszych n-tych liczb w zamówieniu, jak możesz policzyć ile wiersze są zaangażowane w sumowanie do 80, jak pokazano na zrzucie ekranu?
doc liczy, aż suma osiągnie 1

Policz wiersze, aż za pomocą formuły zostanie osiągnięta określona sumaryczna wartość


strzałka niebieski prawy bąbelek Policz wiersze, aż za pomocą formuły zostanie osiągnięta określona sumaryczna wartość

Aby policzyć liczbę wierszy, które są używane do sumowania do określonej dopasowanej wartości, możesz zastosować formułę.

Wybierz pustą komórkę, na przykład E2, i wprowadź tę formułę =MATCH(D2,SUBTOTAL(9,OFFSET(B2,,,ROW(B2:B17)-ROW(B2))),1), naciśnij Shift + Ctrl + Enter aby uzyskać prawidłowy wynik. Zobacz zrzut ekranu:
doc liczy, aż suma osiągnie 2

Wskazówka: W tej formule D2 to pewna wartość, którą chcesz osiągnąć, sumując, B2: B17 to zakres wartości, z którego sumujesz, możesz je zmienić według potrzeb.

Jeśli chcesz wykluczyć wiersz nagłówka, możesz użyć tej formuły =MATCH(D2,SUBTOTAL(9,INDIRECT("$B$1"&":"&ADDRESS(ROW($B$1:$B$17),COLUMN($B$1))))i naciśnij  Shift + Ctrl + Enter klucze jednocześnie.

Najlepsze narzędzia biurowe

🤖 Pomocnik AI Kutools: Zrewolucjonizuj analizę danych w oparciu o: Inteligentne wykonanie   |  Wygeneruj kod  |  Twórz niestandardowe formuły  |  Analizuj dane i generuj wykresy  |  Wywołaj funkcje Kutools...
Popularne funkcje: Znajdź, wyróżnij lub zidentyfikuj duplikaty   |  Usuń puste wiersze   |  Łącz kolumny lub komórki bez utraty danych   |   Okrągły bez wzoru ...
Super wyszukiwanie: Wiele kryteriów VLookup    Wiele wartości VLookup  |   Przeglądanie pionowe na wielu arkuszach   |   Wyszukiwanie rozmyte ....
Zaawansowana lista rozwijana: Szybko twórz listę rozwijaną   |  Zależna lista rozwijana   |  Lista rozwijana wielokrotnego wyboru ....
Menedżer kolumn: Dodaj określoną liczbę kolumn  |  Przesuń kolumny  |  Przełącz stan widoczności ukrytych kolumn  |  Porównaj zakresy i kolumny ...
Polecane funkcje: Fokus siatki   |  Widok projektu   |   Duży pasek formuły    Menedżer skoroszytów i arkuszy   |  Biblioteka zasobów (Automatyczny tekst)   |  Selektor dat   |  Połącz arkusze   |  Szyfruj/odszyfruj komórki    Wysyłaj e-maile według listy   |  Super filtr   |   Specjalny filtr (filtruj pogrubienie/kursywa/przekreślenie...) ...
15 najlepszych zestawów narzędzi12 Tekst Tools (Dodaj tekst, Usuń znaki, ...)   |   50 + Wykres rodzaje (Wykres Gantta, ...)   |   40+ Praktyczne Wzory (Oblicz wiek na podstawie urodzin, ...)   |   19 Wprowadzenie Tools (Wstaw kod QR, Wstaw obraz ze ścieżki, ...)   |   12 Konwersja Tools (Liczby na słowa, Przeliczanie walut, ...)   |   7 Połącz i podziel Tools (Zaawansowane wiersze łączenia, Podział komórki, ...)   |   ... i więcej

Zwiększ swoje umiejętności Excela dzięki Kutools for Excel i doświadcz wydajności jak nigdy dotąd. Kutools dla programu Excel oferuje ponad 300 zaawansowanych funkcji zwiększających produktywność i oszczędzających czas.  Kliknij tutaj, aby uzyskać funkcję, której najbardziej potrzebujesz...

Opis


Karta Office wprowadza interfejs z zakładkami do pakietu Office i znacznie ułatwia pracę

  • Włącz edycję i czytanie na kartach w programach Word, Excel, PowerPoint, Publisher, Access, Visio i Project.
  • Otwieraj i twórz wiele dokumentów w nowych kartach tego samego okna, a nie w nowych oknach.
  • Zwiększa produktywność o 50% i redukuje setki kliknięć myszką każdego dnia!
Comments (8)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Hi, how would the formula be if instead of D2 I want it to be an established range of data?
This comment was minimized by the moderator on the site
MATCH(D2,SUBTOTAL(9,OFFSET(B2,,,ROW(B2:B17)-ROW(B2))),1)this formula is not working on google sheets any info about that
This comment was minimized by the moderator on the site
Hi, Suresh, above formula only work in Microsoft Excel, if you want to count rows until a target sum reach in google sheet, you need a helper column, and the COUNTIF function. Specific operations as below screenshot shown. Please let me know if this help you.
This comment was minimized by the moderator on the site
hi , what can be the formula but to count columns?
This comment was minimized by the moderator on the site
Hello, Diane, just change ROW to COLUMN in the formula like =MATCH(A2,SUBTOTAL(9,OFFSET(A1,,,COLUMN(A1:E1)-COLUMN(B2))),1). See screenshot:
This comment was minimized by the moderator on the site
This doesn't work - I think it's because subtotal only works vertically?
This comment was minimized by the moderator on the site
After reading this solution, I started reading about array formulas. Very interesting and useful. With respect to this solution, it solves a very challenging aspect of material stockpile size and composition tracking. Can you help me with one little change...I want the position in the list of the total going from the bottom of the list up vs top down in your example. I've tried a few things but not cracked it yet, mainly due to not fully understanding the formula syntax yet. Any suggestions? Particularly interested in the exclude header version, or a version that does not include any header consideration since I wish to calculate this fresh on each row (i.e. day.)
This comment was minimized by the moderator on the site
Never mind. I figured out a simple, non array way to do what I wanted to do.
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations