Spojování vybraných textů – aneb jak by fungovala funkce CONCATENATEIFS kdyby existovala
S tímto tématem se setkáte na našich kurzech ExcelTown.
Aktuálně: kurzy můžete absolvovat jak online, tak prezenčně.
Tento článek popisuje, jak z oblasti spojit texty, které odpovídají určité podmínce.
Je to trochu obdoba funkcí SUMIFS, COUNTIFS (jen pro texty místo počítání) nebo funkce CONCATENATEX v DAXu. Využívá se funkce TEXTJOIN, která je zatím dostupná pouze v některých verzích Excelu.
Naším úkolem bude ke konkrétnímu vybranému produktu spojit všechny zákaznické komentáře z tabulky vpravo:
Jak na to? Zapíšeme tento maticový vzorec:
- {=TEXTJOIN("; ";1;IF(D:D=A2;E:E;""))}
Vysvětlení:
- Kudrnaté závorky označují, že jde o maticový vzorec. Ten se liší tím, že ho zapíšeme (bez kudrnatých závorek) a místo Entru dáme Ctrl Shift Enter. Tím se vzorec zapíše a vzniknou kudrnaté závorky.
- TEXTJOIN je funkce, která spojuje texty. Může obsahovat i spojovací text - tedy co se objeví mezi spojenými texty. V našem případě je to středník s mezerou.
- IF je funkce, která zajistí, že se do výsledného slepence zahrnou pouze položky, které jsou u právě vybraného textu - produktu.
Poznámka
- Obdobně jako TEXTJOIN by šlo použít také funkci CONCAT (což není to samé co CONCATENATE...)
Jak na to ve VBA?
Celé to jde řešit i pomocí VBA vlastní funkce:
Function CONCATENATEIF(oblast_kritérií As Range, kritérium As String, _ oblast_spojení As Range, oddelovac As String) As String Dim arrOblastKriterii() Dim arrOblastSpojeni() Dim i As Long Dim strText As String 'načtení oblastí do polí arrOblastKriterii = oblast_kritérií arrOblastSpojeni = oblast_spojení 'pro každou položku z pole kriterii For i = LBound(arrOblastKriterii) To UBound(arrOblastKriterii) 'odpovídá položka předepsanému kritériu? If arrOblastKriterii(i, 1) Like kritérium Then 'pridani polozky z oblasti spojeni do vysledneho retezce strText = strText & arrOblastSpojeni(i, 1) & oddelovac End If Next i 'prevzeti rezetce funkci (orezani o posledni oddelovac) CONCATENATEIF = Left(strText, Len(strText) - Len(oddelovac)) End Function
Soubor s funkcí je možné si stáhnout tady
S tímto tématem se setkáte na našich kurzech ExcelTown.
Aktuálně: kurzy můžete absolvovat jak online, tak prezenčně.