SUMMESLE функциясы, сондай-ақ SUMMESLES екі өлшем бойынша

»11 маусым 2011 ж Дмитрий 243582 рет қаралды

Бөлімдердің атаулары (немесе шоттар немесе басқа нәрсе) қатардағы жолдарда тізімделетін кестені елестетіңіз.

Жасушаларды критерий бойынша есептеу
Әрбір бөлім үшін жалпы соманы есептеу қажет. Көптеген адамдар сүзгі жасап, жасушалардағы қаламдармен жазады.
Бұл бір ғана функциямен оңай және оңай орындалуы мүмкін - SUMMESLI .
SUMMESLES (SUMIF) - берілген шартты қанағаттандыратын ұяшықтарды толтырады (тек бір шарт көрсетілуі мүмкін). Бұл функция кестелерге бағандарға бөлінгенде (ай сайын, ай сайын, үш баған - кіріс | шығыс | айырмашылық) бөлінгенде де пайдаланылуы мүмкін және сіз барлық кезеңдер үшін жалпы соманы тек кіріс, шығыс және айырмашылық арқылы есептеуіңіз керек.

SUMMESLI: Range , Criterion , Range_Summing үшін барлығы үш дәлел бар.
= SUMMESLE (A1: A20000; A1; B1: B20000)
= SUMIF (A1: A20000, A1, B1: B20000)

• Ауқымы (A1: A20000) - өлшемдерді қамтитын ауқымды көрсетеді. Яғни Criterion дәлелімен көрсетілген мәнді іздеуге арналған баған.
• Критерий (A1) - диапазонда болуы керек мән (мәтін немесе сандық, сондай-ақ күн). «*» Және «?» Қойылмалы таңбалары болуы мүмкін. Яғни «массасы» * деген сөз критерийін көрсетіп, «массасы» деген сөздің мәндерін келтіріңіз. Сонымен қатар, «масса» сөзі мәтіннің кез келген жерінде болуы мүмкін немесе ұяшықтағы бұл сөздің біреуі ғана болуы мүмкін. Және «массасы *» көрсетіле отырып, «массадан» басталатын барлық мәндер жинақталады. «?» - бір ғана таңбаны ауыстырады, яғни, «mas? a» көрсетіп, сіз «масса» және «маска» мәнін және т.б.
  Егер критерий ұяшықта жазылған болса және сіз әлі де қойылмалы таңбаларды пайдалануыңыз қажет болса, қажетті ұяшықты қосу арқылы осы ұяшыққа сілтеме жасай аласыз. Мысалы, «жалпы» сөзі бар мәндерді қорытындылау керек. «Толық» сөзі А1 ұяшығында жазылған, ал A бағанында «жалпы» деген сөз бар, «маусымға арналған», «шілденің қорытындысы», «наурызға арналған» сөздері бар түрлі жазу мәндері болуы мүмкін. Формула келесідей болуы керек:
  = SUMMERS (A1: A20000; «*» & A1 & «*»; B1: B20000)
  «*» & A1 & «*» - & белгісі (ampersand) бірнеше мәндерді бір біріктіреді. Яғни нәтиже «* нәтиже» болады.
  Формулалардың жұмыс істеу принципін жақсы түсіну үшін Формуланы есептеу құралын пайдалану жақсы: Формулаларды есептеу үшін қадамдарды қалай қарау керек
  Барлық мәтіндік критерийлер мен критерийлер логикалық және математикалық белгілермен екі қос тырнақшаға (= SUMMESLI (A1: A20000; «total»; B1: B20000) қосылуы керек). Егер критерий сан болса, онда тырнақшалар талап етілмейді. Егер сіз сұрақ белгісін немесе жұлдызшаны тікелей іздегіңіз келсе, оны алдына тильды (~) қою керек.
  Тильда және оның ерекшеліктері туралы осы мақалада білуге ​​болады: Жұлдызшаны қалай ауыстыру / жою / табу керек?
• Sum_Range (B1: B20000) (қосымша дәлел) - жинақталатын сомалардың немесе сандық мәндердің ауқымын анықтайды.

Бұл қалай жұмыс істейді: функция аралықты Criterion дәлелімен көрсетілген мән үшін іздейді және сәйкестік табылғанда, Range_Amount дәлелімен көрсетілген деректерді қосады. Яғни егер А бағанында бөлім атауы бар болса және B бағанындағы сома болса, Даму департаменті критерий ретінде белгіленсе, бағаны B бағасының барлық мәндеріне әкеледі, ал оның орнына Даму департаменті А бағанында табылған. Шын мәнінде, SumArrangement Range дәлелінің өлшемімен бірдей болмауы мүмкін және бұл функцияның қатесін тудырмайды. Алайда, жиынтығы үшін ұяшықтарды анықтаған кезде, Range_Amount аргументінің жоғарғы сол жақ ұясы жинақтау үшін бастапқы ұяшық ретінде пайдаланылады, содан кейін ауқым мен пішінге сәйкес ұяшықтар Аралық дәлелге жиналады.

Кейбір мүмкіндіктер
Функцияның соңғы аргументі (Sum_And_Band: B1: B20000) міндетті емес. Бұл дегеніміз, оны көрсету мүмкін емес. Егер сіз оны көрсетпесеңіз, функция Аралық дәлелмен анықталған мәндерді қосады. Бұл не үшін. Мысалы, нөлден жоғары сандар ғана болуы керек. Соманың A бағанында. Сонда функция келесідей болады:
= SUMMERS (A1: A20000; «> 0»)

Не қарастыру керек: range_summing және ауқым жолдар санына тең болуы керек. Әйтпесе, сіз дұрыс емес нәтижеге қол жеткізе аласыз. Оңтайлы түрде, егер ол ұсынған формулаларда көрінсе , жиынтықтар ауқымы мен ауқымы бір жолдан басталып, бірдей жолдарға ие: A1: A20000; B1: B20000

Екі немесе одан көп өлшемдер бойынша жиынтықтау
Бірақ 2 және одан да көп жинау критерийлері болған кезде не істеу керек? Сіз бір бөлімге тиесілі соманы ғана және белгілі бір күнге ғана қорытындылаңыз. 2007 және одан жоғары Office нұсқаларының бақытты иелері SUMMESLIMN функциясын пайдалана алады:
= SUMMESLIMN ($ C $ 2: $ C $ 50; $ A $ 2: $ A $ 50; $ I $ 3; $ B $ 2: $ B $ 50 $ H8)
$ C $ 2: $ C $ 50 - range_summing. Бірінші аргумент жиналған сомаларды қамтитын ұяшықтар ауқымын көрсетеді.
$ A $ 2: $ A $ 50, $ B $ 2: $ B $ 50 - Range_Criteria. Матчты критерий бойынша іздеу керек ұяшықтардың ауқымын анықтайды.
$ I $ 3, $ H8 - критерий. Мұнда, SUMMESLI сияқты, қойылмалы таңбалар * және ? Рұқсат етілген . және олар бірдей жұмыс істейді.

Аргументтердің ерекшеліктері: біріншіден, критерийлер диапазоны (олар нөмірленген) көрсетіледі, содан кейін мән (критерий) тікелей осы нүктеде табылуы тиіс - $ A $ 2: $ A $ 50, $ I $ 3. Және ештеңе жоқ. Сіз алдымен барлық ауқымдарды көрсетпеуге тырыспаңыз, содан кейін олар үшін критерийлер - функция қате жібереді, не қажет болса, қорытындылайды.

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

��йткені SUMMESLIMN 2007 нұсқасынан тек Excel нұсқаларында пайда болды, онда мұндай жағдайларда бұрынғы нұсқалардың бақытсыз пайдаланушылары қалай болуы мүмкін? Өте қарапайым: басқа функцияны пайдаланыңыз - SUMPRODUCT. Мен дәлелдерді боямаймын, өйткені Олардың көпшілігі бар және олардың барлығы құндылық массивтері болып табылады. Бұл функция аргументтермен көрсетілген массивтерді көбейтеді. Бұл функцияны бірнеше шарт бойынша деректерді жинақтау үшін пайдаланудың жалпы принципін сипаттауға тырысамын.
Бірнеше критерий бойынша жиынтықты мәселені шешу үшін функция келесідей болады:
= SUMPRODUCT ($ A $ 2: $ A $ 50 = $ I $ 3) * ($ B $ 2: $ B $ 50 = H5); $ C $ 2: $ C $ 50)
$ A $ 2: $ A $ 50 - күндер ауқымы. $ I $ 3 - бұл деректерді қосу қажет критерийдің күні.
$ B $ 2: $ B $ 50 - бөлімдердің аты. H5 - бөлімнің атауы, ол туралы мәліметтер жинақталуы керек.
$ C $ 2: $ C $ 50 - сомалармен диапазон.

Логиканы талдаймыз, өйткені Көптеген адамдарға бұл функцияны қарау арқылы ғана түсініксіз болады. Егер бұл қолданбаның анықтамасында болмаса ғана. Үлкен оқылу үшін ауқымның өлшемін азайтыңыз:
= SUMPRODUCT ($ A $ 2: $ A $ 5 = $ I $ 3) * ($ B $ 2: $ B $ 5 = H5); $ C $ 2: $ C $ 5)
Осылайша, ($ A $ 2: $ A $ 5 = $ I $ 3) және ($ B $ 2: $ B $ 5 = H5) өрнегі логикалық болып табылады және логикалық FALSE және TRUE мәндерін қайтарады. TRUE $ A $ ауқымындағы ұяшық болса: $ A $ 5 ұяшығының мәніне тең $ I $ 3 және $ B $ 2 ауқымының ұяшығы: $ B $ 5 H5 ұяшығының мәніне тең. Яғни бізде мыналар бар:
= SUMPRODUCT ({FALSE; TRUE; TRUE; FALSE} * {FALSE; FALSE; TRUE; FALSE; $ C $ 2: $ C $ 50)
Көріп отырғаныңыздай, бірінші массивде шарт үшін екі сәйкестік, ал екіншісінде болады. Сонымен қатар, бұл екі массив көбейтіледі (көбейту белгісі (*) бұл үшін жауап береді). Көбейту кезінде FALSE және TRUE мәндерінің тиісінше 0 және 1 сандық тұрақты мәндеріне ({0; 1; 1; 0} * {0; 0; 1; 0}) қате түрлендіруі орын алады. Өздеріңіз білетіндей, нөлге көбейтілгенде, нөлге тең болады. Нәтиже - бірыңғай жиым:
= SUMPRODUCT ({0; 0; 1; 0}; $ C $ 2: $ C $ 50)
Содан кейін {0; 0; 1; 0} массасы $ C $ 2 ауқымындағы сандардың массивіне көбейтіледі: $ C $ 50:
= SUMPRODUCT ({0; 0; 1; 0}; {10; 20; 30; 40})
Нәтижесінде біз 30-ын алдық. Не қажет болса - өлшемге сай келетін соманы ғана аламыз. Егер критерийді қанағаттандыратын бірнеше сома болса, онда олар қорытындыланады.

SUMMIR артықшылығы
Аргументтерде көбейту белгісінің орнына қосу белгісі болса:
($ A $ 2: $ A $ 5 = $ I $ 3) + ($ B $ 2: $ B $ 5 = H5)
онда шарттарды НЕМЕСЕ принципіне сәйкес салыстыруға болады: яғни, $ A $ 2: $ A $ 5 мәнін ұяшыққа $ I $ 3 немесе $ B $ 2 ауқымының ұяшығына тең: $ B $ 5 ұяшық мәні H5-ге тең болса, жалпы сомалар қорытындыланады.
Бұл SUMMPRODUCT артықшылығы SUMMESLIMN. SUMMESLIMN OR қағидасына сәйкес мәндерді қорытындылай алмайды, тек ЖӘНЕ принцип бойынша (барлық шарттар орындалуы керек).

Кемшіліктері
SUMPRODUCT қойылмалы * және? Дәлірек қолдануға болады, бірақ олар арнайы таңбалар емес, жұлдызша және сұрақ белгісі ретінде қабылданады. Менің ойымша, бұл маңызды кемшілігі. Бұл айналып өтуге болатын болса да, SUMPRODUCT ішіндегі басқа функцияларды пайдаланамын - бұл функция қойылмалы таңбаларды қолдануға болар еді.

Осы мысалда жоғарыда жазылғандарды жақсы түсіну үшін функциялардың бірнеше мысалын таба аласыз.

Мысалды жүктеп алыңыз

Бірнеше өлшем бойынша сома (41.5 KiB, 10.477 Қотару)

Сондай-ақ қараңыз:
Бояу түсі бойынша ұяшықтарды жинақтау
Қаріп түсі бойынша ұяшықтардың жиынтығы
Ұяшық форматы арқылы ұяшықтарды жинақтау
Бояу түсі бойынша ұяшықтардың мөлшерін есептеңіз
Қаріп түсі бойынша ұяшықтардың мөлшерін есептеңіз
Бірнеше парақтан деректерді қалай қосуға болады, соның ішінде шарт бойынша

Бейне сабақтар

: «Textpositionmarginstatic»: 0, «textpositiondynamic»: «bottomleft», «textpositionmarginleft»: 24, «textpositionmarginleft»: « «textpositionmarginint»: 24, «textpositionmarginbottom»: 24, «texteffect»: «slide», «texteffecteasing»: «easeOutCubic», «texteffectduration»: 600, «texteffectslidedirection»: «left», «texteffectslidedistance» : 30, «texteffectdelay»: 500, «texteffectseparate»: жалған, «texteffect1»: «слайд», «texteffectslidedirection1»: «оң», «texteffectslidedistance1»: 120, «texteffecteasing1»: «easeOutCubic», «texteffectduration1»: 600 «texteffectdelay1»: 1000, «texteffect2»: «slide», «texteffectslidedirection2»: «right», «texteffectslidedistance2»: 120, «texteffecteasing2»: «easeOutCubic», «texteffectduration2»: 600, «texteffectdelay2»: 1500, Мәтіндік айқындама: 12px; text-align: left; «,» textbgcss «:» display: block; position: absolute; top: 0px; left: 0px; width: 100%; height: 100% ; background-color: # 333333; ашықтық: 0.6; сүзгі: a lpha (ашықтық = 60); «,» titlecss «:» дисплей: блок; позиция: салыстырмалы; қаріп: bold 14px \ «Lucida Sans Unicode \», \ «Lucida Grande \», sans-serif, Arial; Түсі: #fff; «,» descriptioncss «:» display: block; позиция: салыстырмалы; шрифт: 12px \ «Lucida Sans Unicode \», \ «Lucida Grande \», sans-serif, Arial; Түсі: #fff; margin-top: 8px; «,» buttoncss «:» дисплей: блок; позиция: салыстырмалы; «», «жауап беру»: 640, «titlecssresponsive»: «font-size: 12px;», «descriptioncssresponsive»: «display: none: important;», «buttoncssresponsive»: «», «addgooglefonts»: жалған, «googlefonts»: «», «textleftrightpercentforstatic»: 40}}

Тегтер бойынша іздеу

Барыңыз Кіру алма сағаты Multex Outlook Power Query және Power BI VBA редакторында жұмыс істейді VBA кодын басқару Тегін қондырмалар Күні мен уақыты Диаграммалар мен графиктер Ескертулер Деректерді қорғау Интернет Суреттер мен нысандар Кітаптар мен кітаптар Макростар және VBA Қосымшалар Теңшеу Басып шығару Деректерді іздеу Құпиялылық саясаты Пошта Бағдарламалар Қолданбалармен жұмыс істеу Файлдармен жұмыс істеу Қолданбаны әзірлеу Жиынтық кестелер Тізімдер Тренингтер мен вебинарлар Қаржылық Пішімдеу Формулалар мен функциялар Excel функциялары VBA функциялары Ұяшықтар мен диапазондар MulTEx акциялары деректерді талдау Excel бағдарламасындағы қателер мен қателіктер сілтемелер