В тази статия ще научим как да създаваме изчислени колони в Excel.
Сценарий
Ако искате да получите сумата от колона, като използвате само името на колоната, можете да направите това по 3 лесни начина в Excel. Нека разгледаме тези начини.
За разлика от други статии, нека първо да видим с различни сценарии.
Как да създадете изчислена колона в таблица:
- Изберете клетка в една от колоните на таблица или празна клетка точно до последната колона, в примера под избраната клетка е G2.
- Вмъкнете формулата, която изчислява продажбите на единица, въведете равния (=) символ, изберете клетка F2, въведете символа за разделяне (/) и след това изберете клетка E2 и натиснете Enter.
Формулата се копира автоматично във всички клетки в колоната.
Пример:
Тук имам таблица на продажбите, направена от различни продавачи през различни месеци.
Сега задачата е да се получи сумата от продажбите за даден месец в Cell C10. Ако променим месеца в B10, сумата трябва да се промени и да върне сумата за този месец, без да променя нищо във формулата.
Метод 1: Сумиране на цялата колона в таблица с помощта на функцията SUMPRODUCT.
Синтаксисът на метода SUMPRODUCT за сумиране на съответстваща колона е:
| = SUMPRODUCT ((колони)*(заглавки = заглавие)) |
Колони: Това е двуизмерният диапазон на колоните, който искате да сумирате. Не трябва да съдържа заглавки. В горната таблица е C3: N7.
Заглавки: Това е диапазонът на заглавките на колони които искате да обобщите. В горните данни това е C2: N2.
Заглавие: Това е заглавието, което искате да съпоставите. В горния пример той е в B10.
Без допълнително забавяне нека използваме формулата.
| =СУМПРОДУКТ((C3: N7)*(C2: N2 = B10)) |
и това ще се върне:
Как работи?
Това е просто. Във формулата изявлението C2: N2 = B10 връща масив, който съдържа всички FALSE стойности, с изключение на този, който съвпада с B10. Сега формулата е
| = SUMPRODUCT ((C3: N7)*{FALSE, FALSE, FALSE, FALSE,ВЯРНО, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE}) |
Сега C3: N7 се умножава към всяка стойност на този масив. Всяка колона става нула, с изключение на колоната, умножена по TRUE. Сега формулата става:
| = СУМПРОДУКТ ({0,0,0,0,6,0,0,0,0,0,0,0; 0,0,0,0,12,0,0,0,0,0,0, 0; 0,0,0,0,15,0,0,0,0,0,0,0; 0,0,0,0,15,0,0,0,0,0,0,0; 0,0,0,0,8,0,0,0,0,0,0,0}) |
Сега този масив се сумира и получаваме сумата от колоната, която съответства на колоната в клетката B10.
Метод 2: Сумиране на цялата колона в таблица с помощта на функцията INDEX-MATCH.
Синтаксисът на метода за сумиране на заглавието на съответната колона в Excel е:
| =SUM(ИНДЕКС(колони,,СЪВПАДА(заглавие, заглавки, 0))) |
Всички променливи в този метод са същите като метода SUMPRODUCT. Нека просто го приложим, за да разрешим проблема. Напишете тази формула в C10.
| =SUM(ИНДЕКС(C3: N7 ,,СЪВПАДА(B10, C2: N2,0))) |
Това връща:
Метод 3: Сумиране на цялата колона в таблица, като се използва именован диапазон и функция ИНДИРЕКТНО
Всичко става просто, ако посочите диапазоните си като заглавия на колони. В този метод първо трябва да назовем колоните като техните имена на заглавия.
Изберете таблицата с заглавията и натиснете CTRL+SHIFT+F3. Той ще отвори диалогов прозорец за създаване на име от диапазоните. Проверете горния ред и натиснете бутона OK.
Той ще посочи всички колони с данни като заглавия.
Сега общата формула за сумиране на съответстващата колона ще бъде:
| =SUM(НЕПРЯК(заглавие)) |
Заглавие: Това е името на колоната, която искате да сумирате. В този пример, В10 е този, който засега съдържа май.
За да приложите тази обща формула, напишете тази формула в клетка C10.
| =SUM(НЕПРЯК(B10)) |
Това връща сумата от май месец:
Друг метод е подобен на този. В този метод използваме таблици на Excel и неговото структурирано именуване. Да речем, ако сте кръстили горната таблица като table1. Тогава тази формула ще работи същата като горната формула.
| =SUM(ИНДИРЕКТНО ("Таблица1 [" & B10 & "]")) |
Надявам се, че тази статия за това как да създадете изчислена колона (и) в Excel е обяснителна. Намерете още статии за изчисляване на стойности и свързани формули на Excel тук. Ако ви харесаха нашите блогове, споделете го с приятелите си във Facebook. Можете също така да ни следвате в Twitter и Facebook. Ще се радваме да чуем от вас, уведомете ни как можем да подобрим, допълним или обновим работата си и да я подобрим. Пишете ни на имейл сайта.
Как да сумирате чрез съвпадение на ред и колона в Excel : SUMPRODUCT е най -универсалната функция, когато става въпрос за сумиране и преброяване на стойности с трудни критерии. Общата функция за сумиране чрез съвпадение на колона и ред е …
SUMIF с 3D справка в Excel : Забавният факт е, че нормалните Excel 3D препратки не работят с условни функции, като функцията SUMIF. В тази статия ще научим как да накараме 3D референции да работят с функцията SUMIF.
Относителна и абсолютна справка в Excel: Препратките в Excel са важна тема за всеки начинаещ. Дори опитни потребители на Excel правят грешки при препратките.
Справка за динамичен работен лист: Давайте справочни листове динамично, като използвате функцията INDIRECT на excel. Това е просто…
Разширяване на препратки в Excel: Разширяващата се справка се разширява, когато се копира надолу или надясно. За целта използваме знака $ преди номера на колоната и реда. Ето един пример…
Всичко за абсолютната справка: Типът позоваване по подразбиране в excel е относителен, но ако искате препратката към клетките и диапазоните да е абсолютна, използвайте знака $. Ето всички аспекти на абсолютните препратки в Excel.
Популярни статии:
Как да използвате функцията IF в Excel : Изразът IF в Excel проверява условието и връща конкретна стойност, ако условието е TRUE или връща друга конкретна стойност, ако FALSE.
Как да използвате функцията VLOOKUP в Excel : Това е една от най -използваните и популярни функции на excel, която се използва за търсене на стойност от различни диапазони и листове.
Как да използвате функцията SUMIF в Excel : Това е друга основна функция на таблото. Това ви помага да обобщите стойностите при конкретни условия.
Как да използвате функцията COUNTIF в Excel : Пребройте стойностите с условия, използвайки тази невероятна функция. Не е необходимо да филтрирате данните си, за да броите конкретни стойности. Функцията Countif е от съществено значение за подготовката на вашето табло.