Formula Solutions - Συμβουλές για το Excel

Σημείωση

Αυτό είναι ένα από μια σειρά άρθρων που περιγράφουν λεπτομερώς τις λύσεις που αποστέλλονται για την πρόκληση Podcast 2316.

Ενώ περίμενα ως επί το πλείστον λύσεις Power Query ή VBA στο πρόβλημα, υπήρχαν κάποιες καλές λύσεις τύπου.

Ο Hussein Korish έστειλε μια λύση με 7 μοναδικούς τύπους, συμπεριλαμβανομένου ενός τύπου δυναμικού πίνακα.

7 μοναδικές φόρμουλες

Τύποι κυττάρων
Εύρος	Τύπος
Κ13: Κ36	Κ13	= INDEX (ΦΙΛΤΡΟ (ΕΑΝ (LEN (ΜΕΤΑΦΟΡΑ (ΦΙΛΤΡΟ ($ H $ 3: $ AA $ 3, H3: AA3> LEN (H3: AA3))))> 2, ΜΕΤΑΦΟΡΑ (ΦΙΛΤΡΟ ($ H $ 3: $ AA $ 3, H3) : AA3> LEN (H3: AA3))), ""), IF (LEN (TRANSPOSE (FILTER ($ H $ 3: $ AA $ 3, H3: AA3> LEN (H3: AA3))))> 2, ΜΕΤΑΦΟΡΑ ( ΦΙΛΤΡΟ ($ H $ 3: $ AA $ 3, H3: AA3> LEN (H3: AA3))), "") ""), MATCH (SEQUENCE (COUNTA ($ J $ 13: $ J $ 36) ,, 1,1) , SEQUENCE (COUNTA ($ J $ 13: $ J $ 36) / COUNTA ($ B $ 4: $ B $ 9) ,, 1, COUNTA ($ 4 $: $ B $ 9)), 1))
L13: L36	L13	= OFFSET ($ H $ 3, MOD (COUNTA ($ J $ 12: J12) -1, COUNTA ($ B $ 4: $ B $ 9)) + 1, ΜΕΤΑΦΟΡΑ (MATCH (K13, $ H $ 3: $ AA $ 3,0) ) + ΣΤΗΛΕΣ ($ L $ 12: $ P $ 12) -COLUMNS (12 $: 12 $ $ 12))
Μ13: Μ36	Μ13	= OFFSET ($ H $ 3, MOD (COUNTA ($ J $ 12: J12) -1, COUNTA ($ B $ 4: $ B $ 9)) + 1, ΜΕΤΑΦΟΡΑ (MATCH (K13, $ H $ 3: $ AA $ 3,0) ) + ΣΤΗΛΕΣ ($ L $ 12: $ P $ 12) -COLUMNS (12 $: 12 $ $ 12))
Ν13: Ν36	Ν13	= OFFSET ($ H $ 3, MOD (COUNTA ($ J $ 12: J12) -1, COUNTA ($ B $ 4: $ B $ 9)) + 1, ΜΕΤΑΦΟΡΑ (MATCH (K13, $ H $ 3: $ AA $ 3,0) ) + ΣΤΗΛΕΣ ($ L $ 12: $ P $ 12) -COLUMNS (N $ 12: $ P $ 12))
Ο13: Ο36	Ο13	= OFFSET ($ H $ 3, MOD (COUNTA ($ J $ 12: J12) -1, COUNTA ($ B $ 4: $ B $ 9)) + 1, ΜΕΤΑΦΟΡΑ (MATCH (K13, $ H $ 3: $ AA $ 3,0) ) + ΣΤΗΛΕΣ ($ L $ 12: $ P $ 12) -COLUMNS (O $ 12: $ P $ 12))
P13: P36	Ρ13	= SUM (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))
Δυναμικοί τύποι πίνακα.

Ο Prashanth Sambaraju έστειλε μια άλλη λύση τύπου που χρησιμοποιεί πέντε τύπους.

5 λύσεις τύπων

Οι τύποι που χρησιμοποιήθηκαν παραπάνω:

Τύποι κυττάρων
Εύρος	Τύπος
J15: J38	J15	= IF (MOD (ROWS ($ J $ 15: J15), 6) = 0,6, MOD (ROWS ($ J $ 15: J15), 6))
Κ15: Κ38	Κ15	= OFFSET ($ A $ 3, J15, J $ 15,1,1)
L15: L38	L15	= CONCATENATE ("Υπάλληλος", "", ROUNDUP (ROWS ($ J $ 15: J15) / 6,0))
Μ15: Ρ38	Μ15	= OFFSET ($ A $ 3, $ J15, MATCH (L15 $, $ B $ 3: $ AA $ 3,0) + MOD (COLUMNS ($ A: A), 5))
Ε15: Ε38	Ε15	= SUM (M15: P15)

Ο Ρεν Μάρτιν έστειλε σε αυτήν τη λύση τύπου με τρεις μοναδικούς τύπους:

3 λύσεις τύπων

Οι τύποι που χρησιμοποιούνται στα παραπάνω:

Τύποι κυττάρων
Εύρος	Τύπος
I12: N12	Ι12	= Α3
I13: O13, O14: O36	Ι13	= 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 + ΣΤΗΛΗ (A1)))))
I14: Ν36	Ι14	= 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))))

Μια εναλλακτική λύση από τον René Martin:

Τύποι κυττάρων
Εύρος	Τύπος
I12: N12	Ι12	= Α3
I13: O13, O14: O36	Ι13	= 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 + ΣΤΗΛΗ (A1)))))
I14: Ν36	Ι14	= 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))))

Το Excel MVP Roger Govier έστειλε μια λύση τύπου. Πρώτα απ 'όλα, ο Roger διέγραψε τις περιττές στήλες από τα αρχικά δεδομένα. Ο Roger επισημαίνει ότι θα μπορούσατε να τους αφήσετε εκεί, αλλά τότε πρέπει να προσαρμόσετε κατάλληλα τους αριθμούς ευρετηρίου στηλών.

Ο Roger χρησιμοποίησε τρία ονόματα. Αυτό το σχήμα δείχνει τις επιλεγμένες γραμμές.