Obecný vzorec
=GET.WORKBOOK(1)&T(NOW())
souhrn
Chcete-li zobrazit seznam listů v sešitu aplikace Excel, můžete použít dvoustupňový přístup: (1) definujte pojmenovaný rozsah s názvem "sheetnames" pomocí starého příkazu makra a (2) použijte funkci INDEX k načtení názvů listů pomocí pojmenovaného rozsahu. V zobrazeném příkladu je vzorec v B5:
=INDEX(MID(sheetnames,FIND(")",sheetnames)+1,255),ROWS($B$5:B5))
Poznámka: Na tento vzorec jsem narazil na nástěnce MrExcel v příspěvku T. Valka.
Vysvětlení
Pojmenovaný rozsah „sheetnames“ je vytvořen pomocí tohoto kódu:
=GET.WORKBOOK(1)&T(NOW())
GET.WORKBOOK je příkaz makra, který načte řadu názvů listů v aktuálním sešitu. Výsledné pole vypadá takto:
("(workbook.xlsm)Sheet1","(workbook.xlsm)Sheet2","(workbook.xlsm)Sheet3","(workbook.xlsm)Sheet4","(workbook.xlsm)Sheet5")
Tajemný výraz je zřetězen do výsledku:
&T(NOW())
Účelem tohoto kódu je přinutit přepočet k vyzvednutí změn v názvech listů. Protože NOW je těkavá funkce, přepočítává se při každé změně listu. Funkce NOW vrací číselnou hodnotu představující datum a čas. Funkce T vrací prázdný řetězec ("") pro číselné hodnoty, takže zřetězení nemá žádný vliv na hodnoty.
Zpět na listu obsahuje buňka B6 tento vzorec zkopírovaný dolů:
=INDEX(MID(sheetnames,FIND(")",sheetnames)+1,255),ROWS($B$5:B5))
Při práci zevnitř ven se funkce MID používá k odstranění názvů listů. Výsledné pole vypadá takto:
("Sheet1","Sheet2","Sheet3","Sheet4","Sheet5")
Toto přejde do funkce INDEX jako „pole“. Funkce ROW používá rozšiřující se rozsahy ke generování zvyšujícího se čísla řádku. Na každém novém řádku vrátí INDEX další hodnotu pole. Pokud již nejsou k dispozici žádné další názvy listů, vrátí vzorec chybu #REF.
Poznámka: protože tento vzorec závisí na příkazu makra, budete muset uložit jako sešit s povolenými makry, pokud chcete, aby vzorec pokračoval v aktualizaci názvů listů po uzavření a opětovném otevření souboru. Pokud uložíte jako normální list, kód názvu listu bude odstraněn.
