Poszukiwanie określonej sumy wartości na liście

by admin

W jednym z komentarzy na blogu niejaki „zupan” zadał mi następujące pytanie (tak, tak …..jest szansa na to, że będę regularnie odpowiadał na blogu na Wasze pytania przysyłane do mnie mailem.)

Czy istnieje prosta metoda policzenia pozycji w kolumnie dającej określoną sumę kolejnych wartości? np mam kolumne a w niej 1,2,3,4,5,…. i chce policzyć ile pozycji da mi sumę 6 licząc od pierwszej pozycji ( lub wybranej))))

Moje rozwiązanie problemu (pierwsze jakie przyszło mi do głowy) poniżej. Nie jest może najbardziej eleganckie, jak wszystko w Excelu można rozwiązać problem także na inne sposoby (sam mam w głowie dwa dodatkowe, w tym jeden z wykorzystaniem formuł tablicowych), ale …JEST i z tego co widzę działa :) Dowód na animacji poniżej.

Poszukiwanie określonej sumy liczb

Rozwiązanie problemu

Jak widzisz na animacji, po lewej stronie znajduje się kolumna z liczbami. Dla jasności – liczby nie muszą być wpisane po kolei ani w porządku rosnącym. Zakresowi temu (C10:C29) nadałem nazwę „zakres_liczby”.

Obok znajduje się kolumna pomocnicza, z której korzystam dla ułatwienia. Ten zakres z kolei (E10:E29) otrzymał nazwę „zakres_pokalkulacji”. W kolumnie tej znajdują się obliczone sumy odpowiednich liczb z pierwszej kolumny. Od pozycji początkowej (którą można sobie ustawić w komórce obok) do pozycji odpowiadającej danemu (aktywnemu) wierszowi. W swoim rozwiązaniu korzystam także z innych zdefiniowanych nazw.

Pozycja początkowa naszych poszukiwań (H9) otrzymała nazwę „kalkulacja_start”, a pierwsza komórka zakresu źródłowego z liczbami (C10) otrzymała nazwę „zakres_start”.

Oprócz tego zdefiniowałem jeszcze jedna WAŻNĄ  nazwę. Jest ona na tyle szczególna, że to właśnie „w niej” znajdują się formuły rozwiązujące nasz problem. Jako nazwę wybrałem „zakres_dokalkulacji”, a zdefiniowałem ją następująco:

=JEŻELI(WIERSZ()<WIERSZ(zakres_start)+kalkulacja_start;0;PRZESUNIĘCIE(zakres_liczby;kalkulacja_start-1;0;WIERSZ()-WIERSZ(PRZESUNIĘCIE(zakres_start;kalkulacja_start-2;0;1;1))))

To co chciałem osiągnąć to zdefiniowanie nazwy, która w każdym momencie (dla każdej komórki w kolumnie pomocniczej) określi zakres rozpoczynający się na wybranej „pozycji początkowej”, a kończący na wierszu, w którym znajduje się kursor.

Posiadając tak zdefiniowaną nazwę wszystko co pozostaje to w pierwszej komórce kolumny pomocniczej (E10) wpisać formułę

=SUMA(zakres_dokalkulacji)

i po prostu skopiować w dół na pozostałe komórki.

Od tej pory możesz zmieniać wartości w komórce „pozycja początkowa” i wszystko powinno zostać ładnie przeliczone w kolumnie pomocniczej. Co zresztą widać na przykładowej animacji.To co pozostaje to odszukać pozycję liczby (sumy), której szukamy. W komórce H11 znajduje się więc formuła

=PODAJ.POZYCJĘ(H10;zakres_pokalkulacji;1)

Jeżeli poszukiwanej sumy nie ma na liście to formuła zwróci pozycję największej sumy mniejszej od sumy poszukiwanej. Jeżeli jednak poszukiwana suma jest na liście powinniście otrzymać pozycje dokładnie tej liczby (sumy).  Dodałem jeszcze jedną komórkę z delikatnie tylko zmienioną formułą

=PODAJ.POZYCJĘ(H10;zakres_pokalkulacji;1)+1

W przypadku nieznalezienia odpowiedniej sumy zwróci ona kolejną sumę większą od sumy poszukiwanej. Nieco „dziko” za to zachowuje się w przypadku kiedy poszukiwana suma jest na liście (ale wtedy formuła wspomniana wcześniej załatwia problem).

Tyle ode mnie. Jak zwykle czekam na komentarze i z przyjemnością zobaczę inne (Wasze) rozwiązania.

Marcin

Download

{ 3 comments… read them below or add one }

Piotr P.

O ile dobrze zrozumiałem :)
np.
start od pozycji 1; suma 15 ma dać w wyniku 5 (bo suma 5 kolejnych pozycji od 1 do 5 to 15)
start od pozycji 3; suma 33 ma dać w wyniku 6 (bo suma 6 kolejnych pozycji od 3 do 8 to 33)

to można to policzyć krócej – przekształcając wzór na sumę ciągu arytmetycznego
A1 (pozycja startowa)
B2 (suma)
formuła wyliczająca ile pozycji da mi sumę:
=0,5*(PIERWIASTEK((2*A1-1)^2+8*A2)-2*A1+1)

Marcin

@Piotr

Problem w tym, że to wcale nie musi być ciąg arytmetyczny. Akurat w moim przykładzie taki się pojawił, ale moje formuły powinny działać z dowolną kombinacją liczb nie tworzących żadnego ciągu. W każdym razie tak mi się wydaje. Gdyby jednak było inaczej proszę mnie śmiało „zjechać” i będę wtedy myslał o lepszym rozwiązaniu.

Marcin

Piotr

Witam
Otóż mam pytanie związane z makrami. Ponieważ jestem nowicjuszem
a mam do zrobienia jedną rzecz w excel’u, a mianowicie.
Mam 5 kolumn z danymi, pozycji jest (wierszy) coś ponad 14000 ale to jest mało istotne. W 6 kolumnie mam dodane checkbox’y dodane przez formanty formularza.
Muszę zrobić tak żeby jak zaznaczę pozycje w checkbox, to żeby się kopiowały zawartości 5 kolumn do innego arkusza w tym samym pliku.
zaznaczę inny checkbox to automatycznie ma być kopiowane zawartości kolumn pod tymi co wybrałem wcześniej.
Czyli jak zaznaczę 5 pozycji z tych 14000 to żeby się wyświetliły w osobnym arkuszu.Tak samo jak odznaczę to, żeby się skasowały. Na logikę to bym zaczął pisać funkcję aby zlokalizować zaznaczony checkbox, skopiować zawartość wiersza od A1:E1 a następnie wkleić do nowego arkusza.
Bardzo bym prosił o jakieś wskazówki.
Z góry dziękuje
Z poważaniem
Piotr Polnik

Leave a Comment

Previous post:

Next post: