Списък с имената на работните листове в Ексел – 5 начина

Който е работил достатъчно дълго с Ексел неминуемо е попадал в ситуация, в която е било необходимо да направи списък с имената на работните листове. Например, за да направи индексен шийт.

В тази статия ще видите пет начина да се извлече списък с имената на шийтовете, всеки със своите предимства и недостатъци.

Метод 1: Ръчно

Вярвам, че всеки потребител на Ексел знае този начин, но за пълнота започвам от него. За да вземем името на някой шийт, трябва кликнем с десния бутон върху името и от появилото се меню да изберем Rename.

списък с имената на работните листове - първи начин

Това маркира името (същото може да се постигне и просто с двойно кликане върху името с левия бутон)

И сега остава да го копираме с CTRL+C и да го пейстнем, където ни е удобно.

Повтаряме тази процедура с всички останали шийтове и си имаме списък с техните имена. Това не звучи много „майсторски“ или „куул“. Обаче от собствен опит ви казвам – когато имате 5-6 шийта и това е еднократно упражнение, което няма да се повтаря в бъдеще, това е най-бързият и ефективен начин да си набавите списък с имената на работните листове. В този конкретен случай просто не се занимавайте с другите начини, които ще видите по-долу.

Метод 2: Макрос

Макросите се правят точно с цел автоматизиране на рутинно повтарящи се действия. Ако вашият файл има десетки работни листове и/или се налага да извличате списъка постоянно, тогава този метод е сред препоръчителните.

Ако не сте запознати с писането на макроси просто следвайте следните стъпки:

  1. Във файла, в който искате да направите списъка с имената на шийтовете, направете един шийт с име Index.
  1. Копирайте този код:

Sub Sheet_Names_List()

    Sheets(„Index“).Select

    Sheets(„Index“).Move Before:=Sheets(1)

    Columns(1).Insert

    Columns(1).Select

    Selection.ClearContents

    Cells(1, 1) = „Sheet Name“

    Cells(1, 1).Select

    Selection.Font.Bold = True

            For i = 2 To Sheets.Count

        Cells(i, 1) = Sheets(i).Name

    Next i

End Sub

Накратко казано този код за всеки случай премества шийта Index най-отляво, вмъква в него една колона най-отляво и след това започва да обхожда другите работни листове и да изпълнява вместо вас Метод1.

  1. С ALT + F11 отворете Visual Basic Editor-а

списък с имената на работните листове - втори начин

  1. Пейстнете кода вътре запазете промените (CTRL + S) и затворете VBA Editor-а

VBA код за съставяне на списък с имената на работните листове

  1. Когато искате да стартирате макроса, трябва да отидете на Developer > Macros

След това да изберете макроса с име Sheet_Names_List  и да натиснете Run

Ако това ви се струва твърде неудобно, може да натиснете Options и да зададете клавишна комбинация, с която да се задейства макроса.

Добре е тази комбинация да не е някоя от стандартните за Ексел като CTR+S (Save) или CTR+Р (принтиране) например. Аз в случая съм избрал CTRL+ SHIFT + M (натискате ги трите едновременно).

Предимствата на този метод са очевидни – бързина и ефективност.

Недостатъците са, че макросът прави винаги и точно това, за което е програмиран и ако нещо се промени и не отговаря на написаното в кода – той просто няма да работи.

Например, винаги ще вмъкне нова колона и ще направи нов списък в нея. Ако потребителят желае старият списък да се изтрива и на негово място да се появява новият – кодът трябва да се промени. Ако пък файлът няма шийт с име Index, получава се съобщение за грешка

Метод 3: Полумакрос

При този метод отново се възползвате от бързината и ефективността на VBA, но без да записвате макроси. Той е подходящ за еднократно или не твърде често съставяне на списък с имената на работните листове.

Отваряме с ALT + F11 VBA Editor-a и след това натискаме CTRL + G, за да отворим Immediate прозореца

списък с имената на работните листове - трети начин

В него трябва да се въведе този прост код: for each s in sheets:debug.print s.name:next

След това натискаме Enter и списъкът с имената на шийтовете се появява моментално. Остава само да го копираме и пейстнем, където желаем.

Метод 4: На VBA дядото

Преди версията 5.0 на Ексел макросите са писани на езика XLM (да не се бърка с XML, което е формат за съхранение на данни). Въпреки, че това е направо древна технология, много от неговите над 500 функции все още могат да се използват.

Например с помощта на функцията GET.WORKBOOK можем да получим различна информация за определен файл.

Синтаксисът й е прост: =GET.WORKBOOK(параметър; текст)

Параметърът показва каква точно информация искаме да ни покаже, а текстът задава кой е файлът. Ако не се въведе – връща стойност за текущия файл.

Интересни в нашия случай са параметър 1 и 16

1 създава виртуална колона с имената на работните листове като пред тях поставя името на файла в квадратни скоби

16 връща името на файла

Но не можем да ги използваме като стандартни функции в Ексел – получава се грешка.

Трябва да създадем наименована област със съответната функция. За целта с комбинацията CTRL+ F3 извикваме менюто Name Manager и избираме New

Там в Name въвеждаме името на областта (SheetNames), а в Refers To – формулата =GET.WORKBOOK(1)

списък с имената на работните листове - четвърти начин

По подобен начин създаваме и област FileName с формула =GET.WORKBOOK(16)

Следващата стъпка е да въведем няколко поредни номера в една колона, а в съседната да напишем следната формула:

списък с имената на работните листове - четвърти начин

Когато я копираме до долу получаваме нещо, което прилича на списък с имената на работните листове. Обърнете внимание, че за номера 8, 9 и 10 формулата връща грешка, защото нямаме толкова работни листове.

Остава само да премахнем името на файла. Това става с помощта на функцията SUBSTITUTE и втората наименована област FileName. Текстът на формулата е: =SUBSTITUTE(B1;“[„&FileName&“]“;““)

списък с имената на работните листове - четвърти начин

Последното нещо, което трябва да се има предвид е, че тъй като използваме език за макроси, трябва да запазим файла във формат, който поддържа макроси – xls, xlsm или xlsb.

Съзнавам, че този метод звучи малко объркващо на пръв поглед, но той дава възможност да се извежда списък с имена на шийтовете само с формули, без да се пише код във VBA.

За съжаление методът не е напълно автоматичен. Обърнете внимание на долната картинка как са изтрити Sheet1 и Sheet2, а monarchs е преименуван на kings. Но това не се е отразило в списъка с имената.

Необходимо е да се стартира, така да се каже, макроса-дядо с клавишната комбинация CTRL + ALT + F9.

Ако сте любопитни и искате да научите повече за XLM функциите, на този линк може да изтеглите безплатно в pdf формат пълно ръководство от 653 страници.

Внимание! Не разчитайте много на XLM – не се знае до кога Микрософт ще го поддържат, въпреки че за момента той работи във всички версии на Ексел.

Метод 5: Power Query

Не знам дали това е най-гъвкавият начин, но със сигурност е най-модерният 😉

Стъпките са следните:

  1. Отиваме на Data > Get Data > From File > From Workbook (за версия 2016 и нагоре. За по-ранните версии е малко по-различно, но пак трябва да търсите From File)

списък с имената на работните листове - пети начин

  1. Задаваме пътя до текущия файл

  1. В това меню избираме името на файла с жълтата папка отпред и натискаме Transform Data

списък с имената на работните листове - пети начин

  1. Когато се зареди Power Query, появява се таблица с две колони. Маркираме колоната Data, а след това – десен бутон и я премахваме с Remove

  1. Ето го нашият списък, остава само да го заредим с Close & Load To

списък с имената на работните листове - пети начин

В случая аз съм избрал да се зареди в съществуващия шийт Index

Крайният резултат е списък с имената на всички работни листове, подредени по азбучен ред.

Когато има промени в броя и/или имената на шийтовете всичко, което трябва да направим е да кликнем с десния бутон някъде в списъка и да изберем Refresh

Като недостатък на метода отчитам, че имената винаги са подредени по азбучен ред, а не по реда, в който са разположени във файла.

* * *

Това бяха петте начина, които знам за извеждане на списък с имената на работните листове в Ексел. Благодаря, че отделихте от ценното си време да прочетете статията до край. Ако ви е харесала – прегледайте блога за други полезни материали.

Файлът с всички разгледани примери може да изтеглите от от тук

Публикувано в Хитрини. Постоянна връзка.