Който е работил достатъчно дълго с Ексел неминуемо е попадал в ситуация, в която е било необходимо да направи списък с имената на работните листове. Например, за да направи индексен шийт.
В тази статия ще видите пет начина да се извлече списък с имената на шийтовете, всеки със своите предимства и недостатъци.
Метод 1: Ръчно
Вярвам, че всеки потребител на Ексел знае този начин, но за пълнота започвам от него. За да вземем името на някой шийт, трябва кликнем с десния бутон върху името и от появилото се меню да изберем Rename.
Това маркира името (същото може да се постигне и просто с двойно кликане върху името с левия бутон)
И сега остава да го копираме с CTRL+C и да го пейстнем, където ни е удобно.
Повтаряме тази процедура с всички останали шийтове и си имаме списък с техните имена. Това не звучи много „майсторски“ или „куул“. Обаче от собствен опит ви казвам – когато имате 5-6 шийта и това е еднократно упражнение, което няма да се повтаря в бъдеще, това е най-бързият и ефективен начин да си набавите списък с имената на работните листове. В този конкретен случай просто не се занимавайте с другите начини, които ще видите по-долу.
Метод 2: Макрос
Макросите се правят точно с цел автоматизиране на рутинно повтарящи се действия. Ако вашият файл има десетки работни листове и/или се налага да извличате списъка постоянно, тогава този метод е сред препоръчителните.
Ако не сте запознати с писането на макроси просто следвайте следните стъпки:
- Във файла, в който искате да направите списъка с имената на шийтовете, направете един шийт с име Index.
- Копирайте този код:
Sub Sheet_Names_List()
Sheets(„Index“).Select
Sheets(„Index“).Move Before:=Sheets(1)
Columns(1).Insert
Columns(1).Select
Selection.ClearContents
Cells(1, 1) = „SheetName“
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.
- С ALT + F11 отворете Visual Basic Editor-а
- Пейстнете кода вътре запазете промените (CTRL + S) и затворете VBA Editor-а
- Когато искате да стартирате макроса, трябва да отидете на 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
Не знам дали това е най-гъвкавият начин, но със сигурност е най-модерният 😉
Стъпките са следните:
- Отиваме на Data > Get Data > From File > From Workbook (за версия 2016 и нагоре. За по-ранните версии е малко по-различно, но пак трябва да търсите From File)
- Задаваме пътя до текущия файл
- В това меню избираме името на файла с жълтата папка отпред и натискаме Transform Data
- Когато се зареди Power Query, появява се таблица с две колони. Маркираме колоната Data, а след това – десен бутон и я премахваме с Remove
- Ето го нашият списък, остава само да го заредим с Close & Load To
В случая аз съм избрал да се зареди в съществуващия шийт Index
Крайният резултат е списък с имената на всички работни листове, подредени по азбучен ред.
Когато има промени в броя и/или имената на шийтовете всичко, което трябва да направим е да кликнем с десния бутон някъде в списъка и да изберем Refresh
Като недостатък на метода отчитам, че имената винаги са подредени по азбучен ред, а не по реда, в който са разположени във файла.
* * *
Това бяха петте начина, които знам за извеждане на списък с имената на работните листове в Ексел. Благодаря, че отделихте от ценното си време да прочетете статията до край. Ако ви е харесала – прегледайте блога за други полезни материали.
Файлът с всички разгледани примери може да изтеглите от от тук