Kombinacje

https://xpil.eu/ElXmX 📋

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?

https://xpil.eu/ElXmX 📋