Merwyn от Англия изпрати този проблем.
Има ли една формула в клетка B2, която може да се копира отдолу и надолу, за да се създаде таблица за умножение?
Целта на Merwyn е да въведе една формула в B2, която лесно може да бъде копирана във всички 144 клетки, за да се създаде референтна таблица за умножение.
Нека атакуваме това като начинаещ в Excel и да видим в какви клопки се натъкваме. Първоначалната реакция може да бъде формулата в B2 =A2*B1. Тази формула дава правилния резултат, но не е подходяща за заданието на Merwyn.
Когато копирате тази формула от клетка B2 в клетка C2, клетките, посочени във формулата, също се преместват над една клетка. Формулата, която беше =A2*B1сега става =C1*B2. Това е функция, разработена в Microsoft Excel и се нарича относителна формула. Докато копирате формула, препратките към клетки във формулата също преместват съответния брой клетки през и надолу.
Има моменти, когато не искате Excel да проявява това поведение и настоящият случай е един от тях. За да попречите на Excel да променя референцията по време на копиране на клетките, вмъкнете „$“ преди частта от референцията, която искате да замразите. Примери:
- $ A1 казва на Excel, че винаги искате да се обърнете към колона А.
- B $ 1 казва на Excel, че винаги искате да се обърнете към ред 1.
- $ B $ 1 казва на Excel, че винаги искате да се позовавате на клетка B1.
Изучаването на този код ще намали драстично времето, необходимо за изграждане на работни листове. Вместо да се налага да въвежда 144 формули, Merwyn може да използва този стенопис, за да въведе една единствена формула и да я копира във всички клетки.
Разглеждайки формулата на Merwyn =B1*A2, ние осъзнаваме, че частта от израза "B1" винаги трябва да сочи към число в ред 1. Това трябва да бъде пренаписано като "B $ 1". Разделът "A2" във формулата винаги трябва да сочи към число в колона А. Това трябва да бъде пренаписано като "$ A2". И така, новата формула в клетка B2 е =B$1*$A2.
След въвеждане на формулата в B2, мога да я копирам и поставя в съответния диапазон. Excel следва моите инструкции и след като копирам, намирам следните формули:
- Клетка M2 съдържа
=M$1*$A2 - Клетка B13 съдържа
=B$1*$A13 - Клетка M13 съдържа
=M$1*$A13
Всеки от тези видове формули има име. Формули без никакви знаци за долар се наричат относителни формули. Формули, при които и редът, и колоната са заключени със знак за долар, се наричат абсолютни формули. Формули, при които редът или колоната са заключени със знак за долар, се наричат смесени формули.
Съществува стенографски метод за въвеждане на знаците за долар. Докато пишете формула и завършвате препратка към клетка, можете да натиснете клавиша, за да превключвате между 4-те референтни типа. Да приемем, че сте започнали да пишете формула и сте въвели =100*G87.
- Натиснете F4 и вашата формула се променя на
=100*$G$87 - Натиснете F4 отново и вашата формула се променя на
=100*G$87 - Натиснете F4 отново и вашата формула се променя на
=100*$G87 - Натиснете отново F4 и вашата формула се връща към оригинала
=100*G87
Можете да направите пауза по време на въвеждането на формула при всяка препратка към клетка, за да ударите F4, докато получите правилния тип препратка. Натискането на клавишите за въвеждане на формулата на Merwyn по-горе е =B1*A1.
Едно от любимите ми приложения на смесени формули се появява, когато имам дълъг списък от записи в колона А. Когато искам бърз и мръсен метод за намиране на дубликати, понякога въвеждам тази формула в клетка B4 и след това я копирам:
=VLOOKUP(A4,$A$2:$A3,1,FALSE)
Обърнете внимание, че вторият член на формулата VLOOKUP използва както абсолютна ($ A $ 2), така и смесена ($ A3) препратка, за да опише диапазона на търсене. На английски казвам на Excel да търси винаги от клетка $ A $ 2 до клетката в колона А точно над текущата клетка. Веднага щом Excel срещне дублирана стойност, резултатът от тази формула се променя от # N / A! до стойност.
С креативно използване на референциите за клетки в $, можете да сте сигурни, че една единствена формула може да бъде копирана в много клетки с правилни резултати.
