Изтеглете примерната работна книга
Този урок ще покаже как да намерите най -голямата стойност на клетката, която отговаря на специфични условия в Excel и Google Sheets.
Максимална функция на масив
Функцията MAX идентифицира най -голямата стойност в поредица от числа.
Можем да използваме MAX функцията, комбинирана с IF функция, за да идентифицираме най -голямата стойност, която отговаря на определено условие.
На потребителите на Google Sheets и Excel 2022 или по -нова версия се препоръчва да използват по -простата функция MAXIFS. Това е обяснено в по -късен раздел.
Този пример ще използва функциите MAX и IF във формула на масив, за да идентифицира най -големия Размер на поръчката за всеки Име на магазина
| 1 | {= MAX (IF (B3: B8 = "A", D3: D8))} |
В Office 365 и версии на Excel след 2022 г. можете просто да въведете горната формула, както обикновено (чрез натискане на ENTER).
За Excel 2022 и по -ранни обаче трябва да въведете формулата, като натиснете CTRL + SHIFT + ENTER. След като направите това, ще забележите, че около формулата се появяват фигурни скоби.
За да покажем как работи тази формула, нека я разделим на стъпки.
Това е нашата последна формула (показана без автоматично добавените скоби за формула на масива):
| 1 | = MAX (IF (B3: B8 = "A", D3: D8)) |
Първо стойностите на диапазона от клетки се добавят към формулата като масиви:
| 1 | = MAX (IF ({"A"; "B"; "A"; "B"; "A"; "B"} = "A", {500; 400; 300; 700; 600; 200})) |
След това Име на магазина = ”A” условие създава масив от TRUE/FALSE стойности:
| 1 | = MAX (IF ({TRUE; FALSE; TRUE; FALSE; TRUE; FALSE}, {500; 400; 300; 700; 600; 200})) |
След това функцията IF променя всички TRUE стойности в съответните Размер на поръчката:
| 1 | = MAX ({500; FALSE; 300; FALSE; 600; FALSE}) |
Функцията MAX идентифицира най -голямото число в масива, като пренебрегва всички FALSE стойности, за да покаже най -голямото Размер на поръчката за Име на магазина = „А“:
| 1 | =600 |
Макс ако - множество критерии
Можем също да идентифицираме най -голямата стойност въз основа на множество критерии, като използваме булева логика.
Този пример ще покаже най -големия Размер на поръчката за всеки Име на магазина, но за Дати на поръчка преди 4/30/2021 с помощта на функциите MAX, IF и DATE:
| 1 | {= MAX (IF ((B3: B8 = "A")*(C3: C8 |
Забележете тук, че умножаваме два набора от ИСТИННИ/НЕВЯРНИ критерии заедно:
| 1 | (B3: B8 = "A") * (C3: C8 |
Ако и двата критерия са TRUE, тогава общото условие ще се изчисли като TRUE, но ако един (или повече) критерии са FALSE, той ще се изчисли като FALSE.
Използвайки тази методология, е възможно да се добавят много различни критерии към тази формула.
Max If - Множество критерии с препратки към клетки
Обикновено не е добра практика да кодирате стойности във формули. Вместо това е по -гъвкаво да се използват отделни клетки за определяне на критериите.
За да съответства на Име на магазина до стойността, показана в колона F, можем да актуализираме формулата така:
| 1 | {= MAX (IF ((B3: B8 = F3)*(C3: C8 |
Заключване на препратки към клетки
За да направим нашите формули по -лесни за четене, показахме формулите без заключени клетъчни препратки:
| 1 | {= MAX (IF ((B3: B8 = F3)*(C3: C8 |
Но тези формули няма да работят правилно, когато копирате и поставите другаде във вашия файл. Вместо това трябва да използвате заключени клетъчни препратки по следния начин:
| 1 | {= MAX (IF (($ B $ 3: $ B $ 8 = F3)*($ C $ 3: $ C $ 8 |
Прочетете нашата статия за Заключване на препратки към клетки, за да научите повече.
Функция MAXIFS
Потребителите на Google Sheets и Excel 2022 или по -нова версия могат да използват единичната функция MAXIFS, за да възпроизведат поведението на функциите MAX и IF, показани в по -ранните примери.
Този следващ пример използва функциите MAXIFS и DATE, за да покаже най -големия Размер на поръчката за всеки Име на магазина за Дати на поръчка преди 30.04.2021 г .:
| 1 | = MAXIFS (D3: D8, B3: B8, "A", C3: C8, "<" & DATE (2021,4,30)) |
Функцията MAXIFS не изисква потребителят да натиска CTRL + SHIFT + ENTER при въвеждане на формулата.
Макс. Ако (максимална стойност с условие) в Google Таблици
Примерите, показани по -горе, работят точно по същия начин в Google Sheets като в Excel, но тъй като функцията MAXIFS е налична, се препоръчва да се използва тази единична функция, вместо да се комбинират функциите MAX и IF.
Ако е необходимо да се използват примерите, използващи функции MAX и IF, тогава Google Sheets изисква да ги въведете като формули за масиви. Вместо да показвате формулата с квадратни скоби в Excel {}, натискането на CTRL + SHIFT + ENTER автоматично добавя функцията ARRAYFORMULA около формулата:
| 1 | = ARRAYFORMULA (MAX (IF ((B3: B8 = "A"))*(C3: C8 |
