В Microsoft Excel е често срещана задача да се позовавате на клетки в други работни листове или дори в различни файлове на Excel. В началото това може да изглежда малко обезсърчително и объркващо, но след като разберете как работи, не е толкова трудно.
В тази статия ще разгледаме как да препращаме към друг лист в същия файл на Excel и как да препращаме към различен файл на Excel. Ще покрием също неща като как да препращаме диапазон от клетки във функция, как да направим нещата по-прости с дефинирани имена и как да използваме VLOOKUP за динамични препратки.
Съдържание
Как да препратите друг лист в същия файл на Excel
Основната препратка към клетката се записва като буквата на колоната, последвана от номера на реда.
Така че препратката на клетка B3 се отнася до клетката в пресечната точка на колона B и ред 3.
Когато се позовава на клетки на други листове, тази препратка към клетка се предхожда от името на другия лист. Например, по-долу е препратка към клетка B3 на име на лист „Януари“.
=January!B3
Удивителният знак (!) разделя името на листа от адреса на клетката.
Ако името на листа съдържа интервали, тогава трябва да затворите името с единични кавички в препратката.
='January Sales'!B3
За да създадете тези препратки, можете да ги въведете директно в клетката. Въпреки това е по-лесно и по-надеждно да оставите Excel да напише справката вместо вас.
Въведете знак за равенство (=) в клетка, щракнете върху раздела Лист и след това щракнете върху клетката, която искате да направите кръстосана препратка.
Докато правите това, Excel записва препратката вместо вас в лентата с формули.
Натиснете Enter, за да завършите формулата.
Как да препратите друг файл на Excel
Можете да се обърнете към клетки от друга работна книга, като използвате същия метод. Просто се уверете, че имате отворен другият файл на Excel, преди да започнете да въвеждате формулата.
Въведете знак за равенство (=), превключете към другия файл и след това щракнете върху клетката в този файл, която искате да препратите. Натиснете Enter, когато сте готови.
Попълнената кръстосана препратка съдържа другото име на работна книга, затворено в квадратни скоби, последвано от името на листа и номера на клетката.
=[Chicago.xlsx]January!B3
Ако името на файла или листа съдържа интервали, тогава ще трябва да поставите препратката към файла (включително квадратните скоби) в единични кавички.
='[New York.xlsx]January'!B3
В този пример можете да видите знаци за долар ($) сред адреса на клетката. Това е абсолютна препратка към клетка (Научете повече за абсолютните препратки към клетки).
Когато препращате клетки и диапазони в различни файлове на Excel, препратките се правят абсолютни по подразбиране. Можете да промените това на относителна препратка, ако е необходимо.
Ако погледнете формулата, когато посочената работна книга е затворена, тя ще съдържа целия път до този файл.
Въпреки че създаването на препратки към други работни книги е лесно, те са по-податливи на проблеми. Потребителите, които създават или преименуват папки и преместват файлове, могат да нарушат тези препратки и да причинят грешки.
Съхраняването на данни в една работна книга, ако е възможно, е по-надеждно.
Как да направите кръстосана препратка към диапазон от клетки във функция
Позоваването на една клетка е достатъчно полезно. Но може да искате да напишете функция (като SUM), която препраща към диапазон от клетки в друг работен лист или работна книга.
Стартирайте функцията както обикновено и след това щракнете върху листа и диапазона от клетки – по същия начин, както направихте в предишните примери.
В следващия пример функция SUM сумира стойностите от диапазона B2:B6 на работен лист с име Продажби.
=SUM(Sales!B2:B6)
Как да използвате дефинирани имена за прости кръстосани препратки
В Excel можете да присвоите име на клетка или диапазон от клетки. Това е по-смислено от адрес на клетка или диапазон, когато ги погледнете назад. Ако използвате много препратки в електронната си таблица, наименуването на тези препратки може много по-лесно да видите какво сте направили.
Още по-добре, това име е уникално за всички работни листове в този файл на Excel.
Например, можем да назовем клетка „ChicagoTotal“ и след това кръстосаната препратка ще чете:
=ChicagoTotal
Това е по-смислена алтернатива на стандартна справка като тази:
=Sales!B2
Лесно е да създадете дефинирано име. Започнете, като изберете клетката или диапазона от клетки, които искате да наименувате.
Щракнете в полето Име в горния ляв ъгъл, въведете името, което искате да зададете, и след това натиснете Enter.
Когато създавате дефинирани имена, не можете да използвате интервали. Следователно в този пример думите са обединени в името и разделени с главна буква. Можете също да разделите думите със знаци като тире (-) или долна черта (_).
Excel също има мениджър на имена, който прави наблюдението на тези имена в бъдеще лесно. Щракнете върху Формули > Мениджър на имена. В прозореца Мениджър на имена можете да видите списък с всички дефинирани имена в работната книга, къде се намират и какви стойности съхраняват в момента.
След това можете да използвате бутоните в горната част, за да редактирате и изтриете тези определени имена.
Как да форматирате данните като таблица
Когато работите с обширен списък от свързани данни, използването на функцията Форматиране като таблица на Excel може да опрости начина, по който препращате данни в него.
Вземете следната проста таблица.
Това може да бъде форматирано като таблица.
Щракнете върху клетка в списъка, превключете към раздела „Начало“, щракнете върху бутона „Форматиране като таблица“ и след това изберете стил.
Потвърдете, че диапазонът от клетки е правилен и че вашата таблица има заглавки.
След това можете да зададете смислено име на вашата таблица от раздела „Дизайн“.
След това, ако трябва да сумираме продажбите на Чикаго, можем да се позоваваме на таблицата с нейното име (от всеки лист), последвано от квадратна скоба ([) to see a list of the table’s columns.
Select the column by double-clicking it in the list and enter a closing square bracket. The resulting formula would look something like this:
=SUM(Sales[Chicago])
Можете да видите как таблиците могат да направят референтните данни за функции за агрегиране като SUM и AVERAGE по-лесни от стандартните препратки към листове.
Тази таблица е малка за целите на демонстрацията. Колкото по-голяма е таблицата и колкото повече листове имате в работна книга, толкова повече предимства ще видите.
Как да използвате функцията VLOOKUP за динамични препратки
Всички препратки, използвани в примерите досега, са фиксирани към конкретна клетка или диапазон от клетки. Това е страхотно и често е достатъчно за вашите нужди.
Но какво ще стане, ако клетката, която препращате, има потенциал да се промени, когато се вмъкнат нови редове или някои