Niedawno dostałem do rozwiązania względnie proste zadanie: wypełnić kolumnę Y w Excelu losowo wybranymi wartościami z kolumny X. Mogą się powtarzać.
Rozwiązanie jest łatwe, chociaż na pierwszy rzut oka nieoczywiste:
Wyznaczamy ilość wszystkich elementów N w kolumnie X
Następnie dla każdej komórki w kolumnie Y:
- Losujemy liczbę P z przedziału 1..N
- Wstawiamy P-ty element kolumny X do komórki w kolumnie Y.
A jak to zrobić w Excelu?
A tak:
No dobra, widać, że działa, ale jak to właściwie jest zrobione?
A tak:
Po rozebraniu tego na kawałki wygląda to następująco:
Funkcja INDEX potrzebuje trzech argumentów: tablicy, numeru wiersza oraz numeru kolumny; zwraca wartość z tablicy z zadanego wiersza / kolumny.
Tablica jest zakodowana na stałe: $A$2:$A$17 - to nasza jednokolumnowa tablica X.
Numer wiersza losujemy z wartości między 1 a COUNTA(tablica). COUNTA to z kolei funkcja, która zlicza ilość niepustych komórek w tablicy.
Numer kolumny ustawiamy na 1 bo X jest tablicą jednokolumnową.
Proste?
No pewnie że proste.
“Wyznaczamy ilość wszystkich elementów N w kolumnie X”
A jeśli tablica X ma zmienne rozmiary, czyli że można dopisywać kolejne pozycje lub usuwać istniejące? Da się bez makra? Problem polega teraz na wyznaczeniu aktualnego rozmiaru tablicy. Najprościej byłoby wprowadzić kolumnę “L.p.” z numerami pozycji w X, ale to niegodny banał. Więc?
Funkcja COUNTA tak naprawdę zlicza niepuste komórki. Już poprawiłem.
No jak? Nic nie poprawiłeś…
Aby to działało dla z-var-iowanego rozmiaru X, to pierwszy argument INDEXu musi być formułą zwracającą aktualny zakres tablicy.
Nie będę się mądrzył. Wprawdzie kiedyś byłem orłem z Excela, ale od kilku lat działam z konieczności wyłącznie w Open Office, a ten jest z formułami jakby trochę do tyłu. Za to zabawy jest znacznie więcej.
Uwielbiam takie formuły all in one, ale nie wszystko da się w ten sposób zrobić. W Open Office nie da się. A w Excelu?
Poprawiłem treść wpisu dodając słowo “niepustych”.
Tu masz przykład pełnej formuły, dokładnie jak we wpisie, tylko dla całej kolumny. Pierwszy argument INDEX jest adresem tablicy (cała kolumna D). Możesz tam dodawać nowe elementy i będą one uwzględniane w losowaniu (komórka w kolumnie E). O to chodziło?
Moja culpa.
W Open Office nie da się tego zrobić w ten sposób, ale Quatro Pro już spokojnie można. Młotek młotkowi nierówny…