Да предположим, че имате списък с продукти с тяхната цена в таблица на Excel. Сега трябва да създадете пакети, които може да съдържат различни продукти от наличните артикули. Сега, в крайна сметка, бихте искали да изчислите общата цена на всеки пакет. За един път можете да го направите ръчно, но ако това е редовната ви задача, тогава е по -добре да автоматизирате тази задача с някои елегантни формули. И за това е тази статия. Ще научим как да изчисляваме ценообразуването на пакети или групи артикули, използвайки една формула.
Обща формула:
| = SUMPRODUCT (ценови диапазон,-(чек_диапазон = "y")) |
ценови диапазон:Това е гамата, която съдържа цената на продуктите.
check_range: Това е диапазонът, в който искаме да поставим чека си. Ако даден продукт е част от пакета, поставяме y в напречното сечение на пакета и продукта.
"y"= Това е проверката, която поставяме, за да включим продукт в пакета.
Нека имаме пример за изчистване на концепцията.
Пример: Създайте формула за ценообразуване на пакети в Excel.
Ние приемаме същия сценарий, който обсъждахме в началото. Подготвили сме таблицата в диапазон B2: F9. Нека определим променливите, от които се нуждаем.
Ценови диапазон:Ценовият диапазон е C2: C9. Тъй като е фиксиран, можем да наименуваме диапазон или да използваме абсолютната препратка към него. В този пример ще използвам абсолютна справка $ C $ 2: $ C $ 9.
Диапазон на проверка:Това са диапазонът, който съдържа проверки (колоната на пакета). Те са D3: D9, E3: E9 и F3: f9.
Нека поставим тези стойности в общата формула.
Напишете тази формула в D10, за да изчислите цената на пакета.
| = СУМПРОДУКТ ($ C $ 3: $ C $ 9,-(D3: D9 = "y")) |
Натиснете Enter. Имате цената на пакета за пакет 1, изчислена в клетка D10. Копирайте тази формула в съседни клетки, за да изчислите цената на всички пакети.
Как работи?
Формулата работи отвътре навън. Така че първо -(D3: D9 = "y") е решен.
Това връща масив от 1 и 0 като. 1 за всеки y и 0 всичко друго в диапазон D3: D9.
| {1;1;0;1;0;1;0} |
След това $ C $ 3: $ C $ 9 се преобразува в масив, който съдържа цената на всеки артикул/продукт.
| {100;200;20;10;12;15;25} |
Сега функцията SUMPRODUCT има това в себе си.
| = СУМПРОДУКТ ({100; 200; 20; 10; 12; 15; 25}, {1; 1; 0; 1; 0; 1; 0}) |
Сега, както прави функцията SUMPRODUCT, тя умножава всяка стойност в един масив до същия индексиран масив в друг масив и накрая обобщава тези стойности. Това означава, че всяка цена, която съвпада с 0 в друг масив, се превръща в 0. {100; 200; 0; 10; 0; 15; 0}. Сега този масив се сумира. Това ни дава 325 за пакет 1. Същото се прави за всички пакети.
Алтернативна формула:
Алтернативната формула е, разбира се, функцията SUMIF и SUMIFS.
| = SUMIF (D3: D9, "y", $ C $ 3: $ C $ 9) |
и
| = SUMIFS ($ C $ 3: $ C $ 9, D3: D9, "y") |
Това са класическите отговори, но формулата SUMPRODUCT също е по -бърза и фантастична.
Така че да, момчета, ето как можете лесно да изчислите цената на пакета в Excel. Надявам се, че беше достатъчно обяснително. Ако съм пропуснал нещо или имате някакви съмнения относно тази статия или друго съмнение, свързано с Excel, попитайте го в секцията за коментари по -долу.
Пребройте общите съвпадения в два диапазона в Excel | Научете как да броите общо съвпадения в два диапазона, като използвате функцията SUMPROUDCT.
SUMIFS използва ИЛИ-ИЛИ логика | SUMIFS може да се използва и с логика ИЛИ. Дефолтната логика, която SUMIFS използва, е логиката И.
СУМПРОДУКТ с логика IF | Научете как да използвате SUMPRODUCT с логика IF, без да използвате функцията IF във формулата.
50 преки пътища в Excel за повишаване на вашата производителност | Бъдете по -бързи в задачата си. Тези 50 преки пътища ще ви накарат да работите още по -бързо в Excel.
Функцията VLOOKUP в Excel | Това е една от най -използваните и популярни функции на excel, която се използва за търсене на стойност от различни диапазони и листове.
COUNTIF в Excel 2016 | Пребройте стойностите с условия, използвайки тази невероятна функция. Не е необходимо да филтрирате данните си, за да преброите конкретна стойност. Функцията Countif е от съществено значение за подготовката на вашето табло.
Как да използвате функцията SUMIF в Excel | Това е друга основна функция на таблото. Това ви помага да обобщите стойностите при конкретни условия.