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

2 komentarze

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

    1. Fuuuj.

      Nie neguj臋 Pythona jako takiego. Porz膮dny j臋zyk, nawet swego czasu pope艂ni艂em w nim par臋 drobiazgów. Ale to ma by膰 szybkie rozwi膮zanie "dla pani Krysi", bez instalowania 偶adnych dodatkowych armat. Przestan臋 marudzi膰 jak Python b臋dzie instalowany domy艣lnie jako cz臋艣膰 systemu operacyjnego.

      Natomiast z pewno艣ci膮 da si臋 to samo zrobi膰 w Powershell, który (od wersji bodaj偶e Windows 7 / Windows Server 2008 – nie jestem pewien) jest instalowany wraz z systemem, a w starszych wersjach jest dost臋pny ze strony producenta (by膰 mo偶e nawet jako poprawka z Windows Update)

Leave a Comment

Komentarze mile widziane.

Je偶eli chcesz do komentarza wstawi膰 kod, u偶yj sk艂adni:
[code]
tutaj wstaw sw贸j kod
[/code]

Je偶eli zrobisz liter贸wk臋 lub zmienisz zdanie, mo偶esz edytowa膰 komentarz po jego zatwierdzeniu.