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 }

Leave a Comment

Previous post:

Next post: