Да можеш да позоваваш клетки и диапазони е това, което кара всеки инструмент за електронни таблици да работи. И Excel е най -добрият и най -мощният там.
В този урок ще разгледам всичко, което трябва да знаете за това как да препращате към клетки и диапазони в Excel. Освен основните препратки към същия лист, основната част от този урок ще бъде около как да препратите към друг лист или работна книга в Excel.
Въпреки че няма голяма разлика в начина на работа, когато се позовавате на друг лист в същия файл или посочвате напълно отделен файл на Excel, форматът на тази справка се променя малко.
Също така има някои важни неща, които трябва да имате предвид, когато препращате към друг лист или към други външни файлове.
Но тревожи се … нищо твърде лудо!
Докато приключите с този урок, ще разберете всичко, което трябва да знаете за препратките към клетки и диапазони в Excel (бъдете в същата работна книга или друга работна книга).
Да започваме!
Позоваване на клетка в същия лист
Това е най -основното ниво на препратки, когато препращате към клетка на същия лист.
Например, ако съм в клетка В1 и искам да се позова на клетка А1, форматът ще бъде:
= А1
Когато направите това, стойността в клетката, където използвате тази справка, ще бъде същата като тази в клетка A1. И в случай, че направите някакви промени в клетка A1, те ще бъдат отразени в клетката, където сте използвали тази справка.
Позоваване на клетка в друг лист
Ако трябва да се позовавате на друг лист в същата работна книга, трябва да използвате следния формат:
Sheet_name! Cell_address
Първо, имате името на листа, последвано от удивителен знак, който е последван от препратка към клетката.
Така че, ако трябва да се обърнете към клетка A1 в лист 1, трябва да използвате следната справка:
= Лист1! A1
И ако искате да се обърнете към диапазон от клетки в друг лист, трябва да използвате следния формат:
Име на лист! Първа клетка: Последна клетка
Така че, ако искате да се обърнете към диапазона A1: C10 в друг лист в същата работна книга, трябва да използвате посочената по -долу справка:
= Лист1! A1: C10
Имайте предвид, че съм ви показал само препратката към клетката или диапазона. В действителност бихте ги използвали във формули. Но форматът на споменатите по -горе референции ще остане същият
В много случаи работният лист, към който се отнасяте, ще има няколко думи в името. Например, може да бъде Данни от проекта или Данни за продажбите.
В случай, че имате интервали или символи без азбука (като @,!, #, -и т.н.), трябва да използвате името в единични кавички.
Например, ако искате да препратите клетка A1 в листа, наречен Данни за продажбите, ще използвате следната справка:
= 'Данни за продажбите'! A1
И в случай, че името на листа е Sales-Data, тогава, за да се позовете на клетка A1 в този лист, трябва да използвате посочената по-долу справка:
= 'Данни за продажбите'! A1
Когато се позовавате на лист в същата работна книга и след това по -късно промените името на работния лист, не е нужно да се притеснявате, че препратката ще се счупи. Excel автоматично ще актуализира тези препратки вместо вас.
Въпреки че е чудесно да знаете формата на тези препратки, на практика не е толкова добра идея да ги въвеждате ръчно всеки път. Това би отнело много време и е много податливо на грешки.
Нека ви покажа по -добър начин за създаване на препратки към клетки в Excel.
Автоматично създаване на препратка към друг лист в същата работна книга
Много по -добър начин за създаване на препратка към клетка към друг лист е просто да насочите Excel към клетката/диапазона, към който искате да създадете препратката, и да оставите Excel да я създаде сама.
Това ще гарантира, че няма да се притеснявате, че удивителният знак или кавичките липсват или друг проблем с формата се появява. Excel автоматично ще създаде правилната справка за вас.
По -долу са стъпките за автоматично създаване на препратка към друг лист:
- Изберете клетката в текущата работна книга, където имате нужда от препратка
- Въведете формулата, докато имате нужда от препратката (или знак за равенство, ако просто искате препратката)
- Изберете листа, към който трябва да се обърнете
- Изберете клетката/диапазона, към който искате да се обърнете
- Натиснете Enter, за да получите резултата от формулата (или продължете да работите по формулата)
Горните стъпки автоматично биха създали препратка към клетката/диапазона в друг лист. Също така ще можете да видите тези препратки в лентата с формули. След като приключите, можете просто да натиснете клавиша Enter и той ще ви даде резултата.
Например, ако имате някои данни в клетка A1: A10 в лист, наречен Данни за продажби, и искате да получите сумата от тези стойности в текущия лист, следващите стъпки ще бъдат следните:
- Въведете следната формула в текущия лист (където имате нужда от резултата): = Сума (
- Изберете листа „Данни за продажбите“.
- Изберете диапазона, който искате да добавите (A1: A10). Веднага щом направите това, Excel автоматично ще създаде препратка към този диапазон (можете да видите това в лентата с формули)
- Натиснете клавиша Enter.
Когато създавате дълга формула, може да се наложи да се обърнете към клетка или диапазон в друг лист, а след това да имате нужда да се върнете към началния лист и да препратите към някаква клетка/диапазон там.
Когато направите това, ще забележите, че Excel автоматично вмъква препратка към лист към листа, където имате формулата. Въпреки че това е наред и не вреди, не е необходимо. В такъв случай можете да изберете да запазите препратката или да я премахнете ръчно.
Друго нещо, което трябва да знаете, когато създавате препратки, като изберете листа и след това клетката/диапазона е, че Excel винаги ще създаде относителна справка (т.е. препратки със знак n0 $). Това означава, че ако копирам и поставя формулата (една с препратка към друг лист) в друга клетка, тя автоматично ще коригира препратката.
Ето един пример, който обяснява относителните препратки.
Да предположим, че използвам следната формула в клетка A1 в текущия лист (за да се позова на клетка A1 в име на лист SalesData)
= SalesData! A1
Сега, ако копирам тази формула и я поставя в клетка A2, формулата ще се промени на:
= SalesData! A1
Това се случва, защото формулата е относителна и когато я копирам и поставя, препратките автоматично ще се коригират.
В случай, че искам тази препратка винаги да се отнася до клетка A1 в листа SalesData, ще трябва да използвам формулата по -долу:
= SalesData! $ A $ 1
Знакът за долар преди реда и номера на колоната заключва тези препратки, така че да не се променят.
Ето подробен урок, където можете да научите повече за абсолютни, смесени и относителни препратки.
Сега, когато разгледахме как да препратим към друг лист в същата работна книга, нека видим как можем да се позовем на друга работна книга.
Как да препратите към друга работна книга в Excel
Когато се отнасяте към клетка или диапазон към друга работна книга на Excel, форматът на тази справка ще зависи от това дали тази работна книга е отворена или затворена.
И разбира се, името на работната книга и работните листове също играят роля при определяне на формата (в зависимост от това дали имате имена на интервали или без азбука в името или не).
Така че нека да видим различните формати на външни препратки към друга работна книга в различни сценарии.
Външна справка за отворена работна книга
Когато става въпрос за позоваване на външна отворена работна книга, трябва да посочите името на работната книга, името на работния лист и адреса на клетката/диапазона.
По -долу е форматът, който трябва да използвате, когато се позовавате на външна отворена работна книга
= '[FileName] SheetName! CellAddress
Да предположим, че имате работна книга ‘ExampleFile.xlsx’ и искате да се обърнете към клетка A1 в Sheet1 на тази работна книга.
По -долу има справка за това:
= [ExampleFile.xlsx] SalesData! A1
В случай, че в името на външната работна книга или името на листа (или и двете) има интервали, трябва да добавите поставете името на файла (в квадратни скоби) и името на листа в единични кавички.
По -долу са примерите, в които трябва да имате имената в единични кавички:
= '[Пример за файл.xlsx] SalesData'! A1 = '[ExampleFile.xlsx] Данни за продажбите'! A1 = '[Пример за файл.xlsx] Данни за продажби'! A1
Как да създадете препратка към друга работна книга (автоматично)
Отново, въпреки че е добре да знаете формата, най -добре е да не го въвеждате ръчно.
Вместо това просто насочете Excel в правилната посока и той ще създаде тези препратки за вас. Това е много по -бързо с много по -малък шанс за грешки.
Например, ако имате някои данни в клетка A1: A10 в работна книга с име „Примерен файл“ в листа с име „Данни за продажбите“ и искате да получите сумата от тези стойности в текущия лист, следващите стъпки ще бъдат следните :
- Въведете следната формула в текущия лист (където имате нужда от резултата): = Сума (
- Отидете в работната книга „Примерен файл“
- Изберете листа „Данни за продажбите“.
- Изберете диапазона, който искате да добавите (A1: A10). Веднага щом направите това, Excel автоматично ще създаде препратка към този диапазон (можете да видите това в лентата с формули)
- Натиснете клавиша Enter.
Това незабавно ще създаде формулата с правилните препратки.
Едно нещо, което бихте забелязали, когато създавате препратка към външна работна книга, е, че тя винаги ще създава абсолютни препратки. Това означава, че има знак $ преди номерата на редовете и колоните. Това означава, че ако копирате и поставите тази формула в други клетки, тя ще продължи да се позовава на същия диапазон поради абсолютна препратка.
В случай, че искате това да се промени, трябва да промените препратките ръчно.
Външна препратка към затворена работна книга
Когато е отворена външна работна книга и се позовавате на тази работна книга, просто трябва да посочите името на файла, името на листа и адреса на клетката/диапазона.
Но когато това е затворено, Excel няма представа къде търсите клетките/диапазона, за които сте споменали.
Ето защо, когато създавате препратка към затворена работна книга, трябва също да посочите пътя на файла.
По -долу е препратка, която се отнася до клетка A1 в работния лист Sheet1 в работната книга на примерния файл. Тъй като този файл не е отворен, той се отнася и до мястото, където файлът е записан.
= 'C: \ Users \ sumit \ Desktop \ [Example File.xlsx] Sheet1'! $ A $ 1
Горната справка има следните части:
- Път на файла - местоположението във вашата система или мрежа, където се намира външният файл
- Име на файл - името на външната работна книга. Това ще включва и разширението на файла.
- Име на лист - името на листа, в който се позовавате на клетките/диапазоните
- Адрес на клетка/диапазон - точният адрес на клетка/обхват, към който се отнасяте
Когато създавате външна препратка към отворена работна книга и след това затваряте работната книга, ще забележите, че препратката автоматично се променя. След затваряне на външната работна книга Excel автоматично вмъква препратка и към пътя на файла.
Въздействие на промяната на местоположението на файла върху препратките
Когато създавате препратка към клетка/диапазон във външен Excel файл и след това я затваряте, препратката сега използва и пътя на файла.
Но ако промените местоположението на файла, нищо няма да се промени във вашата работна книга (в която създавате препратката). Но тъй като сте променили местоположението, връзката вече е прекъсната.
Така че, ако затворите и отворите тази работна книга, тя ще ви каже, че връзката е прекъсната и трябва или да актуализирате връзката, или да я прекъснете напълно. Той ще ви покаже подкана, както е показано по -долу:
Когато кликнете върху Актуализиране, той ще ви покаже друго подкана, където можете да изберете опциите за редактиране на връзките (което ще ви покаже диалоговия прозорец по -долу)
Ако трябва да поддържате тези файлове свързани, можете да посочите новото местоположение на файла, като щракнете върху Update Values. Excel отваря диалогов прозорец за вас, където можете да посочите новото местоположение на файла, като навигирате там и го изберете.
Позоваване на определено име (в същата или външна работна книга)
Когато трябва да се позовавате на клетки и диапазони, по -добрият начин е да създадете определени имена за диапазоните.
Това е полезно, тъй като ви улеснява да се позовавате на тези диапазони, като използвате име вместо дълъг и сложен референтен адрес.
Например, по -лесно е да се използва = SalesData вместо = [Example File.xlsx] Sheet1 ′! $ A $ 1: $ A $ 10
И в случай, че сте използвали това дефинирано име в множество формули и трябва да промените препратката, трябва да го направите само веднъж.
Ето стъпките за създаване на именуван диапазон за диапазон от клетки:
- Изберете всички клетки, които искате да включите в именувания диапазон
- Щракнете върху раздела Формули
- Кликнете върху опцията Определяне на име (тя е в групата Дефинирани имена)
- В диалоговия прозорец Ново име дайте име на този диапазон (използвам името SalesData в този пример). Не забравяйте, че не можете да имате интервали в името
- Запазете обхвата като работна книга (освен ако нямате сериозна причина да го направите на ниво лист)
- Уверете се, че препратките към диапазона са правилни.
- Щракнете върху OK.
Сега вашият именован диапазон е създаден и можете да го използвате вместо препратките към клетки с адреси на клетки.
Например, ако искам да получа сумата от всички тези клетки в диапазона SalesData, можете да използвате формулата по -долу:
= SUM (SalesData)
И какво, ако искате да използвате този именен диапазон са други работни листове или дори други работни книги?
Можеш!
Просто трябва да следвате същия формат, който обсъдихме в горния раздел.
Няма нужда да се връщате в началото на тази статия. Нека ви дам всички примери тук, за да добиете представа.
Работна книга Vs Работен лист Назовани диапазони: Когато създавате име на ниво работна книга, може да има само един диапазон с това име. Така че, ако се позовавате на него от външни работни книги или работни листове в същата работна книга, не е нужно да посочвате името на работния лист (тъй като е достъпно за използване в цялата работна книга). В случай, че използвате нивото на работния лист с име диапазон, ще можете да го използвате само в работния лист и докато се позовавате на него, ще трябва да посочите и името на работния лист.Позоваване на определено име в същия работен лист или работна книга
Ако сте създали определеното име за нивото на работната книга, можете да го използвате навсякъде в работната книга, като използвате само самото дефинирано име.
Например, ако искам да получа сумата от всички клетки в именувания диапазон, който създадохме (SaledData), мога да използвам формулата по -долу:
= SUM (SaledData)
В случай, че сте създали ниво на работен лист с име диапазон, можете да използвате тази формула само ако именуваният диапазон е създаден в същия лист, където използвате формулата.
В случай, че искате да го използвате на друг лист (да речем Sheet2), трябва да използвате следната формула:
= SUM (Sheet1! $ A $ 1: $ A $ 10)
И в случай, че в името на листа има интервали или буквено -цифрови знаци, ще трябва да поставите името на листа в единични кавички.
= SUM ('Лист 1'! $ A $ 1: $ A $ 10)
Позоваване на определено име в друга работна книга (отворена или затворена)
Когато искате да посочите именован диапазон в друга работна книга, ще трябва да посочите името на работната книга и след това името на диапазона.
Например, ако имате работна книга на Excel с името ExampleFile.xlsx и кръстен диапазон с името SalesData, тогава можете да използвате формулата по -долу, за да получите сумата от този диапазон от друга работна книга:
= SUM (ExampleFile.xlsx! SalesData)
В случай, че в името на файла има интервали, трябва да ги използвате в единични кавички.
= SUM ('Примерен файл.xlsx'! SalesData)
В случай, че имате именовани диапазони на ниво лист, тогава трябва да посочите името на работната книга, както и работния лист, когато го препращате от външна работна книга.
По-долу е даден пример за позоваване на диапазон на ниво лист:
= SUM ('[Примерен файл.xlsx] лист1'! SalesData)
Както споменах и по -горе, винаги е най -добре да създадете имена на диапазони на ниво работна книга, освен ако нямате сериозна причина да създадете първо ниво на работен лист.
В случай, че препращате към именован диапазон в затворена работна книга, ще трябва да посочите и пътя на файла. По -долу е даден пример за това:
= SUM ('C: \ Users \ sumit \ Desktop \ Example File.xlsx'! SalesData)
Когато създавате препратка към именован диапазон в отворена работна книга и след това затваряте работната книга, Excel автоматично променя препратката и добавя пътя на файла.
Как да създадете препратка към именован диапазон
Ако създавате и работите с много именувани диапазони, не е възможно да запомните името на всеки от тях.
Excel ви помага, като ви показва списък с всички именовани диапазони, които сте създали, и ви позволява да ги вмъкнете във формули с едно щракване.
Да предположим, че сте създали именован диапазон SalesData, който искате да използвате във формула, за да СУМИРАТЕ всички стойности в посочения диапазон.
Ето стъпките за това:
- Изберете клетката, в която искате да въведете формулата.
- Въведете формулата до точката, в която трябва да вмъкнете именования диапазон
- Натиснете клавиша F3 на клавиатурата си. Това ще отвори диалоговия прозорец Paste Name със списъка с всички създадени от вас имена
- Щракнете двукратно върху името, което искате да вмъкнете.
Горните стъпки биха вмъкнали името във формулата и можете да продължите да работите по формулата.
Забележка: Тази техника работи само за диапазоните от имена, които са в една и съща работна книга. Ако искате да получите имената във външна работна книга, ще трябва да отидете там, да проверите името с помощта на диалоговия прозорец Paste Name и след това да се върнете и да го използвате във формулата си (в правилния формат, използвайки името на файла).Това е всичко, което трябва да знаете за това как да препращате към други листове или работни книги и как да създадете външна справка в Excel.
Надявам се, че сте намерили този урок за полезен.
