Досега сме научили как да сумираме цялата съвпадаща колона на таблица в Excel. Но как да сумираме стойности, когато трябва да съпоставим колона и ред. В тази статия ще научим как да правим сумата от съвпадащи редове и колони.
Има две формули за това, но първо нека разгледаме сценария.
Тук имам таблица, в която се записват продажбите, извършени от служителите през различни месеци. Името на служителите може да се повтаря. Вижте фигурата по -долу:
Трябва да получим сумата от майския месец, в който продавачът е Доналд.
Метод 1: Обобщаване на съответстващите заглавки на колони и заглавки с помощта на функцията SUMPRODUCT.
The SUMPRODUCT функция е най -универсалната функция, когато става въпрос за сумиране и преброяване на стойности с трудни критерии. Общата функция за сумиране чрез съвпадение на колона и ред е:
| = SUMPRODUCT ((колони)*(колони_заглавки = колона_заглавие)*(ред_заглавки = ред_заглавие) |
Колони:Това е двуизмерният диапазон на колоните, който искате да сумирате. Не трябва да съдържа заглавки. В горната таблица е C3: N7.
колони_заглавки:Това е диапазонът на заглавките наколони които искате да обобщите. В горните данни това е C2: N2.
колона_заглавие: Това е заглавието, което искате да съпоставите. В горния пример е в B13.
Без допълнително забавяне нека използваме формулата.
row_headers:Това е диапазонът на заглавките наредове които искате да обобщите. В горните данни това е B3: B10.
row_heading: Това е заглавието, което искате да съпоставите в редове. В горния пример той е във F13.
Без допълнително забавяне нека използваме формулата.
Напишете тази формула в клетка D13 и оставете Excel да направи магия (няма такова нещо като магия) …
| = СУМПРОДУКТ ((C3: N10)*(C2: N2 = B13)*(B3: B10 = E13)) |
Това връща стойността:
Сега, когато промените месеца или продавача, сумата ще се промени според заглавията на редовете и колоните.
Как работи?
Тази проста логическа логика.
(C2: N2 = B13): Това изявление ще върне масив от TRUE и FALSE. Всички съвпадащи стойности в колоната са true, а други ще имат false. В този случай ще имаме само едно True, тъй като диапазонът C2: N2 съдържа само един екземпляр май на 5th Местоположение.
(B3: B10 = E13): Това ще работи по същия начин както по -горе и ще върне масив TRUE и FALSE. Всички съвпадащи стойности ще имат TRUE, а други ще имат FALSE. В този случай ще имаме 2 ИСТИНИ, тъй като диапазонът B3: B10 има два случая на „Доналд“.
(C2: N2 = B13)*(B3: B10 = E13): Сега умножаваме масивите, върнати с изрази. Това ще реализира и логика и ще получим масив от 1s и 0s. Сега ще имаме 2D масив, който ще съдържа 2 1s и почивка 0s.
(C3: N10)*(C2: N2 = B13)*(B3: B10 = E13)= Накрая умножаваме 2D масива с 2D таблицата. Той отново ще върне масив от 0 и числата, които отговарят на критериите.
Накрая, СУМПРОДУКТ функцията ще обобщи масива, което ще доведе до желания изход.
Метод 2: Обобщаване на заглавката на съответстващата колона и заглавката с помощта на функцията SUM и IF
Общата формула за сумиране на съвпадащи ред и колона с помощта на функцията SUM и IF Excel е:
| = SUM (IF (колони_заглавки = колона_заглавие, IF (ред_заглавия = ред_заглавие, колони))) |
Всички променливи са същите като в описания по-горе метод. Тук те просто трябва да се използват в различен ред.
Напишете тази формула в клетка D13:
| = SUM (IF (C2: N2 = B13, IF (B3: B10 = E13, C3: N10))) |
Това връща верния отговор. Вижте екранната снимка по -долу:
Как работи?
Логиката е същата като първия метод SUMPRODCUT само механизмът е различен. Ако го обясня накратко, вътрешната IF функция връща 2D масив със същото измерение като таблицата. Този масив съдържа броя на два съответстващи реда. Тогава вътрешната IF функция съвпада с заглавията с две колони в този масив и връща 2D масива, който съдържа само числата, които съвпадат както с колоната, така и със заглавието. Всички останали елементи на масива ще бъдат FALSE.
И накрая, функцията SUM обобщава този масив и получаваме нашата сума.
Така че да, момчета, ето как можете да обобщите съвпадащите редове и колони от таблица в Excel. Надявам се да ви е било обяснително и полезно. Ако имате някакви съмнения по тази тема или имате други съмнения, свързани с Excel/VBA, попитайте в секцията за коментари по -долу.
Как да сумирате колона в Excel чрез съвпадение на заглавието | Ако искате да получите сумата от колона, като използвате само името на колоната, можете да направите това по 3 лесни начина в 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 | Това е друга основна функция на таблото. Това ви помага да обобщите стойностите при конкретни условия.