Статыстычныя функцыі Excel: Падручнік з прыкладамі для даследавання, частка чацвёртая

Excel забяспечвае шырокі спектр статыстычных функцый, якія выконваюць разлікі ад базавага сярэдняга значэння, медыяны і моды да функцый пошуку.

У гэтым артыкуле мы паглыбімся ў функцыі пошуку.

Звярніце ўвагу, што некаторыя статыстычныя функцыі былі прадстаўлены ў апошніх версіях Excel і таму недаступныя ў старых версіях.

Разліковы час чытання: 18 хвілін

Функцыі пошуку

MAX

Функцыя MAX Excel пералічаны ў катэгорыі статыстычных функцый Microsoft Excel. Вяртае найбольшае значэнне са спісу значэнняў. MAX азначае максімум, і калі вы паказваеце спіс значэнняў, ён шукае ў ім найбольшае значэнне і вяртае гэта значэнне ў выніку.

сінтаксіс

= MAX(number1, [number2], …)

прадметы

• number1:  лік, вочка, якая змяшчае лік, або дыяпазон вочак, які змяшчае лікі, з якіх вы хочаце атрымаць найбольшы лік.
• [number2]:  лік - гэта ячэйка, якая змяшчае лік або дыяпазон вочак, якія змяшчаюць лікі, з якіх вы хочаце атрымаць найбольшы лік.

прыклад

Каб асвоіць функцыю МАКС, нам трэба паспрабаваць яе на прыкладзе, і вы можаце паспрабаваць яе ніжэй:

У наступным прыкладзе мы ўвялі лічбы непасрэдна ў функцыю, падзяліўшы іх коскай.

Заўвага: вы таксама можаце ўвесці лік, выкарыстоўваючы падвойныя двукоссі.

У наступным прыкладзе мы спаслаліся на дыяпазон, і вынік вярнуў 1861 як найбольшае значэнне. Вы таксама можаце спасылацца на масіў.

У наступным прыкладзе мы сутыкнуліся са значэннем памылкі, і функцыя вярнула значэнне памылкі ў выніку.

MAXA

Функцыя Excel Maxa гэта вельмі падобна на Функцыя excel Max.

Адзінае адрозненне паміж дзвюма функцыямі ўзнікае, калі аргумент перадаецца функцыі як спасылка на ячэйку або масіў ячэек.

Функцыя Max ігнаруе лагічныя і тэкставыя значэнні падчас функцыі Maxa лагічнае значэнне лічыцца TRUE як 1, лагічнае значэнне FALSE як 0, а тэкставыя значэнні як 0.

Функцыя MAXA Excel вяртае найбольшае значэнне з прадастаўленага набору лікавых значэнняў, падлічваючы тэкст і лагічнае значэнне FALSE як значэнне 0 і падлік лагічнага значэння TRUE як значэнне 1.

сінтаксіс

= MAXA(number1, [number2], …)

прадметы

• number1:  лік (або масівы лікавых значэнняў), вочка, якая змяшчае лік, або дыяпазон вочак, які змяшчае лікі, з якіх вы хочаце атрымаць найбольшы лік.
• [number2]:  лік - гэта ячэйка, якая змяшчае лік (або масівы лікавых значэнняў) або дыяпазон вочак, якія змяшчаюць лікі, з якіх вы хочаце атрымаць найбольшы лік.

У бягучых версіях Excel (Excel 2007 і пазнейшых) вы можаце падаць да 255 лікавых аргументаў для функцыі Maxa, але ў Excel 2003 функцыя можа прымаць толькі да 30 лікавых аргументаў.

Эсемпі

Прыклад 1

Клетка B1 з наступнай табліцы паказвае функцыю Excel Maxa, які выкарыстоўваецца для атрымання найбольшага значэння з набору значэнняў у ячэйках A1-A5.

Прыклад 2

Клетка B1 з наступнай табліцы паказвае функцыю Excel Maxa, які выкарыстоўваецца для атрымання найбольшага значэння з набору значэнняў у ячэйках A1-A3.

Звярніце ўвагу, што значэнне TRUE у вочку A1 табліцы разглядаецца функцыяй як лікавае значэнне 1 Maxa. Такім чынам, гэта самае вялікае значэнне ў дыяпазоне A1-A3.

Далейшыя прыклады функцыі Excel Maxa прадастаўляюцца на Сайт Microsoft Office .

Памылка функцыі MAXA

Калі вы атрымліваеце памылку ад функцыі Maxa Excel, верагодна, гэта памылка #VALORE!: Узнікае, калі значэнні перадаюцца непасрэдна ў функцыю Maxa яны не лікавыя.

MAXIFS

Функцыя Excel Maxifs гэта функцыя пошуку, якая вяртае максімальнае значэнне з падмноства значэнняў, зададзеных на аснове аднаго або некалькіх крытэраў.

сінтаксіс

= MAXIFS( max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ... )

прадметы

• max_range:  Масіў лікавых значэнняў (або дыяпазон вочак, якія змяшчаюць лікавыя значэнні), з якога вы хочаце вярнуць максімальнае значэнне, калі крытэрыі выкананы.
• criteria_range1:  масіў значэнняў (або дыяпазон ячэек, якія змяшчаюць значэнні) для праверкі criteria1 .(Гэты масіў павінен быць аднолькавай даўжыні з max_range).
• criteria1: Умова для праверкі адносна значэнняў у criteria_range1.
• [criteria_range2, criteria2], [criteria_range3, criteria3], ...: дадатковыя дадатковыя масівы значэнняў для праверкі і адпаведныя ўмовы для праверкі.

Функцыя Maxifs можа апрацоўваць да 126 пар тэм criteria_range e criteria.

Кожны з прадстаўленых крытэрыяў можа быць:

• лікавае значэнне (якое можа быць цэлым лікам, дзесятковым лікам, датай, часам або лагічным значэннем) (напрыклад, 10, 01, TRUE)

або

• тэкставы радок (напрыклад, «Імя», «Мercoleз")

або

• выраз (напрыклад, “>1”, “<>0”).

У criteria звязаны з тэкстам, вы можаце выкарыстоўваць падстаноўныя знакі:

• ? каб адпавядаць любому асобнаму сімвалу
• * каб адпавядаць любой паслядоўнасці знакаў.

Калі а criteria з'яўляецца тэкставым радком або выразам, гэта павінна быць перададзена ў функцыю Maxifs у двукоссі.

Функцыя Maxifs Ён не адчувальны да рэгістра. Таму, напрыклад, пры параўнанні значэнняў в criteria_range з i criteria, тэкставыя радкі "TEXT"А"text” будуць лічыцца роўнымі.

Функцыя Maxifs ён быў упершыню прадстаўлены ў Excel 2019 і таму недаступны ў больш ранніх версіях Excel.

Эсемпі

Табліца ніжэй паказвае штоквартальныя даныя аб продажах 3 гандлёвых прадстаўнікоў.

Функцыя Maxifs можна выкарыстоўваць, каб знайсці максімальную лічбу продажаў для любога квартала, тэрыторыі або гандлёвага прадстаўніка (або любой камбінацыі квартала, тэрыторыі і гандлёвага прадстаўніка).

Давайце разгледзім наступныя прыклады.

Прыклад 1

Каб знайсці максімальную лічбу продажаў за першы квартал:

=MAXIFS( D2:D13, A2:A13, 1 )

што дае вынік $ 456.000.

У гэтым прыкладзе Excel Maxifs вызначае радкі, дзе значэнне ў слупку A роўна 1, і вяртае максімальнае значэнне з адпаведных значэнняў у слупку D.

Гэта значыць функцыя знаходзіць максімальнае са значэнняў $223.000 125.000, $456.000 2 і $3 4 (з вочак DXNUMX, DXNUMX і DXNUMX).

Прыклад 2

Зноў жа, выкарыстоўваючы электронную табліцу дадзеных вышэй, мы таксама можам выкарыстаць функцыю Maxifs, каб знайсці максімальную лічбу продажаў для «Джэфа» на працягу 3 і 4 кварталаў:

=MAXIFS( D2:D13, A2:A13, ">2", C2:C13, "Jeff" )

Гэтая формула вяртае вынік $ 310.000 .

У гэтым прыкладзе Excel Maxifs вызначае радкі, у якіх:

• Значэнне ў слупку А больш за 2

E

• Запіс у слупку C роўны «Джэф»

і вяртае максімальнае з адпаведных значэнняў у слупку D.

Гэта значыць, гэтая формула знаходзіць максімальнае са значэнняў $310.000 261.000 і $8 11 (з вочак DXNUMX і DXNUMX).

Пракансультуйцеся з Сайт Microsoft Office для больш падрабязнай інфармацыі аб прыкладах функцый Excel Maxifs.

Памылка функцыі MAXIFS

Калі вы атрымліваеце памылку ад функцыі Excel Maxifs, верагодна, гэта адно з наступнага:

#VALUE!: Правярае, ці масівы max_range e criteria_range не ўсе ў камплекце аднолькавай даўжыні.

@NAME?: узнікае, калі вы выкарыстоўваеце старую версію Excel (да 2019 г.), якая не падтрымлівае гэтую функцыю Maxifs.

MIN

Функцыя MIN гэта функцыя пошуку, якая вяртае найменшае значэнне са спісу значэнняў. MIN азначае мінімум, і калі вы паказваеце спіс значэнняў, ён шукае самае нізкае значэнне ў ім і вяртае гэта значэнне ў выніку.

сінтаксіс

= MIN(number1, [number2], …)

прадметы

• number1:  лік, вочка, якая змяшчае лік, або дыяпазон вочак, якія ўтрымліваюць лікі, з якіх вы хочаце атрымаць найменшы лік.
• [number2]:  лік, вочка, якая змяшчае лік, або дыяпазон вочак, якія ўтрымліваюць лікі, з якіх вы хочаце атрымаць найменшы лік.

прыклад

У наступным прыкладзе мы ўвялі лічбы непасрэдна ў функцыю, падзяліўшы іх коскай.

Вы таксама можаце ўвесці лік, выкарыстоўваючы падвойныя двукоссі. Цяпер у наступным прыкладзе мы спаслаліся на дыяпазон, і вернуты вынік 1070.

У наступным прыкладзе мы сутыкнуліся са значэннем памылкі, і функцыя вярнула значэнне памылкі ў выніку.

MINA

Функцыя Excel MINA гэта вельмі падобна на Функцыя excel MIN.

Адзінае адрозненне паміж дзвюма функцыямі ўзнікае, калі аргумент перадаецца функцыі як спасылка на ячэйку або масіў ячэек.

У дадзеным выпадку функцыя MIN ігнаруе лагічныя і тэкставыя значэнні падчас функцыі MINA лагічнае значэнне лічыцца TRUE як 1, лагічнае значэнне FALSE як 0, а тэкставыя значэнні як 0.

Функцыя MINA Excel вяртае найменшае значэнне з прадастаўленага набору лікавых значэнняў, падлічваючы тэкст і лагічнае значэнне FALSE як значэнне 0 і падлік лагічнага значэння TRUE як значэнне 1.

сінтаксіс

= MINA( number1, [number2], ... )

прадметы

• number1:  лік, ячэйка, якая змяшчае лік, або дыяпазон вочак (або масіваў лікавых значэнняў), якія змяшчаюць лікі, з якіх вы хочаце атрымаць найменшы лік.
• [number2]:  лік, ячэйка, якая змяшчае лік, або дыяпазон вочак (або масіваў лікавых значэнняў), якія змяшчаюць лікі, з якіх вы хочаце атрымаць найменшы лік.

У бягучых версіях Excel (Excel 2007 і больш позніх версій) вы можаце задаць функцыі да 255 лікавых аргументаў MINA, але ў Excel 2003 функцыя можа прымаць толькі да 30 лікавых аргументаў.

Эсемпі

Прыклад 1

Клетка B1 наступнай электроннай табліцы паказвае функцыю Excel MINA, якая выкарыстоўваецца для атрымання найменшага значэння з набору значэнняў у ячэйках A1-A5.

Прыклад 2

Клетка B1 з наступнай табліцы паказвае функцыю Excel MINA, які выкарыстоўваецца для атрымання найменшага значэння з набору значэнняў у ячэйках A1-A3.

Майце на ўвазе, што значэнне TRUE у камеры A1 табліцы разглядаецца функцыяй як лікавае значэнне 1 MINA. Такім чынам, гэта найменшае значэнне ў дыяпазоне A1-A3.

Больш прыкладаў функцыі Excel MINA прадастаўляюцца на Сайт Microsoft Office .

Памылка функцыі MINA

Калі вы атрымліваеце памылку ад функцыі MINA Excel, верагодна, гэта памылка #VALORE!. Адбываецца, калі значэнні, перададзеныя функцыі MINA, не з'яўляюцца лікавымі.

MINIFS

Функцыя Excel MINIFS гэта функцыя пошуку, якая вяртае мінімальнае значэнне з падмноства значэнняў, вызначаных на аснове аднаго або некалькіх крытэраў.

сінтаксіс

= MINIFS( min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ... )

прадметы

• min_range:  Масіў лікавых значэнняў (або дыяпазон вочак, якія змяшчаюць лікавыя значэнні), з якога вы хочаце вярнуць максімальнае значэнне, калі крытэрыі выкананы.
• criteria_range1:  масіў значэнняў (або дыяпазон ячэек, якія змяшчаюць значэнні) для праверкі criteria1 .(Гэты масіў павінен быць той жа даўжыні, што і min_range ).
• criteria1: Умова для праверкі адносна значэнняў у criteria_range1.
• [criteria_range2, criteria2], [criteria_range3, criteria3], ...: дадатковыя дадатковыя масівы значэнняў для праверкі і адпаведныя ўмовы для праверкі.

Функцыя Minifs можа апрацоўваць да 126 пар тэм criteria_range e criteria.

Кожны з прадстаўленых крытэрыяў можа быць:

• лікавае значэнне (якое можа быць цэлым лікам, дзесятковым лікам, датай, часам або лагічным значэннем) (напрыклад, 10, 01, TRUE)

або

• тэкставы радок (напрыклад, «Імя», «Мercoleз")

або

• выраз (напрыклад, “>1”, “<>0”).

У criteria звязаны з тэкстам, вы можаце выкарыстоўваць падстаноўныя знакі:

• ? каб адпавядаць любому асобнаму сімвалу
• * каб адпавядаць любой паслядоўнасці знакаў.

Калі а criteria з'яўляецца тэкставым радком або выразам, гэта павінна быць перададзена ў функцыю Minifs у двукоссі.

Функцыя Minifs Ён не адчувальны да рэгістра. Таму, напрыклад, пры параўнанні значэнняў в criteria_range з i criteria, тэкставыя радкі "TEXT» і «тэкст» будуць лічыцца адным і тым жа.

Функцыя Minifs ён быў упершыню прадстаўлены ў Excel 2019 і таму недаступны ў больш ранніх версіях Excel.

Эсемпі

У табліцы ніжэй паказаны квартальныя даныя аб продажах 3 прадаўцоў.

Функцыя Minifs можна выкарыстоўваць, каб знайсці мінімальную лічбу продажаў для любога квартала, рэгіёна або гандлёвага прадстаўніка.

Гэта паказана ў наступных прыкладах.

Прыклад 1

Каб знайсці мінімальную лічбу продажаў за першы квартал:

=MINIFS( D2:D13, A2:A13, 1 )

што дае вынік $ 125.000 .

У гэтым прыкладзе Excel Minifs вызначае радкі, дзе значэнне ў слупку A роўна 1, і вяртае мінімальнае значэнне з адпаведных значэнняў у слупку D.

Гэта значыць функцыя знаходзіць мінімальнае са значэнняў $223.000 125.000, $456.000 2 і $3 4 (з вочак DXNUMX, DXNUMX і DXNUMX).

Прыклад 2

Зноў жа, выкарыстоўваючы электронную табліцу дадзеных вышэй, мы таксама можам выкарыстоўваць функцыю Minifs каб знайсці мінімальную лічбу продажаў для «Джэфа» на працягу кварталаў 3 і 4:

=MINIFS( D2:D13, A2:A13, ">2", C2:C13, "Jeff" )

Гэтая формула вяртае вынік $261.000 .

У гэтым прыкладзе Excel Minifs вызначае радкі, у якіх:

• Значэнне ў слупку А больш за 2

E

• Запіс у слупку C роўны «Джэф»

і вяртае мінімальнае з адпаведных значэнняў у слупку D.

Гэта значыць, гэтая формула знаходзіць мінімум значэнняў $310.000 261.000 і $8 11 (з вочак DXNUMX і DXNUMX).

Больш прыкладаў функцыі Excel Minifs, пракансультуйцеся з Сайт Microsoft Office .

Памылка функцыі MINIFS

Калі вы атрымліваеце памылку ад функцыі Excel Minifs, верагодна, адна з наступных прычын:

• #VALORE! -Правярае масівы min_range e criteria_range не ўсе ў камплекце аднолькавай даўжыні.
• #NOME? – Адбываецца, калі вы выкарыстоўваеце старую версію Excel (да 2019 г.), якая не падтрымлівае гэтую функцыю Minifs.

LARGE

Функцыя Excel Large гэта функцыя пошуку, якая вяртае k'е па велічыні значэнне з масіва лікавых значэнняў.

сінтаксіс

= LARGE( array, k )

прадметы

• масіў – масіў лікавых значэнняў для пошуку k'-га найбольшага значэння.
• K – індэкс, г.зн. функцыя вяртае k-е найбольшае значэнне зarray форніта.

Аргумент масіва можа быць прадастаўлены функцыі непасрэдна або ў якасці спасылкі на дыяпазон ячэек, якія змяшчаюць лікавыя значэнні. Калі значэнні ў прадстаўленым дыяпазоне вочак з'яўляюцца тэкставымі значэннямі, гэтыя значэнні ігнаруюцца.

прыклад

Наступная электронная табліца паказвае функцыю Excel Large, які выкарыстоўваецца для атрымання 1-га, 2-га, 3-га, 4-га і 5-га па велічыні значэнняў з набору значэнняў у ячэйках A1-A5.

Некалькі думак пра прыклад электроннай табліцы вышэй:

• У камеры B1, дзе k усталявана ў 1, функцыя Large выконвае тое ж дзеянне, што і Функцыя Excel Макс ;
• У камеры B5, калі k усталявана ў 5 (колькасць значэнняў у прадстаўленым масіве), функцыя Large выконвае тое ж дзеянне, што і Функцыя Excel Min .

Дадатковую інфармацыю і прыклады функцыі Excel Large можна знайсці на Сайт Microsoft Office .

Памылка функцыі LARGE

Калі Excel Large вяртае памылку, верагодна, адно з наступнага:

• #NUM! - Адбываецца, калі:
  • Прадстаўленае значэнне k меншае за 1 або большае за колькасць значэнняў у прадастаўленым масіве
    або
    L 'array пры ўмове пусты.
• #VALUE! – Адбываецца, калі k не з'яўляецца лікам.

Аднак пры вылічэнні функцыі LARGE могуць узнікнуць памылкі, нават калі дадзенае значэнне k знаходзіцца паміж 1 і колькасцю значэнняў у пастаўленым масіве. Магчымай прычынай можа быць тое, што тэкставыя значэнні, у тым ліку тэкставыя прадстаўленні лікаў у прадастаўленым масіве, ігнаруюцца функцыяй Large. Такім чынам, гэтая праблема можа ўзнікнуць, калі значэнні ў прадстаўленым масіве з'яўляюцца тэкставымі ўяўленнямі лікаў, а не фактычнымі лікавымі значэннямі.

Рашэнне можа быць дасягнута шляхам пераўтварэння ўсіх значэнняў масіва ў лікавыя значэнні. 

SMALL

Функцыя Excel Small - гэта функцыя пошуку, якая вяртае k-е найменшае значэнне з масіва лікавых значэнняў.

сінтаксіс

= SMALL( array, k )

прадметы

• array – Масіў лікавых значэнняў для пошуку k'-га найбольшага значэння.
• K – індэкс, г.зн. функцыя вяртае k-е найбольшае значэнне зarray форніта.

Аргумент масіва можа быць прадастаўлены функцыі непасрэдна або ў якасці спасылкі на дыяпазон ячэек, якія змяшчаюць лікавыя значэнні. Калі значэнні ў прадстаўленым дыяпазоне вочак з'яўляюцца тэкставымі значэннямі, гэтыя значэнні ігнаруюцца.

прыклад

Наступная электронная табліца паказвае функцыю Excel Small, які выкарыстоўваецца для атрымання 1-га, 2-га, 3-га, 4-га і 5-га найменшых значэнняў з набору значэнняў у ячэйках A1-A5.

У прыкладзе неабходна ўлічваць, што ::

• У ячэйцы B1, дзе k усталявана ў 1, функцыя Small выконвае тое ж дзеянне, што і Функцыя Excel Min ;
• У вочку B5, калі k усталяваны ў 5 (колькасць значэнняў уarray пры ўмове ), функцыя Small выконвае тое ж дзеянне, што і Функцыя Max Excel .

Больш падрабязная інфармацыя і прыклады функцыі Excel Small прадастаўляюцца на Сайт Microsoft Office .

Памылка функцыі SMALL

Калі Excel SMALL вяртае памылку, верагодна, адно з наступнага:

• #NUM! - Адбываецца, калі:
  • Прадстаўленае значэнне k меншае за 1 або большае за колькасць значэнняў у прадастаўленым масіве
    або
    Прадстаўлены масіў пусты.
• #VALUE! – Адбываецца, калі k не з'яўляецца лікам.

Аднак пры вылічэнні функцыі могуць узнікнуць памылкі LARGE нават калі дадзенае значэнне k знаходзіцца паміж 1 і колькасцю значэнняў уarray пры ўмове. Магчымай прычынай можа быць тое, што тэкставыя значэнні, у тым ліку тэкставыя прадстаўленні лікаў у межахarray пры ўмове, яны ігнаруюцца функцыяй Large. Такім чынам, гэтая праблема можа ўзнікнуць, калі значэнні ўarray прадстаўлены тэкставыя прадстаўленні лікаў, а не сапраўдныя лікавыя значэнні.

Рашэнне можа быць дасягнута шляхам пераўтварэння ўсіх значэнняўarray у лікавых значэннях. 

Падобныя чытанні

• Статыстычныя функцыі Excel: падручнік з прыкладамі, частка першая
• Дыяграмы Excel, што гэта такое, як стварыць дыяграму і як выбраць аптымальную дыяграму
• JSP Request, усе метады няяўнага аб'екта Request
• Кіраванне праектамі ў вопыце навучання
• Зводныя табліцы: што гэта такое, як стварыць у Excel і Google. Падручнік з прыкладамі

Ercole Palmeri