Pieņemsim, ka Excel tabulā ir produktu saraksts ar to cenu. Tagad jums ir jāizveido saišķi, kuros var būt dažādi produkti no pieejamajiem vienumiem. Visbeidzot, jūs vēlaties aprēķināt katras paketes kopējo cenu. Vienu reizi jūs to varat izdarīt manuāli, bet, ja tas ir jūsu parastais uzdevums, tad labāk ir automatizēt šo uzdevumu, izmantojot dažas elegantas formulas. Un tas ir šis raksts. Mēs iemācīsimies aprēķināt saišu vai vienību grupu cenas, izmantojot vienu formulu.
Vispārējā formula:
| = SUMPRODUCT (cenu diapazons,-(check_range = "y")) |
cenu amplitūda:Tas ir diapazons, kas satur produktu cenas.
check_range: Tas ir diapazons, kurā mēs vēlamies ievietot savu čeku. Ja produkts ir daļa no saišķa, mēs ievietojam y saišķa un produkta šķērsgriezumā.
"g"= Šī ir pārbaude, ko mēs veicam, lai komplektā iekļautu kādu produktu.
Lūk, piemērs, lai notīrītu jēdzienu.
Piemērs: izveidojiet formulu saišu cenu noteikšanai programmā Excel.
Mēs izmantojam to pašu scenāriju, ko mēs apspriedām sākumā. Mēs esam sagatavojuši tabulu diapazonā B2: F9. Noskaidrosim vajadzīgos mainīgos.
Cenu amplitūda:Cenu diapazons ir C2: C9. Tā kā tas ir fiksēts, mēs varam nosaukt diapazonu vai izmantot tā absolūto atsauci. Šajā piemērā es izmantošu absolūtu atsauci $ C $ 2: $ C $ 9.
Check_range:Šie ir diapazons, kurā ir pārbaudes (saiņa kolonna). Tie ir D3: D9, E3: E9 un F3: f9.
Iekļausim šīs vērtības vispārējā formulā.
Uzrakstiet šo formulu D10, lai aprēķinātu komplekta cenu.
| = SUMPRODUCT ($ C $ 3: $ C $ 9,-(D3: D9 = "y")) |
Nospiediet Enter. 1. komplekta komplekta izmaksas ir aprēķinātas šūnā D10. Kopējiet šo formulu blakus esošajās šūnās, lai aprēķinātu visu saišu cenu.
Kā tas darbojas?
Formula darbojas no iekšpuses. Tātad vispirms -(D3: D9 = "y") ir atrisināts.
Tas atgriež masīvu 1 un 0 kā. 1 katram y un 0 jebkas cits diapazonā D3: D9.
| {1;1;0;1;0;1;0} |
Tālāk $ C $ 3: $ C $ 9 tiek pārvērsts masīvā, kurā ir norādīta katra vienuma/produkta cena.
| {100;200;20;10;12;15;25} |
Tagad funkcijā SUMPRODUCT tas ir iekļauts.
| = SUMPRODUCT ({100; 200; 20; 10; 12; 15; 25}, {1; 1; 0; 1; 0; 1; 0}) |
Tagad, tāpat kā funkcija SUMPRODUCT, tā reizina katra viena masīva vērtību ar to pašu indeksēto masīvu citā masīvā un galu galā apkopo šīs vērtības. Tas nozīmē, ka katra cena, kas atbilst masīvam 0 citā masīvā, tiek pārvērsta par 0. {100; 200; 0; 10; 0; 15; 0}. Tagad šis masīvs ir apkopots. Tas dod mums 325 par 1. komplektu. Tas pats tiek darīts ar visiem saišķiem.
Alternatīva formula:
Alternatīva formula, protams, ir funkcija SUMIF un SUMIFS.
| = SUMIF (D3: D9, "y", $ C $ 3: $ C $ 9) |
un
| = SUMIFS ($ C $ 3: $ C $ 9, D3: D9, "y") |
Šīs ir klasiskās atbildes, taču arī SUMPRODUCT formula ir ātrāka un izsmalcinātāka.
Tātad, jā, puiši, šādā veidā jūs varat viegli aprēķināt paketes cenu programmā Excel. Es ceru, ka tas bija pietiekami izskaidrojošs. Ja esmu palaidis garām kādu punktu vai jums ir šaubas par šo rakstu vai citas ar Excel saistītas šaubas, jautājiet to komentāru sadaļā zemāk.
Saskaitiet kopējās atbilstības divos diapazonos programmā Excel | Uzziniet, kā saskaitīt kopējo atbilstību divos diapazonos, izmantojot funkciju SUMPROUDCT.
SUMIFS, izmantojot AND-OR loģiku | SUMIFS var izmantot arī ar loģiku VAI. SUMIFS izmantotā defualt loģika ir AND loģika.
SUMPRODUCT ar IF loģiku | Uzziniet, kā lietot SUMPRODUCT ar IF loģiku, formulā neizmantojot IF funkciju.
50 Excel saīsnes, lai palielinātu produktivitāti | Ātrāk izpildiet savu uzdevumu. Šie 50 īsceļi ļaus jums strādāt vēl ātrāk programmā Excel.
Funkcija VLOOKUP programmā Excel | Šī ir viena no visbiežāk izmantotajām un populārākajām Excel funkcijām, kas tiek izmantota, lai meklētu vērtību no dažādiem diapazoniem un lapām.
COUNTIF programmā Excel 2016 | Saskaitiet vērtības ar nosacījumiem, izmantojot šo apbrīnojamo funkciju. Jums nav jāfiltrē dati, lai uzskaitītu konkrētu vērtību. Skaitītāja funkcija ir būtiska, lai sagatavotu informācijas paneli.
Kā lietot SUMIF funkciju programmā Excel | Šī ir vēl viena būtiska informācijas paneļa funkcija. Tas palīdz apkopot vērtības noteiktos apstākļos.