Kombinacje

In Branżowe by xpil2 Comments

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?

Dodaj komentarz

2 komentarzy do "Kombinacje"

Powiadom o
avatar
Sortuj wg:   najnowszy | najstarszy | oceniany
B
Gość

Napisać skrypt w Pythonie i zaimportować dzieło w postaci np. CSV, które powstanie po 20 sekundach do Excela ;P

wpDiscuz