එක්සෙල් හි එක් කොටුවක් හරහා එකතු කරන්න. එක්සෙල්. කොන්දේසිගත හැඩතල ගැන්වීමේ නිර්ණායක සපුරාලන සෛල ගණන් කිරීම සහ එකතු කිරීම

විකුණුම් නියෝජිතයින්ගේ විකුණුම් පිළිබඳ පහත වාර්තාව ඔබ සතුව ඇතැයි උපකල්පනය කරමු:

එයින් ඔබ කොපමණ මුදලක් සොයා ගත යුතුය පැන්සල්විකුණුම් නියෝජිතයා විසින් විකුණනු ලැබේ ඉවානොව්තුල ජනවාරි.

ගැටලුව: බහු නිර්ණායක මගින් දත්ත සාරාංශ කරන්නේ කෙසේද ??

තීරණය: ක්රමය 1:

BDSUMM(A1:G16;F1;I1:K2)

ඉංග්‍රීසි අනුවාදයෙන්:

DSUM(A1:G16,F1,I1:K2)

එය ක්‍රියා කරන ආකාරය:

අපි සඳහන් කළ දත්ත සමුදායෙන් A1:G16කාර්යය BDSUMMතීරු දත්ත ලබාගෙන එකතු කරයි ප්රමාණය(තර්කය" ක්ෂේත්රය" = F1) සෛල තුළ ලබා දී ඇති පරිදි I1:K2 (විකුණුම්කරු = ඉවානොව්; නිෂ්පාදන = පැන්සල්;මාසය = ජනවාරි) නිර්ණායක.

අවාසි: නිර්ණායක ලැයිස්තුව පත්රයේ තිබිය යුතුය.

සටහන්: සම්පිණ්ඩන නිර්ණායක ගණන RAM මගින් සීමා වේ.

අයදුම් කරන ප්‍රදේශය: Excel හි ඕනෑම අනුවාදයක්

ක්රමය 2:

SUMPRODUCT((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2)*F2:F16)

ඉංග්‍රීසි අනුවාදයෙන්:

SUMPRODUCT((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2)*F2:F16)

එය ක්‍රියා කරන ආකාරය:

SUMPRODUCT ශ්‍රිතය Excel මතකයේ තෝරාගත් නිර්ණායක අනුව සත්‍ය සහ අසත්‍ය අගයන් අරා ජනනය කරයි.

ගණනය කිරීම් සිදු කළේ පත්‍රයේ සෛල තුළ නම් (පැහැදිලි භාවය සඳහා, ගණනය කිරීම් සිදු වන්නේ පත්‍රයේ මිස මතකයේ නොව) සූත්‍රයේ සම්පූර්ණ ක්‍රියාකාරිත්වය මම නිරූපණය කරමි, එවිට අරා මේ ආකාරයෙන් පෙනෙනු ඇත:

උදාහරණයක් ලෙස, නම්, බව පැහැදිලිය. D2=පැන්සල්, එවිට අගය සත්‍ය වනු ඇත, සහ නම් D3=ෆෝල්ඩර, පසුව FALSE (අපගේ උදාහරණයේ නිෂ්පාදන තේරීමේ නිර්ණායකය අගය වන බැවින් පැන්සල්).

TRUE සෑම විටම 1 ට සමාන වන අතර FALSE සෑම විටම 0 ට සමාන වන බව දැනගෙන, අපි අංක 0 සහ 1 සමඟ මෙන් අරා සමඟ වැඩ කරන්නෙමු.
අරා වල ලබාගත් අගයන් අනුක්‍රමිකව ගුණ කිරීමෙන් අපට ශුන්‍ය සහ ඒවා එක අරාවක් ලැබේ. තේරීම් නිර්ණායක තුනම සපුරා ඇති තැන, ( ඉවානොව්, පැන්සල්, ජනවාරි) i.e. සියලුම කොන්දේසි සත්‍ය විය, අපට 1 ලැබේ (1*1*1 = 1), නමුත් අවම වශයෙන් එක් කොන්දේසියක්වත් සපුරා නොමැති නම්, අපට 0 ලැබේ (1*1*0 = 0 ; 1*0*1 = 0 ; 0* 1* 1 = 0).

දැන් ඉතිරිව ඇත්තේ අපට ප්‍රතිඵලයක් ලෙස සාරාංශ කිරීමට අවශ්‍ය දත්ත අඩංගු අරාවෙන් ලැබෙන අරාව ගුණ කිරීම පමණි (පරාසය F2:F16) සහ, ඇත්ත වශයෙන්ම, 0 න් ගුණ නොකළ දේ සාරාංශ කරන්න.

දැන් සූත්‍රයේ ආධාරයෙන් සහ පත්‍රයේ පියවරෙන් පියවර ගණනය කිරීම (රතු පැහැයෙන් උද්දීපනය කර ඇති) සමඟ ලබාගත් අරාව සසඳන්න.

මම හිතන්නේ හැම දෙයක්ම පැහැදිලියි :)

අවාසි: SUMPRODUCT - "බර" අරාව සූත්‍රය. විශාල දත්ත පරාසයන් මත ගණනය කිරීමේදී, නැවත ගණනය කිරීමේ කාලය සැලකිය යුතු ලෙස වැඩි වේ.

සටහන්

අයදුම් කරන ප්‍රදේශය: Excel හි ඕනෑම අනුවාදයක්

ක්රමය 3: Array Formula

එකතුව(IF((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2),F2:F16))

ඉංග්‍රීසි අනුවාදයෙන්:

එකතුව(IF((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2),F2:F16))

එය ක්‍රියා කරන ආකාරය:ක්‍රමය අංක 2 වගේ. ඇත්තේ වෙනස්කම් දෙකක් පමණි - ලබා දුන් සූත්රයඑබීමෙන් ඇතුල් විය Ctrl+Shift+Enterනිකම් එබීම වෙනුවට ඇතුල් කරන්නසහ 0 සහ 1 හි අරාව සාරාංශ පරාසයෙන් ගුණ නොකෙරේ, නමුත් IF ශ්‍රිතය භාවිතයෙන් තෝරා ගනු ලැබේ.

අවාසි: විශාල දත්ත පරාසයන් මත ගණනය කිරීමේදී අරා සූත්‍ර සැලකිය යුතු ලෙස නැවත ගණනය කිරීමේ කාලය වැඩි කරයි.

සටහන්: සැකසූ අරා ගණන 255 ට සීමා වේ.

අයදුම් කරන ප්‍රදේශය: Excel හි ඕනෑම අනුවාදයක්

ක්රමය 4:

SUMIFS(F2:F16,B2:B16,I2,D2:D16,J2,A2:A16,K2)

මීට පෙර, සොයා ගැනීමට අභිරුචි ශ්‍රිතයක් භාවිතා කරන ආකාරය මම විස්තර කළෙමි. අවාසනාවකට, සෛල වර්ණ ගන්වා ඇත්නම් මෙම කාර්යය ක්රියා නොකරයි කොන්දේසි සහිත හැඩතල ගැන්වීම. මම කාර්යය "අවසන් කිරීමට" පොරොන්දු විය. නමුත් එම සටහන පළ වී ගෙවී ගිය වසර දෙක තුළ මට තනිවම හෝ අන්තර්ජාලයේ තොරතුරු ආධාරයෙන් දිරවන කේතයක් ලිවීමට නොහැකි විය ... ( 2017 මාර්තු 29 යාවත්කාලීන කරන්නතවත් අවුරුදු පහකට පසුව, මම තවමත් කේතය ලිවීමට සමත් විය; සටහනේ අවසාන කොටස බලන්න). ඒ වගේම මෑතකදී මට D. Holy, R. Holy "Excel 2007. Tricks" විසින් රචිත පොතේ අඩංගු අදහසක් හමු විය, එය ඔබට කේතයකින් තොරව කිරීමට ඉඩ සලසයි.

A1: A100 පරාසයේ තබා ඇති 1 සිට 100 දක්වා සංඛ්‍යා ලැයිස්තුවක් තිබිය යුතුය (රූපය 1; Excel ගොනුවේ "SUMIS" පත්‍රයද බලන්න). පරාසය තුළ 10 ට වැඩි සහ 20 ට අඩු හෝ සමාන සංඛ්‍යා අඩංගු කොටු සලකුණු කරන කොන්දේසි සහිත හැඩතල ගැන්වීම් ඇත.

සහල්. 1. සංඛ්යා පරාසය; කොන්දේසිගත හැඩතල ගැන්වීම 10 සිට 20 දක්වා අගයන් අඩංගු සෛල ඉස්මතු කරයි

සටහන ආකෘතියෙන් බාගන්න, උදාහරණ ආකෘතියෙන්

දැන් ඔබ විසින් ස්ථාපිත කර ඇති නිර්ණායක සපුරාලන සෛලවල අගයන් එකතු කළ යුතුය. මෙම සෛල සඳහා කුමන හැඩතල ගැන්වීම් යෙදුවත් කමක් නැත, නමුත් සෛල උද්දීපනය කරන නිර්ණායක ඔබ දැනගත යුතුය.

අනුරූපී සෛල පරාසයක් එක් කිරීමට තනියමනිර්ණායකය, ඔබට SUMIF ශ්රිතය භාවිතා කළ හැකිය (රූපය 2).

සහල්. 2. එකම කොන්දේසිය සපුරාලන සෛල සාරාංශ කිරීම

ඔබට තිබේ නම් සමහරකොන්දේසි, ඔබට SUMIFS ශ්‍රිතය භාවිතා කළ හැකිය (රූපය 3).

සහල්. 3. බහු කොන්දේසි සපුරාලන සෛල එකතු කිරීම

එකම නිර්ණායක සපුරාලන සෛල ගණන ගණනය කිරීමට ඔබට COUNTIF ශ්‍රිතය භාවිතා කළ හැක.

බහුවිධ නිර්ණායක සපුරාලන සෛල ගණන ගණනය කිරීමට ඔබට COUNTIFS ශ්‍රිතය භාවිතා කළ හැක.

Excel ඔබට කොන්දේසි කිහිපයක් නියම කිරීමට ඉඩ සලසන තවත් කාර්යයක් සපයයි. මෙම විශේෂාංගය මූලික විශේෂාංග කට්ටලයේ කොටසකි. excel දත්තසහ BDSUMM ලෙස හැඳින්වේ. එය පරීක්ෂා කිරීම සඳහා, A2:A100 පරාසයේ ඇති එකම සංඛ්‍යා කට්ටලය භාවිතා කරන්න (රූපය 4; Excel ගොනුවේ "BDSUMM" පත්‍රයද බලන්න).

සහල්. 4. දත්ත සමුදා විශේෂාංග භාවිතා කිරීම

C1:D2 කොටු තෝරන්න සහ සූත්‍ර තීරුවේ වම් පස ඇති නාම කොටුවේ ටයිප් කිරීමෙන් මෙම පරාසය නිර්ණායක නම් කරන්න. දැන් C1 කොටුව තෝරා =$A$1 ඇතුලත් කරන්න, එය දත්ත සමුදායේ නම අඩංගු වැඩ පත්‍රිකාවේ පළමු කොටුව වෙත සබැඳිය වේ. D1 කොටුවේ =$A$1 ඇතුළු කරන්න, එවිට ඔබට A තීරුවේ ශීර්ෂයේ පිටපත් දෙකක් ලැබෙනු ඇත. මෙම පිටපත් ඔබ නිර්ණායක ලෙස නම් කළ BDSUMM (C1:D2) හි කොන්දේසි සඳහා ශීර්ෂයන් ලෙස භාවිතා කරනු ඇත. C2 කොටුවේ, >10 ඇතුලත් කරන්න. D2 කොටුවේ, ටයිප් කරන්න<=20. В ячейке, где должен быть результат, введите следующую формулу:

BDSUMM($A$1:$A$101.1, නිර්ණායක)

බහු නිර්ණායක සපුරාලන සෛල ගණන ගණනය කිරීමට ඔබට COUNT ශ්‍රිතය භාවිතා කළ හැක.

John Walkenbach ගේ පොත කියවන අතරතුර, Excel 2010 සිට VBA විසින් නව DisplayFormat දේපලක් හඳුන්වා දුන් බව මම දැනගත්තා (උදාහරණයක් ලෙස, Range.DisplayFormat දේපල බලන්න). එනම්, VBA තිරයේ දිස්වන ආකෘතිය කියවිය හැකිය. එය ලබා ගත් ආකාරය, සෘජු පරිශීලක සැකසුම් හෝ කොන්දේසි සහිත හැඩතල ගැන්වීම් භාවිතා කිරීම වැදගත් නොවේ. අවාසනාවන්ත ලෙස, MS සංවර්ධකයින් විසින් එය සාදන ලද අතර එමඟින් DisplayFormat දේපල ක්‍රියා කරන්නේ VBA වෙතින් හැඳින්වෙන ක්‍රියා පටිපාටිවල පමණක් වන අතර, මෙම ගුණාංගය මත පදනම්ව පරිශීලක-නිර්වචනය කරන ලද කාර්යයන් #VALUE ජනනය කරයි! කෙසේ වෙතත්, ඔබට ක්‍රියා පටිපාටියක් (සාර්ව, ශ්‍රිතයක් නොවේ) භාවිතා කරමින් යම් වර්ණයක සෛල හරහා පරාසයක ඇති අගයන් එකතුව ලබා ගත හැක. විවෘත කරන්න (VBA කේතය අඩංගු වේ). මෙනුව හරහා යන්න දැක්ම -> මැක්රෝස් -> මැක්රෝස්; කවුළුව තුළ මැක්රෝ, රේඛාව ඉස්මතු කරන්න SumColorCond., සහ ඔබන්න දුවන්න. මැක්රෝ ධාවනය කරන්න, සාරාංශ පරාසය සහ නිර්ණායක තෝරන්න. පිළිතුර කවුළුවේ දිස්වනු ඇත.

ක්රියා පටිපාටි කේතය

Sub SumColorCond() Application.Volatile True Dim SumColor ලෙස Double Dim i ලෙස Dim UserRange as Range Dim CriterionRange As Range SumColor = 0 " පරාසයේ ඉල්ලීම් සකසන්න UserRange = Application.InputBox(_ Prompt: "Title Range:",Select Sumcolor ======================================================================================] සාරාංශ නිර්ණායකය", _ මාතෘකාව:="නිර්ණායක තේරීම", _ පෙරනිමිය:=ActiveCell.ලිපිනය, _ වර්ගය:=8) " i.DisplayFormat.Interior.Color = _ CriterionRange.DisplayFormat නම් පරිශීලක පරාසයේ එක් එක් i සඳහා "නිවැරදි" කොටු එකතු කරන්න අභ්යන්තරය

Sub SumColorCond() අයදුම්පත. වාෂ්පශීලී ඇත්ත Dim SumColor As Double Dim i As Range පරිශීලක පරාසය පරාසය ලෙස අඳුරු කරන්න Dim CriterionRange as Range එකතුව = 0 "පරාස ඉල්ලීම UserRange = Application.InputBox(_) සකසන්න විමසුම:="සම්පූර්ණ පරාසය තෝරන්න", _ මාතෘකාව:="පරාස තේරීම", _ පෙරනිමිය:=ActiveCell.ලිපිනය, _ වර්ගය:=8) "ඉල්ලීමේ නිර්ණායක CriterionRange = යෙදුම සකසන්න. ආදාන පෙට්ටිය(_ විමසුම := "සාරාංශ නිර්ණායකය තෝරන්න", _ මාතෘකාව : = "නිර්ණායක තේරීම" , _ පෙරනිමිය := ActiveCell . ලිපිනය , _

පැතුරුම්පතක සෑම තත්පරයකම, තුන්වන, හතරවන, ආදී සෛල සාරාංශ කිරීමට අවශ්‍ය වීම බොහෝ විට සිදුවේ. දැන්, පහත උපක්රමයට ස්තූතියි, එය කළ හැකිය.

එක්සෙල් එක් එක් සාරාංශ කළ හැකි සම්මත ශ්‍රිතයක් සපයන්නේ නැත n වන සෛලයහෝ නූලක්. කෙසේ වෙතත්, ඔබට මෙම කාර්යය විවිධ ආකාරවලින් ඉටු කළ හැකිය. මෙම සියලු ප්රවේශයන් ROW (ROW) සහ MOD (MOD) ශ්රිතයන් මත පදනම් වේ.

ROW කාර්යයලබා දී ඇති සෛල යොමුව සඳහා පේළි අංකය ලබා දෙයි: ROW(යොමු), Excel ROW(reference) හි රුසියානු අනුවාදයේ.
MOD කාර්යය (MOD)එක්සෙල් MOD(number;divisor) හි රුසියානු අනුවාදයේ: MOD(number;divisor), සංඛ්‍යාවක් බෙදීමකින් බෙදීමෙන් පසු ඉතිරිය ලබා දෙයි.

ROW ශ්‍රිතය MOD ශ්‍රිතයට දමන්න (සංඛ්‍යා තර්කයක් සම්මත කිරීමට), 2 න් බෙදන්න (සෑම දෙවන කොටුවක්ම එකතු කිරීමට) සහ ප්‍රතිඵලය ශුන්‍ය නොවේදැයි පරීක්ෂා කරන්න. එසේ නම්, සෛලය සාරාංශ කර ඇත. මෙම කාර්යයන් විවිධ ආකාරවලින් භාවිතා කළ හැකිය - සමහරක් අනෙක් ඒවාට වඩා හොඳ ප්රතිඵල ලබා දෙනු ඇත. උදාහරණයක් ලෙස, $A$1:$A$100 පරාසයේ අනෙක් සෑම සෛලයක්ම එකතු කිරීමට අරා සූත්‍රයක් මේ වගේ විය හැක: =SUM(IF(MOD(ROW($A$1:$A$500);2)=0;$ A$1: $A$500;0))

මෙය අරා සූත්‍රයක් වන බැවින්, ඔබ Ctrl+Shift+Enter එබීමෙන් එය ඇතුළු කළ යුතුය, Excel විසින් කැරලි වරහන් එක් කරනු ඇත එවිට එය මෙසේ දිස්වේ: (=SUM(IF(MOD(ROW($A$1:$A$500),) 2)= 0;$A$1:$A$500;0))) $500;0))) Curly brackets එකතු කිරීමට අපට Excel අවශ්‍යයි; ඔබ ඒවා ඔබම එකතු කළහොත්, සූත්‍රය ක්‍රියා නොකරනු ඇත.

ඉලක්කය සපුරා ගත්තද, මෙම ක්රමය නිර්මාණයට සෘණාත්මකව බලපායි. පැතුරුම්පත. මෙය අරා සූත්‍රයක අනවශ්‍ය යෙදුමකි. කාරණා වඩාත් නරක අතට හැරීම සඳහා, මෙම දිගු සූත්‍රය තුළ නැවත ගණනය කළ හැකි ROW ශ්‍රිතයක් ඇති අතර, විශාල සූත්‍රයද නැවත ගණනය කළ හැකිය. මෙයින් අදහස් කරන්නේ ඔබ කුමක් කළත් එය නිරන්තරයෙන් නැවත ගණනය කරනු ඇති බවයි වැඩපොත. මෙය ඉතා නරක ක්රමයකි!

මෙන්න තවත් සූත්රයක්, එය ටිකක් හොඳම තේරීම: =SUMPRODUCT((MOD(ROW($A$1:$A$500);2)=0)*($A$1:$A$500)) :$A$500);2)=0)*($A$1 :$A$500)) .

කෙසේ වෙතත්, මෙම සූත්‍රය #VALUE ආපසු ලබා දෙන බව මතක තබා ගන්න! (#VALUE!) පරාසයේ කිසියම් සෛලයක සංඛ්‍යා වෙනුවට පෙළ අඩංගු නම්. මෙම සූත්‍රය, ඇත්ත වශයෙන්ම අරා සූත්‍රයක් නොවුවද, මන්දගාමී වේ එක්සෙල් රැකියාවඑය බොහෝ වාර ගණනක් භාවිතා කරන්නේ නම්, හෝ එය සෑම අවස්ථාවකම විශාල පරාසයක් වෙත යොමු කරයි නම්.

වාසනාවකට මෙන් තිබේ හොඳම මාර්ගය, එය වඩාත් කාර්යක්ෂම පමණක් නොව, වඩාත් නම්යශීලී වේ. එය DSUM ශ්රිතය භාවිතා කිරීම අවශ්ය වේ. මෙම උදාහරණයේ දී, අපි A1:A500 පරාසය සෑම n වන කොටුවක්ම එකතු කරන පරාසය ලෙස භාවිතා කර ඇත.

E1 කොටුව තුළ, නිර්ණායක යන වචනය ඇතුළත් කරන්න. E2 කොටුවේ, පහත සූත්‍රය ඇතුළත් කරන්න: =MOD(ROW(A2)-$C$2-1,$C$2)=0 =0 . C2 කොටුව තෝරා Data → Validation තෝරන්න.

දත්ත වර්ගය (අවසර දෙන්න) ක්ෂේත්‍රය තුළ, ලැයිස්තුව (ලැයිස්තුව) තෝරන්න, සහ මූලාශ්‍ර (මූලාශ්‍රය) ක්ෂේත්‍රය තුළ, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 ඇතුළත් කරන්න. ලැයිස්තුව තහවුරු කර ගන්න. අවසර ලත් අගයන් පරීක්ෂා කර ඇත (Cell තුළ), සහ OK බොත්තම ක්ලික් කරන්න. C1 කොටුවේ, සෑම විටම SUM යන පෙළ ඇතුළත් කරන්න. පේළිය 1 හැර ඕනෑම කොටුවක, පහත සූත්‍රය ඇතුළත් කරන්න: =DSUM($A:$A;1;$E$1:$E$2) :$E$2) .

ඔබ DSUM ශ්‍රිතයට ඇතුළු වූ ස්ථානයට වහාම ඉහළින් ඇති කොටුවේ, ="Summing Every" & $C$2 & CHOOSE($C$2;"st";"nd";"rd";"th";" යන පාඨය ඇතුලත් කරන්න. th";"th";"th";"th";"th";"th") & "Cell" . දැන් එය C2 සෛලය තුළ අවශ්ය අංකය තෝරා ගැනීමට පමණක් ඉතිරිව ඇති අතර, ඉතිරිය DSUM ශ්රිතය මගින් සිදු කරනු ලැබේ.

DSUM ශ්‍රිතය භාවිතයෙන්, ඔබ සඳහන් කරන කාල පරතරයන්හිදී ඔබට සෛල එකතු කළ හැක. DSUM ශ්‍රිතය අරා සූත්‍රයකට හෝ SUMPRODUCT ශ්‍රිතයට වඩා බොහෝ කාර්යක්ෂම වේ. සෙට් වෙන්න ටිකක් වෙලා ගියාට මේක ඉගෙන ගන්න අමාරු, පොර වෙන්න ලේසි කේස් එකක්.