Šajā rakstā mēs uzzināsim, kā meklēt precīzas atbilstības, izmantojot funkciju SUMPRODUCT programmā Excel.
Scenārijs:
Vienkārši sakot, strādājot ar datu tabulām, dažreiz programmā Excel mums ir jāmeklē vērtības ar reģistrjutīgajiem burtiem. Uzmeklēšanas funkcijas, piemēram, VLOOKUP vai MATCH, neatrod precīzu atbilstību, jo tās nav reģistrjutīgas funkcijas. Pieņemsim, ka strādāsim pie datiem, kuros 2 vārdi tiek atšķirti, pamatojoties uz reģistrjutību, ti, Džerijs un Džerijs ir divi dažādi vārdi. Varat veikt tādus uzdevumus kā darbības vairākos diapazonos, izmantojot tālāk izskaidroto formulu.
Kā atrisināt problēmu?
Lai atrisinātu šo problēmu, mums būs jāizmanto funkcija SUMPRODUCT & EXACT. Tagad mēs izveidosim formulu no funkcijas. Šeit mums ir dota tabula, un mums ir jāatrod vērtības, kas atbilst precīzai atbilstībai tabulā programmā Excel. Funkcija SUMPRODUCT atgriež atbilstošo TRUE vērtību SUM (kā 1) un ignorē vērtības, kas atbilst FALSE vērtībām (kā 0) atgrieztajā masīvā
Vispārējā formula:
| = SUMPRODUCT ( - (EXACT (lookup_value, lookup_array)), (return_array)) |
lookup_value: meklējamā vērtība.
lookup_array: uzmeklēšanas masīvs uzmeklēšanas vērtībai.
return_array: masīvs, atgriezta vērtība, kas atbilst uzmeklēšanas masīvam.
-: noliegums (-) char maina vērtības, TRUEs vai 1s uz FALSEs vai 0s un FALSEs vai 0s uz TRUEs vai 1s.
Piemērs:
To visu var būt mulsinoši saprast. Tātad, pārbaudīsim šo formulu, palaižot to zemāk redzamajā piemērā. Šeit mums ir dati par datumiem saņemto produktu daudzumu un cenu. Ja kāds produkts tiek pirkts divreiz, tam ir 2 nosaukumi. Šeit mums ir jāatrod priekšmetu skaits visiem svarīgākajiem priekšmetiem. Tāpēc mēs esam saņēmuši 2 sarakstus kā saņemto sarakstu un nepieciešamās sastāvdaļas, kas vajadzīgas formulas slejā. Tagad mēs izmantosim zemāk esošo formulu, lai no tabulas iegūtu "piena" daudzumu.
Izmantojiet formulu:
| = SUMPRODUCT ( - (EXACT (F5, B3: B11)), (C3: C11)) |
F6: meklējiet vērtību F6 šūnā
B3: B11: uzmeklēšanas masīvs ir vienumi
C3: C11: atgriešanas masīvs ir daudzums
-: noliegums (-) char maina vērtības, TRUEs vai 1s uz FALSEs vai 0s un FALSEs vai 0s uz TRUEs vai 1s.
Šeit diapazons ir norādīts kā šūnu atsauce. Nospiediet Enter, lai iegūtu daudzumu.
Kā redzat, 58 ir daudzums, kas atbilst vērtībai "piens" B5 šūnā, nevis "piens" B9 šūnā. Jums būs jāmeklē vērtība "Piens", ja C9 šūnā nepieciešams daudzums "54".
Jūs varat meklēt cenu, kas atbilst vērtībai "piens", vienkārši izmantojot zemāk redzamo formulu.
Izmantojiet formulu:
| = SUMPRODUCT ( - (EXACT (F5, B3: B11)), (D3: D11)) |
F6: meklējiet vērtību F6 šūnā
B3: B11: uzmeklēšanas masīvs ir vienumi
D3: D11: atgriešanas masīvs ir Cena
Šeit mums 58 ir cena, kas atbilst vērtībai "piens" B5 šūnā, nevis "piens" B9 šūnā. Jums būs jāmeklē vērtība "Piens", ja jums nepieciešama cena "15.58" D9 šūnā.
Unikālas vērtības uzmeklēšanas masīvā
Līdzīgi jūs varat atrast unikālās vērtības, izmantojot formulu. Šeit kā piemērs tiek izmantota uzmeklēšanas vērtība "EGGS".
Iepriekš redzamajā attēlā mēs saņēmām vērtības, pielāgojot to pašu formulu. Taču problēma rodas, ja tai ir unikāla vērtība un jūs nemeklējat pareizo uzmeklēšanas vērtību. Tāpat kā šeit, izmantojot vērtību "Maize" formulā, lai meklētu tabulā.
Formula atgriež 0 kā daudzumu un cenu, bet tas nenozīmē, ka maizes daudzums un cena ir 0. Vienkārši, ka formula atgriež 0 kā vērtību, ja meklējamā vērtība netiek atrasta. Tāpēc izmantojiet šo formulu, lai atrastu precīzu atbilstību.
Varat arī veikt uzmeklēšanas precīzās atbilstības, izmantojot funkciju INDEX un MATCH programmā Excel. Uzziniet vairāk par to, kā veikt reģistrjutīgu meklēšanu, izmantojot funkciju INDEX & MATCH programmā Excel. Daļējās atbilstības varat arī meklēt, izmantojot Excel aizstājējzīmes.
Tālāk ir norādītas dažas novērošanas piezīmes.
Piezīmes:
- Formula darbojas tikai, lai meklētu precīzu atbilstību.
- Funkcija SUMPRODUCT neskaitliskas vērtības uzskata par 0.
- Funkcija SUMPRODUCT loģisko vērtību TRUE uzskata par 1 un False par 0.
- Argumentu masīvam jābūt vienāda izmēra, pretējā gadījumā funkcija atgriež kļūdu.
- Funkcija SUMPRODUCT atgriež vērtību, kas atbilst TRUE vērtībām atgrieztajā masīvā pēc atsevišķu produktu uzņemšanas attiecīgajā masīvā.
- Operatoriem patīk vienāds ar ( = ), kas ir mazāks par ( <= ), lielāks nekā ( > ) vai nav vienāds ar (), var veikt, izmantojot formulu, izmantojot tikai skaitļus.
Ceru, ka šis raksts par precīzas atbilstības meklēšanu, izmantojot funkciju SUMPRODUCT programmā Excel, ir paskaidrojošs. Vairāk rakstu par formulu skaitīšanu meklējiet šeit. Ja jums patika mūsu emuāri, kopīgojiet to ar saviem draugiem Facebook. Un arī jūs varat sekot mums Twitter un Facebook. Mēs labprāt uzklausītu jūsu viedokli, dariet mums zināmu, kā mēs varam uzlabot, papildināt vai ieviest jauninājumus mūsu darbā un uzlabot to jūsu labā. Rakstiet mums e -pasta vietnē
Funkcijas SUMPRODUCT izmantošana programmā Excel: Atgriež SUM pēc vērtību reizināšanas vairākos Excel masīvos.
Izmantojiet INDEX un MATCH, lai atrastu vērtību : Formulu INDEX-MATCH izmanto, lai dinamiski un precīzi meklētu vērtību attiecīgajā tabulā. Šī ir alternatīva funkcijai VLOOKUP, un tā novērš funkcijas VLOOKUP trūkumus.
Summa pēc OFFSET grupām rindās un kolonnās : Funkciju OFFSET var izmantot, lai dinamiski summētu šūnu grupu. Šīs grupas var atrasties jebkurā lapas vietā.
Kā atgūt katru N vērtību diapazonā programmā Excel: Izmantojot Excel funkciju OFFSET, mēs varam iegūt vērtības no mainīgām rindām vai kolonnām. Šī formula izmanto funkciju ROW, lai mainītu rindas, un funkciju COLUMN, lai mainītos kolonnās.
Kā izmantot funkciju OFFSET programmā Excel : Funkcija OFFSET ir spēcīga Excel funkcija, kuru daudzi lietotāji nepietiekami novērtē. Bet eksperti zina OFFSET funkcijas spēku un to, kā mēs varam izmantot šo funkciju, lai veiktu dažus maģiskus uzdevumus Excel formulā. Šeit ir funkcijas OFFSET pamati.
Kā programmā Excel izmantot aizstājējzīmes : Saskaitiet šūnas, kas atbilst frāzēm, izmantojot Excel aizstājējzīmes
Populāri raksti
50 Excel saīsne produktivitātes paaugstināšanai : Ātrāk izpildiet savu uzdevumu. Šie 50 īsceļi ļaus jums strādāt vēl ātrāk programmā Excel.
Kā lietot tVLOOKUP funkcija 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 Excel lapām.
Kā programmā Excel izmantot funkciju COUNTIF : Saskaitiet vērtības ar nosacījumiem, izmantojot šo apbrīnojamo funkciju. Jums nav jāfiltrē dati, lai Excel saskaitītu noteiktas vērtības. Funkcija COUNTIF 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 ar konkrētiem nosacījumiem.