Lejupielādējiet darbgrāmatas piemēru
Šī apmācība parādīs, kā atrast lielāko šūnas vērtību, kas atbilst īpašiem nosacījumiem programmā Excel un Google izklājlapās.
Maksimālā masīva funkcija
Funkcija MAX identificē lielāko vērtību virknē.
Mēs varam izmantot funkciju MAX apvienojumā ar IF funkciju, lai noteiktu lielāko vērtību, kas atbilst noteiktam nosacījumam.
Google izklājlapu un Excel 2022 vai jaunāku versiju lietotājiem ieteicams izmantot vienkāršāku funkciju MAXIFS. Tas ir izskaidrots vēlāk.
Šajā piemērā masīva formulā tiks izmantotas funkcijas MAX un IF, lai noteiktu lielāko Pasūtījuma lielums katram Veikala nosaukums
| 1 | {= MAX (JA (B3: B8 = "A", D3: D8))} |
Programmā Office 365 un Excel versijās pēc 2022. gada varat vienkārši ievadīt iepriekš minēto formulu, kā parasti (nospiežot ENTER).
Tomēr programmai Excel 2022 un vecākām formulām ir jāievada, nospiežot CTRL + SHIFT + ENTER. Pēc tam jūs pamanīsit, ka ap formulu parādās cirtaini masīvu kronšteini.
Lai parādītu, kā šī formula darbojas, sadalīsim to soļos.
Šī ir mūsu galīgā formula (parādīta bez automātiski pievienotām masīva formulu iekavām):
| 1 | = MAX (JA (B3: B8 = "A", D3: D8)) |
Pirmkārt, šūnu diapazona vērtības tiek pievienotas formulai kā masīvi:
| 1 | = MAX (JA ({(A ";" B ";" A ";" B ";" A ";" B "} =" A ", {500; 400; 300; 700; 600; 200})) |
Tālāk Veikala nosaukums = “A” nosacījums rada TRUE/FALSE vērtību masīvu:
| 1 | = MAX (JA ({TRUE; FALSE; TRUE; FALSE; TRUE; FALSE}, {500; 400; 300; 700; 600; 200})) |
Tad funkcija IF maina visas patiesās vērtības uz atbilstošajām Pasūtījuma lielums:
| 1 | = MAX ({500; FALSE; 300; FALSE; 600; FALSE}) |
Funkcija MAX identificē lielāko masīva skaitli, vienlaikus ignorējot visas FALSE vērtības, lai parādītu lielāko Pasūtījuma lielums priekš Veikala nosaukums = "A":
| 1 | =600 |
Maksimālais If - vairāki kritēriji
Mēs varam arī noteikt lielāko vērtību, pamatojoties uz vairākiem kritērijiem, izmantojot Būla loģiku.
Šis piemērs parādīs lielāko Pasūtījuma lielums katram Veikala nosaukums, bet priekš Pasūtīšanas datumi pirms 30.01.2021, izmantojot funkcijas MAX, IF un DATE:
| 1 | {= MAX (JA ((B3: B8 = "A")*(C3: C8 |
Ņemiet vērā, ka mēs reizinām divas patiesu/nepatiesu kritēriju kopas kopā:
| 1 | (B3: B8 = "A") * (C3: C8 |
Ja abi kritēriji ir TRUE, kopējais nosacījums tiks aprēķināts kā TRUE, bet, ja viens (vai vairāki) kritēriji ir FALSE, tas tiks aprēķināts kā FALSE.
Izmantojot šo metodiku, šai formulai ir iespējams pievienot daudz dažādu kritēriju.
Maksimālais If - vairāki kritēriji ar šūnu atsaucēm
Parasti nav laba prakse vērtību kodēšanu formulās. Tā vietā ir elastīgāk izmantot atsevišķas šūnas, lai definētu kritērijus.
Lai atbilstu Veikala nosaukums līdz F slejā parādītajai vērtībai, mēs varam atjaunināt formulu, lai tā būtu šāda:
| 1 | {= MAX (JA ((B3: B8 = F3)*(C3: C8 |
Šūnu atsauču bloķēšana
Lai mūsu formulas būtu vieglāk lasāmas, mēs esam parādījuši formulas bez bloķētām šūnu atsaucēm:
| 1 | {= MAX (JA ((B3: B8 = F3)*(C3: C8 |
Bet šīs formulas nedarbosies pareizi, kopējot un ielīmējot citur jūsu failā. Tā vietā jums vajadzētu izmantot šādas bloķētas šūnu atsauces:
| 1 | {= MAX (IF (($ B $ 3: $ B $ 8 = F3)*($ C $ 3: $ C $ 8) |
Lai uzzinātu vairāk, izlasiet mūsu rakstu par šūnu atsauču bloķēšanu.
Funkcija MAXIFS
Google izklājlapu un Excel 2022 vai jaunākas versijas lietotāji var izmantot vienu funkciju MAXIFS, lai atkārtotu iepriekšējos piemēros parādīto funkciju MAX un IF darbību.
Šajā nākamajā piemērā tiek izmantotas funkcijas MAXIFS un DATE, lai parādītu lielāko Pasūtījuma lielums katram Veikala nosaukums priekš Pasūtīšanas datumi pirms 30.01.2021:
| 1 | = MAXIFS (D3: D8, B3: B8, "A", C3: C8, "<" & DATE (2021,4,30)) |
Funkcija MAXIFS neprasa lietotājam, ievadot formulu, nospiest taustiņu kombināciju CTRL + SHIFT + ENTER.
Maksimālais If (maksimālā vērtība ar nosacījumu) Google izklājlapās
Iepriekš redzamie piemēri Google izklājlapās darbojas tieši tāpat kā programmā Excel, taču, tā kā ir pieejama funkcija MAXIFS, ieteicams izmantot šo vienu funkciju, nevis apvienot funkcijas MAX un IF.
Ja jāizmanto piemēri, kuros tiek izmantotas MAX un IF funkcijas, tad Google izklājlapās ir jāievada tās kā masīva formulas. Tā vietā, lai parādītu formulu ar Excel cirtainu masīvu iekavām {}, nospiežot CTRL + SHIFT + ENTER, automātiski tiek pievienota funkcija ARRAYFORMULA ap formulu:
| 1 | = ARRAYFORMULA (MAX (IF (((B3: B8 = "A"))*(C3: C8 |
