Как да създам структурирани референции в Excel?
Структурираните референции започват с Excel таблици. Веднага след като таблиците са създадени в Excel, той автоматично създава структурирани препратки за вас.
Сега погледнете изображението по-долу.
- Стъпка 1: Бях дал връзка към клетка B3. Вместо да показва връзката като B2, тя се показва като Таблица1 (@ Продажби). Тук Table1 е името на таблицата, а @Sales е колоната, към която се позоваваме. Всички клетки в тази колона са посочени от име на таблица и последвано от име на заглавие на колоната.
- Стъпка 2: Сега ще променя името на таблицата на Data_Table и ще променя заглавието на колоната на Количество .
- Стъпка 3: За да промените името на таблицата, поставете курсор вътре в таблицата> отидете на Дизайн> Име на таблицата.
- Стъпка 4: Споменете името на таблицата като Data_Table.
- Стъпка 5: Сега промяната дава препратка към клетката B3.
Така че разбрахме, че структурираната справка има две части Име на таблица и Име на колона.
Примери
Пример # 1
Използвайки структурирани препратки, можете да направите формулата си динамична. За разлика от нормалните препратки към клетки, той позволява формулата да бъде активна в случай на добавяне и изтриване в диапазона от данни.
Позволете ми да приложа формулата SUM както за нормален диапазон, така и за таблица на Excel.
SUM Формула за нормален обхват.
Формула на сумата за таблица в Excel.
Позволете ми да добавя няколко реда към данните както на нормалните, така и на Excel таблиците. Добавих 2 договорени покупки към данните, сега вижте разликата.
Структурираната справка в таблицата на Excel показва актуализираната стойност, но нормалният диапазон от данни не показва актуализираните стойности, освен ако не направите някои промени във формулата ръчно.
Пример # 2
Сега вижте още един пример. Имам информация за име на продукта, количество и цена. Използвайки тази информация, трябва да стигна до стойността на продажбите.
За да се получи продажната стойност, формулата е Количество * Цена . Нека приложим тази формула към таблицата.
Формулата казва (@QTY) * (@PRICE). Това е по-разбираемо от нормалното позоваване на B2 * C2. Не получаваме името на таблицата, ако поставяме формулата вътре в таблицата.
Проблеми със структурираните референции на Excel
Докато използваме структурирани препратки, ние се сблъскваме с някои проблеми, които са изброени по-долу.
Проблем # 1
Структурираните референции също имат свои собствени проблеми. Всички сме запознати с прилагането на формулата на Excel и копирането или плъзгането й в останалите останали клетки. Това не е същият процес в структурираните референции. Работи малко по-различно.
Сега вижте примера по-долу. Приложих формулата SUM в Excel за нормалния диапазон.
Ако искам да сумирам Цена и Стойност на продажбата, просто ще копирам и поставя или плъзгам текущата формула в другите две клетки и тя ще ми даде СУМНАТА стойност на Стойността на цената и продажбата.
Сега приложете същата формула за таблицата на Excel за колоната Количество.
Сега получихме сумата от колоната Qty. Подобно на нормалния диапазон, формулата копира текущата формула и я поставя в колоната Цена, за да получи общата цена.
Боже мой!!! Не показва общата сума на графата Цена; по-скоро все още показва общата сума само на колоната Количество. Така че, не можем да копираме и поставим тази формула в съседната клетка или друга клетка, за да се позовем на относителната колона или ред.
Плъзнете формулата, за да промените референцията
Сега знаем ограничението му. Вече не можем да изпълняваме задачата за копиране и поставяне със структурирани препратки. Тогава как да преодолеем това ограничение?
Решението е много просто. Просто трябва да плъзнем формулата, вместо да копираме. Изберете клетката на формулата и използвайте манипулатора за запълване и я плъзнете до останалите две клетки, за да промените препратката към колоната на Цена и Продажна стойност
Сега имаме актуализирани формули, за да получим съответните суми.
Проблем # 2
Видяхме един проблем с референциите на структурата и намерихме и решението, но тук имаме още един проблем, не можем да направим повикването като абсолютна препратка, ако плъзгаме формулата в други клетки.
Нека да разгледаме примера по-долу сега. Имам таблица с продажби с множество записи и искам да консолидирам данните, като използвам функцията SUMIF в Excel.
Сега ще приложа функцията SUMIF, за да получа консолидираните стойности на продажбите за всеки продукт.
Приложих формулата за месец януари. Тъй като това е структурирана препратка, не можем да копираме и поставим формулата в останалите две колони. Това няма да промени препратката към февруари и март, така че ще изтегля формулата.
О !! Не получих никакви стойности в колоната февруари и март. Какъв би бил проблемът ??? Погледнете отблизо формулата.
Изтеглихме формулата от месец януари. Във функцията SUMIF първият аргумент е Criteria Range Sales_Table (Product), тъй като изтеглихме формулата. Той се промени на Таблица за продажби _ (януари).
Е, как да се справим с него ?? Трябва да направим първия аргумент, т.е. колоната Product като абсолютна, а другите колони като относителна препратка. За разлика от нормалната препратка, ние не разполагаме с лукса да използваме клавиша F4 за промяна на типа на препратка.
Решението е, че трябва да дублираме референтната колона, както е показано на изображението по-долу.
Сега можем да плъзнем формулата в други разгънати две колони. Обхватът на критериите ще бъде постоянен и съответните препратки към други колони ще се променят.
Професионален съвет: За да направим ROW като абсолютна референция, трябва да направим двоен ROW запис, но трябва да поставим символа @ преди името ROW.
= Таблица за продажби (@ (продукт) :( продукт))
Как да изключите структурираната справка в Excel?
Ако не сте фен на структурираните референции, можете да изключите, като следвате стъпките по-долу.
- Стъпка 1: Отидете на ФАЙЛ> Опции.
- Стъпка 2: Формули> Премахнете отметката Използвайте имена на таблици във формули.
Неща за запомняне
- За да направим абсолютната препратка в структурирана препратка, трябва да удвоим името на колоната.
- Не можем да копираме формулата на структурирана справка; вместо това трябва да плъзнем формулата.
- Не можем да видим точно към коя клетка се позоваваме в структурирани препратки.
- Ако не се интересувате от структурирани препратки, можете да ги изключите.
