Ако искате да получите сумата от колона, като използвате само името на колоната, можете да направите това по 3 лесни начина в Excel. Нека разгледаме тези начини.
За разлика от други статии, нека първо да видим сценария.
Тук имам таблица на продажбите, направена от различни продавачи през различни месеци.
Сега задачата е да се получи сумата от продажбите за даден месец в 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 (INDEX (колони ,, MATCH (заглавие, заглавки, 0))) |
Всички променливи в този метод са същите като метода SUMPRODUCT. Нека просто го приложим, за да разрешим проблема. Напишете тази формула в C10.
| = SUM (INDEX (C3: N7,, MATCH (B10, C2: N2,0))) |
Това връща:
Как работи?
Формулата е решена отвътре навън. Първо, функцията MATCH връща индекса на съответстващия месец от диапазона C2: N2. Тъй като имаме май в B1o, получаваме 5. Сега формулата става
| = SUM (ИНДЕКС (C3: N7,, 5)) |
След това функцията INDEX връща стойности от 5 -та колона на C3: N7. Сега формулата става:
| = SUM ({6; 12; 15; 15; 8}) |
И накрая, получаваме сумата от тези стойности.
Метод 3: Сумиране на цялата колона в таблица, като се използва именован диапазон и функция ИНДИРЕКТНО
Всичко става просто, ако посочите диапазоните си като заглавия на колони. В този метод първо трябва да назовем колоните като техните имена на заглавия.
Изберете таблицата с заглавията и натиснете CTRL+SHIFT+F3. Той ще отвори диалогов прозорец за създаване на име от диапазоните. Проверете горния ред и натиснете бутона OK.
Той ще посочи всички колони с данни като заглавия.
Сега общата формула за сумиране на съответстващата колона ще бъде:
| = SUM (INDIRECT (заглавие)) |
Заглавие: Това е името на колоната, която искате да сумирате. В този пример, В10 е този, който засега съдържа май.
За да приложите тази обща формула, напишете тази формула в клетка C10.
| = SUM (ИНДИРЕКТНО (B10)) |
Това връща сумата от май месец:
Друг метод е подобен на този. В този метод използваме таблици на Excel и неговото структурирано именуване. Да речем, ако сте кръстили горната таблица като table1. Тогава тази формула ще работи същата като горната формула.
| = SUM (INDIRECT ("Таблица1 [" & B10 & "]")) |
Как работи?
В тази формула функцията INDIRECT приема препратката към името и я преобразува в действителна справка за име. Процедурата нататък е проста. Функцията SUM обобщава именувания диапазон.
Така че да, момчета, ето как можете да сумирате съответстващата колона в Excel. Надявам се да ви е полезен и обяснителен. Ако имате някакви съмнения относно тази статия или друга тема, свързана с Excel/VBA, попитайте в секцията за коментари по -долу.
Как да сумирате чрез съвпадение на ред и колона в Excel |SUMPRODUCT е най -универсалната функция, когато става въпрос за сумиране и преброяване на стойности с трудни критерии. Общата функция за сумиране чрез съвпадение на колона и ред е …
SUMIF с 3D справка в Excel |Забавният факт е, че нормалните Excel 3D препратки не работят с условни функции, като функцията SUMIF. В тази статия ще научим как да накараме 3D референции да работят с функцията SUMIF.
Относителна и абсолютна справка в Excel | Препратките в Excel са важна тема за всеки начинаещ. Дори опитни потребители на Excel правят грешки при препратките.
Справка за динамичен работен лист | Давайте справочни листове динамично, като използвате функцията INDIRECT на excel. Това е просто…
Разширяване на препратки в Excel | Разширяващата се справка се разширява, когато се копира надолу или надясно. За целта използваме знака $ преди номера на колоната и реда. Ето един пример…
Всичко за абсолютната справка | Типът позоваване по подразбиране в excel е относителен, но ако искате препратката към клетките и диапазоните да е абсолютна, използвайте знака $. Ето всички аспекти на абсолютните препратки в Excel.
Популярни статии:
50 преки пътища в Excel за повишаване на вашата производителност | Бъдете по -бързи в задачата си. Тези 50 преки пътища ще направят работата ви още по -бърза в Excel.
Функцията VLOOKUP в Excel | Това е една от най -използваните и популярни функции на excel, която се използва за търсене на стойност от различни диапазони и листове.
COUNTIF в Excel 2016 | Пребройте стойностите с условия, използвайки тази невероятна функция. Не е необходимо да филтрирате данните си, за да преброите конкретна стойност. Функцията Countif е от съществено значение за подготовката на вашето табло.
Как да използвате функцията SUMIF в Excel | Това е друга основна функция на таблото. Това ви помага да обобщите стойностите при конкретни условия.