Kombinacje

Miałem niedawno do rozwiązania całkiem banalny problem, który rozwiązałem w dość nietypowy sposób.

Problem polega na wygenerowaniu – za pomocą Excela – wszystkich kombinacji dwóch liter alfabetu łacińskiego, począwszy od “AA” a skończywszy na “ZZ”. Czyli:

AA
AB
AC

AY
AZ
BA
BB
BC
BD

YV
YX
YY
YZ
ZA
ZB

ZX
ZY
ZZ

O ile zagadnienie to jest trywialne w SQL-u – ot, jeden SELECT na iloczynie kartezjańskim – o tyle w Excelu nie da się bezpośrednio, w prosty sposób, budować kombinacji, permutacji etc etc. W bardziej złożonych sytuacjach zazwyczaj piszę sobie prościutką pętelkę w VBA, o taką:

Public Sub Kombinacje(start_from As Integer, end_at As Integer, location As Excel.Range)
  Dim r As Excel.Range
  Dim i As Integer, j As Integer
  Set r = location
  For i = start_from To end_at
    For j = start_from To end_at
      r.Value = Chr(i) & Chr(j)
      Set r = r.Offset(1, 0)
    Next j
  Next i
 End Sub

Potem już tylko Ctrl-G, wywołujemy procedurę z odpowiednimi parametrami, o tak:

Kombinacje 65,90, Range("A1")

Pukamy Enter – i gotowe.

Jednak – jakkolwiek lubię pisać w VBA – to jest trochę za duża armata na tak błahy problem. Da się prościej.

A jak?

Ano, ja skorzystałem z faktu, że począwszy od Office 2007 wzwyż, liczba kolumn w arkuszu zwiększyła się z 256 do 16384, a więc trzeba było również wprowadzić trzyliterowe oznaczenia kolumn (bo kombinacji dwuliterowych jest za mało).

Tym samym, skoro wszystkie dwuliterowe kombinacje liter już w Excelu istnieją (jako nagłówki kolumn), trzeba je po prostu wyciągnąć do komórek arkusza, o tak:

W komórce AA1 wpisujemy formułę:

=SUBSTITUTE(SUBSTITUTE(ADDRESS(ROW(AA1), COLUMN(AA1)), "$1", ""), "$", "")

W wyniku dostajemy tekst “AA”. Formułę kopiujemy w prawo aż do kolumny ZZ. Najlepiej w tym celu: skopiować komórkę AA1 (Ctrl-C), puknąć Control-G, wpisać adres komórki ZZ1, puknąć enter, wcisnąć Shift-Control-Strzałka w lewo, wkleić (Ctrl-V), gotowe.

Otrzymaną w ten sposób listę można potem “spionowić” kopiując wszystkie komórki od AA1 do ZZ1 (tak samo: zaznaczamy komórkę AA1, pukamy Shift-Ctrl-Strzałka w prawo, następnie Ctrl-C), i wklejając je gdzieś poniżej, z zaznaczoną opcją Transpozycji (czyli: Prawomysz -> Wklej specjalnie -> zaznaczamy “Transpozycja” oraz “Wartości”, Enter).

I gotowe.

Na upartego, możnaby to jeszcze uprościć. Zamiast kosztownego SUBSTITUTE można użyć prostego MID (po cholerę wyszukiwać i zamieniać, skoro poszukiwany kawałek adresu jest zawsze w tym samym miejscu?), oraz dodatkowo użyć numeru wiersza jako drugiego parametru funkcji ADDRESS – wówczas niezbędne adresy wyliczą się bez transpozycji. O tak:

W komórkę A1 wpisujemy formułę:

=MID(ADDRESS(1,ROW(A27)), 2, 2)

a następnie kopiujemy ją w dół aż do komórki A676.

I gotowe – bez potrzeby transponowania.

Czy ktoś ma jeszcze jakieś pomysły?


Liczba słów w tym wpisie: 495

Sprawdź też

Pchełki Powershell: odzyskujemy klucz aktywacyjny do Windows 10

Niedawno zachciało mi się na jednym z domowych laptopów przetestować Docker for Windows. Niektóre opcje …

Linuks, find, jak pozbyć się komunikatu “Permission denied”

Linuksowa komenda find jest dość potężna: potrafi nie tylko przeszukiwać foldery, ale również wykonywać dowolne …

Zapisz się
Powiadom o
guest
2 komentarzy
Inline Feedbacks
Zobacz wszystkie komentarze
2
0
Zapraszam do skomentowania wpisu.x
()
x