Съдържание
Ако поне си запознат с функцията ВПР (VLOOKUP) (ако не, тогава първо стартирайте тук), тогава трябва да разберете, че тази и други функции, подобни на нея (VIEW, INDEX и SEARCH, SELECT и т.н.) винаги дават като резултат стойност – номерът, текстът или датата, които търсим в дадената таблица.
Но какво ще стане, ако вместо стойност искаме да получим жива хипервръзка, като щракнем върху която можем незабавно да преминем към намереното съвпадение в друга таблица, за да го разгледаме в общ контекст?
Да кажем, че имаме голяма таблица с поръчки за нашите клиенти като вход. За удобство (въпреки че това не е необходимо), преобразувах таблицата в динамична „интелигентна“ клавишна комбинация Ctrl+T и даде на табл конструктор (Дизайн) нейното име раздел Поръчки:
На отделен лист Консолидиран Изградих пивот таблица (въпреки че не е задължително да е точно пивот таблица – по принцип е подходяща всяка таблица), където според първоначалните данни се изчислява динамиката на продажбите по месеци за всеки клиент:
Нека добавим колона към таблицата с поръчки с формула, която търси името на клиента за текущата поръчка в листа Консолидиран. За целта използваме класическия набор от функции INDEX (ИНДЕКС) и ПО-ИЗЛОЖЕН (СЪВПАДА):
Сега нека увием нашата формула във функция CELL (КЛЕТКА), който ще поискаме да покаже адреса на намерената клетка:
И накрая, поставяме всичко, което се е получило, във функция ХИПЕРВРЪЗКА (ХИПЕРВРЪЗКА), който в Microsoft Excel може да създаде жива хипервръзка към даден път (адрес). Единственото нещо, което не е очевидно е, че ще трябва да залепите знака за решетка (#) в началото към получения адрес, така че връзката да се възприема правилно от Excel като вътрешна (от лист на лист):
Сега, когато щракнете върху някоя от връзките, ние незабавно ще преминем към клетката с името на компанията на листа с обобщената таблица.
За да стане наистина добре, нека леко подобрим нашата формула, така че преходът да не става към името на клиента, а към конкретна цифрова стойност точно в колоната за месеца, когато е изпълнена съответната поръчка. За да направим това, трябва да помним, че функцията INDEX (ИНДЕКС) в Excel е много гъвкав и може да се използва, наред с други неща, във формата:
=ИНДЕКС( XNUMXD_диапазон; номер_на_ред; Номер_на_колона )
Тоест, като първи аргумент можем да посочим не колоната с имената на компаниите в обобщената таблица, а цялата област с данни на обобщената таблица и като трети аргумент да добавим номера на колоната, от която се нуждаем. Може лесно да се изчисли чрез функцията МЕСЕЦ (МЕСЕЦ), който връща номера на месеца за датата на сделката:
Подобрение 2. Красив символ за връзка
Втори аргумент на функцията ХИПЕРВРЪЗКА – текстът, който се показва в клетка с връзка – може да се направи по-красив, ако използвате нестандартни знаци от Windings, Webdings шрифтове и други подобни вместо баналните знаци „>>“. За целта можете да използвате функцията СИМВОЛ (CHAR), които могат да показват знаци по техния код.
Така, например, символен код 56 в шрифта на Webdings ще ни даде хубава двойна стрелка за хипервръзка:
Подобрение 3. Маркирайте текущия ред и активната клетка
Е, за окончателната победа на красотата над здравия разум, можете също да прикачите към нашия файл опростена версия на подчертаване на текущия ред и клетката, към която следваме връзката. Това ще изисква прост макрос, който ще окачим, за да обработи събитието за промяна на селекцията на листа Консолидиран.
За да направите това, щракнете с десния бутон върху раздела Резюме на листа и изберете командата Гледка код (Виж код). Поставете следния код в прозореца на редактора на Visual Basic, който се отваря:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Cells.Interior.ColorIndex = -4142 Cells(ActiveCell.Row, 1).Resize(1, 14).Interior.ColorIndex = 6 ActiveCell.Interior.ColorIndex = 44 End Sub
Както можете лесно да видите, тук първо премахваме запълването от целия лист и след това запълваме целия ред в резюмето с жълто (цветен код 6), а след това оранжево (код 44) с текущата клетка.
Сега, когато някоя клетка в обобщената клетка бъде избрана (няма значение - ръчно или в резултат на щракване върху нашата хипервръзка), целият ред и клетка с месеца, от който се нуждаем, ще бъдат маркирани:
Красота 🙂
PS Само не забравяйте да запишете файла във формат с активирани макроси (xlsm или xlsb).
- Създаване на външни и вътрешни връзки с функцията HYPERLINK
- Създаване на имейли с функцията HYPERLINK