Excel SUMIFS: Тек қана жиынтық мәндер Бірнеше критерийге жауап беру

SUMIFS функциясы SUMIF функциясындағы сияқты бірден бір емес, 2-ден 127-ке дейін критерийлерді көрсетуге мүмкіндік бере отырып, SUMIF функциясының пайдалылығын кеңейтеді.

Әдетте, SUMIFS жазбалар деп аталатын деректер қатарымен жұмыс істейді. Жазбада әрбір ұяшықтағы немесе өрістегі деректердің барлығы, мысалы, компанияның атын, мекен-жайын және телефон нөмірін байланыстырады.

SUMIFS жазбадағы екі немесе одан да көп өрістегі нақты өлшемдерді іздейді және көрсетілген әрбір өріс үшін сәйкестік табылған жағдайда ғана ол жазылған деректер үшін деректер болып табылады.

01-тен 10-ке дейін

SUMIFS функциясы қалай жұмыс істейді

SUMIF қадамдық оқулықта жылына 250-ден астам тапсырысты сатқан сату агенттерінің бірыңғай критерийіне сәйкес келдік.

Осы оқулықта біз SUMIFS-ді пайдалану арқылы екі шартты белгілей аламыз - өткен жылы 275 сатылымнан азы бар Шығыс сату аймағындағы сату агенттері.

SUMIFS үшін қосымша Criteria_range және Criteria аргументтерін көрсету арқылы екіден көп шартты орнату мүмкін.

Төмендегі нұсқаулықтағы қадамдардан кейін жоғарыдағы суретте көрсетілген SUMIFS функциясын құру және пайдалану арқылы жүреді.

Оқулық тақырыптары

• Тәжірибелік деректерді енгізу
• SUMIFS функциясының синтаксисі
• SUMIFS функциясын іске қосу
• Sum_range аргументі
• Criteria_range1 аргументі
• Criteria1 аргументі
• Criteria_range2 аргументі
• Criteria2 дәлелі

02-тен 10-ға дейін

Тәжірибелік деректерді енгізу

Excel бағдарламасындағы SUMIFS функциясын пайдаланудың алғашқы қадамы деректерді енгізу болып табылады.

Деректерді жоғарыдағы суретте көрсетілгендей Excel жұмыс парағының D1-ден F11 ұяшықтарына енгізіңіз.

SUMIFS функциясы және іздеу критерийлері (Шығыс сату аймағынан 275 тапсырыс және сатушы агенттері аз) мәліметтердің 12-жолына қосылады.

Оқулық нұсқаулары жұмыс парағындағы пішімдеу қадамдарын қамтымайды.

Бұл оқулықты орындауға кедергі болмайды. Жұмыс парағы көрсетілген мысалдан өзгеше көрінеді, бірақ SUMIFS функциясы сізге бірдей нәтижелер береді.

03 10

SUMIFS функциясының синтаксисі

Excel бағдарламасында функцияның синтаксисі функцияның орналасуына қатысты және функцияның атауын, жақшалар мен аргументтерді қамтиды .

SUMIFS функциясының синтаксисі:

= SUMIFS (Sum_range, Criteria_range1, Criteria1, Criteria_range2, Criteria2, ...)

Ескерту: Функцияда 127 дейін Criteria_range / Criteria жұптары көрсетілуі мүмкін.

SUMIFS функциясының дәлелдері

Функцияның дәлелдері функцияға қандай шарттар сыналып жатқандығын және сол шарттар орындалатын кезде қандай деректердің жинақталу керектігін айтады.

Бұл функциядағы барлық аргументтер қажет.

Sum_range - бұл ұяшықтар ауқымындағы барлық сәйкестік критерийлері мен сәйкес Criteria_range аргументтері арасында сәйкестік табылғанда жинақталады.

Criteria_range - функция тиісті ұяшықтар дәлеліне сәйкестікті іздеу үшін ұяшықтар тобы.

Критерийлер - бұл мән тиісті Criteria_range деректерімен салыстырылады. Осы аргумент үшін нақты деректер немесе деректерге ұялы сілтеме енгізілуі мүмкін.

04 of 10

SUMIFS функциясын іске қосу

SUMIFS функциясын жұмыс парағындағы ұяшыққа теру мүмкін болса да, көптеген адамдар функцияны енгізу үшін функцияның тілқатысу терезесін пайдалануды жеңілдетеді.

Тәжірибелік қадамдар

1. Белсенді ұяшықты жасау үшін F12 ұяшығын басыңыз. Бұл жерде біз SUMIFS функциясына кіреміз.
2. Формулалар қойындысын нұқыңыз.
3. Функция ашылмалы тізімін ашу үшін таспаның Math & Trig белгішесін басыңыз.
4. SUMIFS функциясының тілқатысу терезесін шығару үшін тізімдегі SUMIFS түймесін басыңыз.

Тілқатысу терезесіндегі бос жолға енгізетін деректер SUMIFS функциясының дәлелдерін құрайды.

Бұл аргументтер функцияға қандай шарттармен сыналып жатқанын және сол шарттар орындалатын кезде қандай деректердің жинақталатындығын айқындайды.

05 of 10

Sum_range дәлелін енгізу

Sum_range аргументі қосу керек деректерге арналған ұялы сілтемелерді қамтиды.

Функция жазбаға арналған барлық белгіленген критерийлер мен Criteria_range аргументтері арасында сәйкестік табылған кезде, осы жазба үшін Sum_range өрісі жалпыға қосылады.

Осы оқулықта Sum_range дәлелінің деректері Жалпы сату бағанында орналасқан.

Тәжірибелік қадамдар

1. Тілқатысу терезесіндегі Sum_range жолын нұқыңыз.
2. Осы ұяшық сілтемелерін Sum_range жолына қосу үшін жұмыс парағында F3-F9 ұяшықтарын бөлектеңіз.

06-дан 10-ке дейін

Criteria_range1 дәлелін енгізу

Осы оқулықта әрбір деректер жазбада екі өлшемге сәйкес келуге тырысамыз:

1. Шығыс сауда аймағынан сату агенттері.
2. Осы жылы 275 сатылымнан кем сатқан сату агенттері.

Criteria_range1 дәлелі SUMIFS бірінші критерийлерге - Шығыс сату аймағына сәйкестендіру кезінде іздеуге болатын ұяшықтар ауқымын көрсетеді.

Тәжірибелік қадамдар

1. Тілқатысу терезесінде Criteria_range1 жолын басыңыз.
2. Осы ұялы сілтемелерді функция арқылы іздеуге болатын ауқымға енгізу үшін жұмыс парағындағы D3-D9 ұяшықтарын бөлектеңіз.

07 ішіндегі 10

Criteria1 аргументін енгізу

Осы оқулықта, D3: D9 диапазонындағы деректер шығысқа тең болғанда, біз сәйкестендіруге бірінші критерий болып табылады.

Шындық сөзі сияқты нақты деректер - бұл дәлел үшін диалогтық терезеге енгізілсе де, деректерді жұмыс парағындағы ұяшыққа қосу және одан кейін осы ұяшық сілтемесін диалогтық терезеге енгізу керек.

Тәжірибелік қадамдар

1. Тілқатысу терезесіндегі Criteria1 жолын басыңыз.
2. Бұл ұяшық сілтемесін енгізу үшін D12 ұяшығын басыңыз. Функция осы өлшемдерге сәйкес келетін деректер үшін алдыңғы қадамда таңдалған ауқымды іздейді.
3. Іздестірудің соңғы кезеңінде іздеу термині (Шығыс) D12 ұяшығына қосылады.

Ұяшық сілтемелері SUMIFS көпмәндігін қалай арттырады

Егер D12 сияқты ұяшық сілтемесі критерийлер аргументі ретінде енгізілсе, SUMIFS функциясы жұмыс парағындағы сол ұяшыққа кез келген деректерді енгізген кезде сәйкестіктерді іздейді.

Сондықтан Шығыс аймағы үшін сату көлемін анықтағаннан кейін, D12 ұяшығындағы Шығысты солтүстікке немесе батысқа ауыстыру арқылы басқа сату аймағына ұқсас деректерді табу оңай болады. Функция автоматты түрде жаңартылып, жаңа нәтижені көрсетеді.

08-тен 10-ға дейін

Criteria_range2 дәлелін енгізу

Бұрын айтылғандай, осы оқулықта әрбір деректер жазбада екі өлшемге сәйкес келуге тырысамыз:

1. Шығыс сауда аймағынан сату агенттері.
2. Осы жылы 275 сатылымнан кем сатқан сату агенттері.

Criteria_range2 дәлелі SUMIFS екінші критерийлерге сəйкес келетін кезде іздеуге болатын ұяшықтар ауқымын көрсетеді, олар осы жылы 275-тен астам тапсырысты сатқан сатушылар.

Тәжірибелік қадамдар

1. Тілқатысу терезесінде Criteria_range2 жолын басыңыз.
2. Жұмыс парағындағы E3-E9 ұяшықтарын осы ұяшық сілтемелерін функция арқылы ізделетін екінші ауқым ретінде енгізу үшін енгізіңіз.

09-дан 10-ке дейін

Criteria2 дәлелін енгізу

Осы оқулықта E3: E9 ауқымындағы деректер 275-тен астам сату тапсырмалары болған жағдайда, біз сәйкестікті іздейтін екінші критерий болып табылады.

Criteria1 аргументінде болғандай , Criteria2 орналасуына ұяшық сілтемесін деректердің өзі емес, тілқатысу терезесіне енгіземіз.

Тәжірибелік қадамдар

1. Тілқатысу терезесіндегі Criteria2 жолын басыңыз.
2. Бұл ұяшық сілтемесін енгізу үшін E12 ұяшығын басыңыз. Функция осы өлшемдерге сәйкес келетін деректер үшін алдыңғы қадамда таңдалған ауқымды іздейді.
3. SUMIFS функциясын аяқтау үшін OK түймешігін басыңыз және тілқатысу терезесін жабыңыз.
4. F12 ұяшығында нөлге (0) жауап пайда болады - біз функцияға кірген ұяшық - деректерді Criteria1 және Criteria2 өрістеріне (C12 және D12) әлі қосқан жоқпыз. Біз әзірге дейін, функцияның қосылуы үшін ештеңе жоқ, сондықтан жалпы нөлге тең болады.
5. Іздестіру критерийлері оқулықтың келесі қадамына қосылады.

10-дан 10-ға дейін

Іздеу критерийлерін қосу және жаттығуды аяқтау

Оқу құралындағы соңғы қадам - ​​деректерді Criteria аргументтері ретінде анықталған жұмыс парағындағы ұяшықтарға қосу.

Тәжірибелік қадамдар

Бұл мысалдағы анықтама үшін жоғарыдағы суретті қараңыз.

1. D12 ұяшығында Шығыс түрін енгізіп, пернетақтадағы Enter пернесін басыңыз.
2. E12 ұяшығында <275 түрінде және пернетақтадағы Enter пернесін басыңыз («<» - Excel бағдарламасынан аз ).
3. Жауап $ 119,719.00 F12 ұяшығында пайда болуы керек.
4. Екі және үш жолдардағы екі жазба тек екі критерийге сәйкес келеді, сондықтан осы екі жазба үшін сату жиынтығы функциямен жинақталады.
5. $ 49,017 және $ 70,702 сомасы 119 719 АҚШ долларын құрайды.
6. F12 ұяшығын басқанда, толық функция
   = SUMIFS (F3: F9, D3: D9, D12, E3: E9, E12) жұмыс парағының үстіндегі формула жолында пайда болады.