फॉर्मूला सॉल्यूशंस - एक्सेल टिप्स

ध्यान दें

यह पॉडकास्ट 2316 चुनौती के लिए भेजे गए समाधानों का विवरण देने वाले लेखों की एक श्रृंखला है।

जबकि मुझे समस्या के ज्यादातर पावर क्वेरी या वीबीए समाधानों की उम्मीद थी, कुछ शांत सूत्र समाधान थे।

हुसैन कोरिश ने 7 अद्वितीय सूत्रों के साथ एक समाधान में भेजा, जिसमें एक गतिशील सरणी सूत्र शामिल है।

7 अनोखे सूत्र
सेल सूत्र
सीमा सूत्र
के १३: के ३६ के १३ = INDEX (फ़िल्टर (IF (LEN (TransPOSE) ($ H $ 3: $ AA $ 3, H3: AA3> LEN (H3: AA3)))> 2, TRANSPOSE (FILTER ($ H $ 3: $ AA $ 3, H3) : AA3> LEN (H3: AA3))), ""), IF (LEN (TransPOSE (फिल्टर एच $ 3: $ AA $ 3, H3: AA3> LEN (H3: AA3))))> 2, TRANSPOSE ( फ़िल्टर ($ H $ 3: $ AA $ 3, H3: AA3> LEN (H3: AA3))), "") ""), MATCH (SEQUENCE (COUNTA ($ J $ 13: $ 36) ,, 1,1) , SEQUENCE (COUNTA ($ J $ 13: $ J $ 36) / COUNTA ($ B $ 4: $ B $ 9), 1, COUNTA ($ B $ 4: $ B $ 9)), 1)
L13: L36 L13 = OFFSET ($ H $ 3, MOD (COUNTA ($ J $ 12: J12) -1, COUNTA ($ B $ 4: $ B $ 9)) + 1, TRANSPOSE (MATCH (K13, $ $ 3: $ AA $ 3,0)) ) + COLUMNS ($ L $ 12: $ P $ 12) -COLUMNS (L $ 12: $ P $ 12)
M13: M36 एम 13 = OFFSET ($ H $ 3, MOD (COUNTA ($ J $ 12: J12) -1, COUNTA ($ B $ 4: $ B $ 9)) + 1, TRANSPOSE (MATCH (K13, $ $ 3: $ AA $ 3,0)) ) + COLUMNS ($ L $ 12: $ P $ 12) -COLUMNS (M $ 12: $ P $ 12)
N13: N36 N13 = OFFSET ($ H $ 3, MOD (COUNTA ($ J $ 12: J12) -1, COUNTA ($ B $ 4: $ B $ 9)) + 1, TRANSPOSE (MATCH (K13, $ $ 3: $ AA $ 3,0)) ) + COLUMNS ($ L $ 12: $ P $ 12) -COLUMNS (N $ 12: $ P $ 12)
O13: O36 O13 = OFFSET ($ H $ 3, MOD (COUNTA ($ J $ 12: J12) -1, COUNTA ($ B $ 4: $ B $ 9)) + 1, TRANSPOSE (MATCH (K13, $ 3: $ AA $ 3,0) ) + COLUMNS ($ L $ 12: $ P $ 12) -COLUMNS (O $ 12: $ P $ 12)
P13: P36 P13 = सूम (L13: O13)
J13: J36 J13 = INDEX ($ B $ 4: $ B $ 9, MATCH (MOD (COUNTA ($ J $ 12: J12) -1, COUNTA ($ B $ 4: $ B $ 9)) + 1, SEQUENCE (COUNTA ($ B $ 4: $ B) $ 9), 1,1), 0))
गतिशील सरणी सूत्र।

प्रशान्त सम्बराजु ने एक अन्य सूत्र समाधान में भेजा है जिसमें पाँच सूत्र हैं।

5 सूत्र समाधान

ऊपर उपयोग किए गए सूत्र:

सेल सूत्र
सीमा सूत्र
J15: J38 जे 15 = IF (MOD (ROWS ($ J $ 15: J15), 6) = 0,6, MOD (ROWS ($ J $ 15: J15), 6))
K15: K38 के 15 = OFFSET ($ A $ 3, J15, J $ 15,1,1)
L15: L38 L15 = CONCATENATE ("कर्मचारी", "", ROUNDUP (ROWS ($ J $ 15: J15) / 6,0))
M15: P38 एम 15 = OFFSET ($ A $ 3, $ J15, MATCH ($ L15, $ B $ 3: $ AA $ 3,0) + MOD (COLUMNS ($ A: A), 5)
क्यू 15: क्यू 38 प्र 15 = एसयूएम (एम 15: पी 15)

तीन अद्वितीय फ़ार्मुलों के साथ रेने मार्टिन ने इस सूत्र समाधान में भेजा:

3 सूत्र समाधान

उपरोक्त में प्रयुक्त सूत्र:

सेल सूत्र
सीमा सूत्र
I12: N12 I12 = ए 3
I13: O13, O14: O36 I13 = IF (COLUMN () = 9, OFFSET ($ A $ 2, MOD (ROW (A1), 6) +1,0), IF (COLUMN) () = 10, "कर्मचारी" और ROUNDUP (ROW (A1) / 6,) 0), IF (COLUMN () = 15, SUM (E13: H13), OFFSET ($ G $ 3, MOD (ROW (A6), 6) + 1), ROUNDUP (ROW (A1) / 6,0) * 5-) 7 + COLUMN (A1))))
I14: N36 I14 = IF (COLUMN () = 9, OFFSET ($ A $ 2, MOD (ROW (A2), 6) +1,0), IF (COLUMN) () = 10, "कर्मचारी" और ROUNDUP (ROW (A2) / 6,) 0), OFFSET ($ G $ 3, MOD (ROW (A7), 6) + 1, ROUNDUP (ROW (A2) / 6,0) * 5-7 + COLUMN (A2)))

रेने मार्टिन का एक वैकल्पिक समाधान:

सेल सूत्र
सीमा सूत्र
I12: N12 I12 = ए 3
I13: O13, O14: O36 I13 = IF (COLUMN () = 9, OFFSET ($ A $ 2, MOD (ROW (A1), 6) +1,0), IF (COLUMN) () = 10, "कर्मचारी" और ROUNDUP (ROW (A1) / 6,) 0), IF (COLUMN () = 15, SUM (E13: H13), OFFSET ($ G $ 3, MOD (ROW (A6), 6) + 1), ROUNDUP (ROW (A1) / 6,0) * 5-) 7 + COLUMN (A1))))
I14: N36 I14 = IF (COLUMN () = 9, OFFSET ($ A $ 2, MOD (ROW (A2), 6) +1,0), IF (COLUMN) () = 10, "कर्मचारी" और ROUNDUP (ROW (A2) / 6,) 0), OFFSET ($ G $ 3, MOD (ROW (A7), 6) + 1, ROUNDUP (ROW (A2) / 6,0) * 5-7 + COLUMN (A2)))

एक्सेल एमवीपी रोजर गोवियर ने एक सूत्र समाधान में भेजा। सबसे पहले, रोजर ने मूल डेटा से अनावश्यक स्तंभों को हटा दिया। रोजर बताते हैं कि आप उन्हें वहां छोड़ सकते हैं, लेकिन फिर आपको कॉलम इंडेक्स संख्या को उचित रूप से समायोजित करना होगा।

रोजर ने तीन नामित श्रेणियों का उपयोग किया। यह आंकड़ा _rows चयनित दिखाता है।

3 नामित पर्वतमाला

He also added _Cols as B3:U3. He redefined my Ugly_Data as B4:U9.

Roger’s solution is two formulas, copied down and one formula copied down and across.

2 formulas solution

Return to the main page for the Podcast 2316 challenge.

To read the last article and Bill’s composite solution: Composite Solution to Podcast 2316 Challenge

दिलचस्प लेख...