สูตร Excel - การสร้างอาร์เรย์ OR ภายใน SUM

สูตรอาร์เรย์ที่ฉันมีตอนนี้ใกล้เคียงกับที่ฉันต้องการมาก แต่ถูกจำกัดด้วยความจริงที่ว่าฉันสามารถมีเงื่อนไขได้เพียงสองเงื่อนไขเท่านั้น (เว้นแต่ฉันต้องการเพิ่มอีกด้วยตนเอง) สิ่งที่ฉันต้องการทำคือสามารถใช้ชื่อที่ได้มาจากคอลัมน์ของตารางเพื่อให้ตรงกับเกณฑ์ คล้ายกับการใช้ OR() กับช่วง

สูตรอาร์เรย์ของฉันมีลักษณะคล้ายกับสิ่งนี้ (โดยใช้ชื่อหรือการอ้างอิงช่วง)

=SUM((JOB_HRS)*(TEXT($E4,"0")=TEXT(EMP_ID,"0"))*(JOB_DATE>=$M4)*(JOB_DATE<=$N4)*((JOB_TYPE=CONFIG!$F$8)+(JOB_TYPE=CONFIG!$F$9)))

ฉันได้รับแผ่นรายงานที่มีข้อมูลประมาณ 5,000 แถว สูตรนี้แยกวิเคราะห์ชีตอย่างแม่นยำและสรุปจำนวนชั่วโมงทำงานทั้งหมดของพนักงาน (ตาม ID ซึ่งบางครั้งจัดเก็บเป็นตัวเลขหรือข้อความตามความรู้สึกของโปรแกรมเอาท์พุตในวันนั้น)

ส่วนสุดท้ายคือสิ่งที่ฉันกำลังมองหาเพื่อปรับปรุง

((JOB_TYPE=CONFIG!$F$8)+(JOB_TYPE=CONFIG!$F$9))

ใช้งานได้ แต่ฉันถูกจำกัดด้วยจำนวนเซลล์เล็กน้อย แถมสูตรยังดูยาวอีกด้วย ฉันต้องการทำให้มันง่ายขึ้นเป็นเช่นนี้ แต่ไม่พบการอ้างอิงที่ดีในการทำงานกับอาร์เรย์ SUM มีความคิดอะไรบ้าง?

(JOB_TYPE=RNG_OF_JOB_TYPES)

แก้ไข: เพิ่มรูปภาพ

ผลลัพธ์ที่คาดหวัง


arrays conditional-statements excel sum
person Karl    schedule 29.11.2017    source แหล่งที่มา
comment
มีเหตุผลใดบ้างที่คุณไม่ใช้ PivotTable เพียงอย่างเดียว   -  person jeffreyweir    schedule 29.11.2017
comment
จากสิ่งที่ฉันพบ PivotTables ใช้การอ้างอิงแบบฮาร์ดโค้ดสำหรับตัวกรอง และฉันจำเป็นต้องสามารถอัปเดตของฉันแบบไดนามิกด้วยการอ้างอิงเซลล์   -  person Karl    schedule 29.11.2017
comment
ใช้ตัวแบ่งส่วนข้อมูลเพื่อกรอง Pivot ผลลัพธ์ที่ได้จะเหมือนเดิม การใช้งานและประสบการณ์ผู้ใช้ง่ายขึ้นมาก   -  person jeffreyweir    schedule 29.11.2017
comment
ฉันคิดว่าส่วนหนึ่งของการจองของฉันคือการจัดรูปแบบ ฉันมีแผ่นงานที่จัดรูปแบบอย่างสมบูรณ์แบบเพื่อพิมพ์บนแผ่นเดียว ฉันไม่ต้องการให้ PivotTable ขยายหรือย่อ นอกจากนี้ ฉันกำลังพยายามจำกัดสิ่งที่ผู้ใช้ปลายทางสามารถปรับได้บนแผ่นงานโดยใช้เซลล์ที่ได้รับการป้องกัน และการจัดรูปแบบตามเงื่อนไขจะมีน้ำหนักมาก ฉันสามารถทำสิ่งนี้ด้วย VBA ได้ง่ายสุด ๆ แต่ฉันยังถูกจำกัดด้วยนโยบายความปลอดภัยบนคอมพิวเตอร์ที่จะใช้งาน   -  person Karl    schedule 29.11.2017
comment
PivotTable จะไม่แสดงจำนวนระเบียนที่เท่ากันทุกประการกับสูตรของคุณหรือไม่ นอกจากนี้ สูตรนี้จะถูกดำเนินการกับ Emp_ID แต่ละรายการหรือไม่ คุณมีกี่อัน? คุณเคยดูฟังก์ชัน SUMIFS แล้วหรือยัง? โปรดทราบว่า SUMIF/SUMIFS มีราคาแพงในการคำนวณ และควรใช้เท่าที่จำเป็น สิ่งสุดท้ายที่คุณต้องการคือแผ่นงานที่มี SUMIFS นับหมื่นอยู่ในนั้น   -  person jeffreyweir    schedule 30.11.2017
comment
Emp_ID คือสิ่งที่จะเปลี่ยนแปลงในแต่ละแถว สูตรเกิดขึ้นใน 1 คอลัมน์เท่านั้น ฉันมีข้อมูลสรุปอื่นๆ จำนวนมากที่ต้องแสดงแบบอินไลน์พร้อมกับเป็นเอกสารต้นฉบับเดียวที่ PivotTable จะไม่พอดี นี่คือสิ่งที่ต้องคัดลอกลงใน PowerPoint ได้อย่างง่ายดายและบรรยายสรุปให้กับบุคคลประเภท CEO ที่ไม่มีเวลาถอดรหัส ฉันต้องการผลลัพธ์เพียง 1 รายการจากสูตรเท่านั้นจึงจะพอดีกับเซลล์เดียว   -  person Karl    schedule 30.11.2017
comment
ถ้าคุณมี Emp_ID ในบานหน้าต่างแถวและคอลัมน์เฉพาะอื่นๆ ที่คุณต้องการในบานหน้าต่างแถวหรือคอลัมน์ แล้ว PivotTable จะใช้พื้นที่มากกว่าการพูดในโซลูชันสูตรอย่างไร ฉันคิดว่าฉันขาดอะไรบางอย่างไป...   -  person jeffreyweir    schedule 30.11.2017
comment
คุณจะฮาร์ดโค้ด Emp_ID เหล่านั้นหรือไม่ มีความเป็นไปได้ที่จะมีการเปลี่ยนแปลงในอนาคตหรือไม่? จะดีกว่าไหมถ้าใช้ PivotTable ในกรณีนี้ คุณจะได้รับรายการ Emp_ID ทั้งหมด แทนที่จะเป็นรายการที่อาจซ้ำซ้อน ขออภัยที่ต้องพูดถึง PivotTables...แค่พยายามทำความเข้าใจความต้องการของคุณและให้แน่ใจว่าคุณได้รับแนวทางที่ดีที่สุด :-)   -  person jeffreyweir    schedule 30.11.2017
comment
ฉันใช้คอลัมน์ที่มีการตรวจสอบข้อมูลเพื่อเลือก Emp ตามชื่อ ซึ่งเติม ID ในคอลัมน์ที่ซ่อนอยู่ แผ่นงานนี้เรียงลำดับตามตำแหน่งพนักงานที่มีโค้ดตายตัว ซึ่งพนักงานจะเปลี่ยนออกเป็นระยะๆ ฉันจะอัปโหลดตัวอย่างว่าผลลัพธ์จะต้องเป็นอย่างไร คอลัมน์ทางด้านขวาคือคอลัมน์จากการคำนวณตามสูตรเดิมของฉัน ฉันแค่ต้องการอัปเดตเพื่อให้สามารถใช้เกณฑ์เพิ่มเติมแบบไดนามิกได้   -  person Karl    schedule 30.11.2017
comment
คุณมีพนักงานประมาณ 5,000 คนที่จำเป็นต้องแสดงใช่ไหม หรือนี่เป็นรายงานเฉพาะกิจที่ผู้ใช้เลือกพนักงานที่สนใจจำนวนหนึ่ง   -  person jeffreyweir    schedule 30.11.2017
comment
เฉพาะกิจมากขึ้น ฉันมีพนักงานเพียง 42 คนที่ต้องติดตาม สิ่งที่ยาวนานคือฉันมีชั่วโมงบิน 5,000 แถว   -  person Karl    schedule 30.11.2017
comment
ฉันชอบการควบคุมเฉพาะที่ฉันมีมากกว่าการจัดรูปแบบและเค้าโครง   -  person Karl    schedule 30.11.2017
comment
ฉันใช้สไตล์ PivotTable แบบกำหนดเองเพื่อทำให้ PivotTable มีลักษณะเหมือนกับที่ฉันต้องการ แต่ฉันลำเอียงอย่างสิ้นหวัง...ฉันคิดว่า PivotTable เป็นวิธีที่ดีที่สุดในการทำอะไรก็ได้ และใช้แทน VBA หรือสูตรในทุกที่ที่ทำได้   -  person jeffreyweir    schedule 30.11.2017
comment
อีกวิธีหนึ่งในการได้รับสิ่งที่ดีที่สุดจากทั้งสองโลกคือการใช้ PivotTable บนแผ่นงานที่ซ่อนอยู่เพื่อทำการกระทืบตัวเลข จากนั้นดึงคำตอบเฉพาะเจาะจงที่คุณต้องการโดยใช้ฟังก์ชัน GETPIVOTDATA ที่ยอดเยี่ยม ได้รวมสิ่งนี้ไว้ในคำตอบของฉันแล้ว   -  person jeffreyweir    schedule 30.11.2017
comment
ฉันคิดว่าฉันมีอคติต่อพวกเขา จริงๆ แล้วฉันชอบ Access มากกว่า แต่ฉันต้องทำให้ตัวติดตามนี้เรียบง่าย... ฉันคิดว่าฉันกำลังมองหาบางอย่างที่คล้ายกับ *(IF(MATCH(FLTD_ACFT_MDS,CFG_PRIACFT,0),1,0)) แต่สิ่งนี้กลับแสดงข้อผิดพลาด มันทำงานในอาร์เรย์การนับ   -  person Karl    schedule 30.11.2017
comment
MAX(--(JOB_TYPE=RNG_OF_JOB_TYPES)) ควรให้ 0 หากไม่มีข้อใดเป็นจริง และ 1 หากข้อใดเป็นจริง   -  person MacroMarc    schedule 30.11.2017
comment
มีวิธีใช้ MAX ร่วมกับ SUM()*() หรือไม่? ฉันได้รับข้อผิดพลาดกับมัน   -  person Karl    schedule 01.12.2017

คำตอบ (1)


arrow_upward
1
arrow_downward

ทางออกที่ดีที่สุดสำหรับสูตรคือใช้สูตร SUMIFS คุณจะต้องระบุแต่ละเกณฑ์แยกกัน แต่ไวยากรณ์จะง่ายกว่ามาก:

=SUMIFS(sum_range,criteria_range,criteria,...)

แต่ตามที่คุยกันไปแล้ว ความชอบส่วนตัวของฉันคือการใช้ PivotTable เพื่อทำการกระทืบตัวเลข บางครั้งฉันวาง Pivot ไว้บนชีตที่ซ่อนอยู่ และใช้ฟังก์ชัน GETPIVOTDATA ในชีตที่ผู้ใช้เปิดเผยเพื่อรับอินพุตและดึงค่ารวมจาก PivotTable สิ่งนี้ทำให้คุณมีความยืดหยุ่นในการจัดรูปแบบอย่างสมบูรณ์

person jeffreyweir    schedule 29.11.2017