Preskoči na glavno vsebino

Kako šteti vrstice, dokler v Excelu ne dosežemo določene vrednosti vsote?

Na primer, obstaja vrsta podatkov, stolpec A vključuje številko izdelka, stolpec B pa vsebuje količino naročila in imate določeno vrednost, na primer 80, kar je skupno število prvih n številk v vrstnem redu, kako lahko preštejete, koliko vrstice, ki seštejejo do 80, kot je prikazano spodaj?
štetje dokumentov, dokler vsota ne doseže 1

Štejte vrstice, dokler s formulo ne dosežete določene vsote vrednosti


puščica modri desni mehurček Štejte vrstice, dokler s formulo ne dosežete določene vsote vrednosti

Če želite prešteti število vrstic, ki se uporabljajo za seštevanje do določene ujemajoče se vrednosti, lahko uporabite formulo.

Izberite prazno celico, na primer E2, in vnesite to formulo =MATCH(D2,SUBTOTAL(9,OFFSET(B2,,,ROW(B2:B17)-ROW(B2))),1), pritisnite Shift + Ctrl + Enter tipko, da pridete do pravilnega rezultata. Oglejte si posnetek zaslona:
štetje dokumentov, dokler vsota ne doseže 2

Nasvet: V tej formuli je D2 določena vrednost, ki jo želite doseči s seštevanjem, B2: B17 je obseg vrednosti, od katerega seštejete, lahko jih spremenite po potrebi.

Če želite izključiti vrstico z glavo, lahko uporabite to formulo =MATCH(D2,SUBTOTAL(9,INDIRECT("$B$1"&":"&ADDRESS(ROW($B$1:$B$17),COLUMN($B$1)))), in pritisnite  Shift + Ctrl + Enter tipke hkrati.

Najboljša pisarniška orodja za produktivnost

Priljubljene funkcije: Poiščite, označite ali identificirajte dvojnike   |  Izbriši prazne vrstice   |  Združite stolpce ali celice brez izgube podatkov   |   Krog brez formule ...
Super iskanje: Več kriterijev VLookup    Multiple Value VLookup  |   VLookup na več listih   |   Nejasno iskanje ....
Napredni spustni seznam: Hitro ustvarite spustni seznam   |  Odvisni spustni seznam   |  Večkrat izberite spustni seznam ....
Upravitelj stolpcev: Dodajte določeno število stolpcev  |  Premakni stolpce  |  Preklop stanja vidnosti skritih stolpcev  |  Primerjaj obsege in stolpce ...
Predstavljene funkcije: Mrežni fokus   |  Pogled oblikovanja   |   Velika vrstica formule    Upravitelj delovnih zvezkov in listov   |  Knjižnica virov (Samodejno besedilo)   |  Izbirnik datuma   |  Združite delovne liste   |  Šifriranje/dešifriranje celic    Pošljite e-pošto po seznamu   |  Super filter   |   Poseben filter (filter krepko/ležeče/prečrtano ...) ...
15 najboljših kompletov orodij12 Besedilo Orodja (dodajanje besedila, Odstrani znake,...)   |   50 + Graf Vrste (Gantt Chart,...)   |   40+ Praktično Formule (Izračunajte starost glede na rojstni dan,...)   |   19 vstavljanje Orodja (Vstavite kodo QR, Vstavi sliko s poti,...)   |   12 Pretvorba Orodja (Številke v besede, Pretvorba valut,...)   |   7 Spoji in razdeli Orodja (Napredne kombinirane vrstice, Razdeljene celice,...)   |   ... in več

Napolnite svoje Excelove spretnosti s Kutools za Excel in izkusite učinkovitost kot še nikoli prej. Kutools za Excel ponuja več kot 300 naprednih funkcij za povečanje produktivnosti in prihranek časa.  Kliknite tukaj, če želite pridobiti funkcijo, ki jo najbolj potrebujete...

zavihek kte 201905


Kartica Office prinaša vmesnik z zavihki v Office in poenostavi vaše delo

  • Omogočite urejanje in branje z zavihki v Wordu, Excelu, PowerPointu, Publisher, Access, Visio in Project.
  • Odprite in ustvarite več dokumentov v novih zavihkih istega okna in ne v novih oknih.
  • Poveča vašo produktivnost za 50%in vsak dan zmanjša na stotine klikov miške za vas!
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