Formül Çözümleri - Excel İpuçları

Not

Bu, Podcast 2316 yarışması için gönderilen çözümleri ayrıntılarıyla anlatan bir dizi makaleden biridir.

Soruna çoğunlukla Power Query veya VBA çözümleri beklerken, bazı harika formül çözümleri vardı.

Hussein Korish, dinamik dizi formülü dahil 7 benzersiz formül içeren bir çözüm gönderdi.

7 benzersiz formül

Hücre Formülleri
Aralık	Formül
K13: K36	K13	= ENDEKS (FİLTRE (EĞER (UZUNLUK (TRANSPOSE (FİLTRE ($ H $ 3: $ AA $ 3, H3: AA3> LEN (H3: AA3)))))> 2, TRANSPOSE (FİLTRE ($ H $ 3: $ AA $ 3, H3 : AA3> UZUNLUK (H3: AA3))), ""), EĞER (UZUNLUK (TRANSPOSE (FİLTRE ($ H $ 3: $ AA $ 3, H3: AA3> UZUNLUK (H3: AA3))))> 2, TRANSPOSE ( FİLTRE ($ H $ 3: $ AA $ 3, H3: AA3> UZUNLUK (H3: AA3))), "") ""), EŞLEŞTİRME (SIRA (COUNTA ($ J $ 13: $ J $ 36) ,, 1,1) , SIRA (COUNTA ($ J $ 13: $ J $ 36) / COUNTA ($ B $ 4: $ B $ 9) ,, 1, COUNTA ($ B $ 4: $ B $ 9)), 1))
L13: L36	L13	= OFSET ($ H $ 3, MOD (COUNTA ($ J $ 12: J12) -1, COUNTA ($ B $ 4: $ B $ 9)) + 1, TRANSPOSE (MAÇ (K13, $ H $ 3: $ AA $ 3,0) ) + SÜTUNLAR ($ L $ 12: $ P $ 12) -COLUMNS (L $ 12: $ P $ 12))
M13: M36	M13	= OFSET ($ H $ 3, MOD (COUNTA ($ J $ 12: J12) -1, COUNTA ($ B $ 4: $ B $ 9)) + 1, TRANSPOSE (MAÇ (K13, $ H $ 3: $ AA $ 3,0) ) + SÜTUNLAR ($ L $ 12: $ P $ 12) -COLUMNS (M $ 12: $ P $ 12))
N13: N36	N13	= OFSET ($ H $ 3, MOD (COUNTA ($ J $ 12: J12) -1, COUNTA ($ B $ 4: $ B $ 9)) + 1, TRANSPOSE (MAÇ (K13, $ H $ 3: $ AA $ 3,0) ) + SÜTUNLAR ($ L $ 12: $ P $ 12) -COLUMNS (N $ 12: $ P $ 12))
O13: O36	O13	= OFSET ($ H $ 3, MOD (COUNTA ($ J $ 12: J12) -1, COUNTA ($ B $ 4: $ B $ 9)) + 1, TRANSPOSE (MAÇ (K13, $ H $ 3: $ AA $ 3,0) ) + SÜTUNLAR ($ L $ 12: $ P $ 12) -COLUMNS (O $ 12: $ P $ 12))
S13: S36	S13	= TOPLA (L13: O13)
J13: J36	J13	= INDEX ($ B $ 4: $ B $ 9, MAÇ (MOD (COUNTA ($ J $ 12: J12) -1, COUNTA ($ B $ 4: $ B $ 9)) + 1, SEKANS (COUNTA ($ B $ 4: $ B 9 ABD Doları), 1,1), 0))
Dinamik dizi formülleri.

Prashanth Sambaraju, beş formül kullanan başka bir formül çözümü gönderdi.

5 formül çözümü

Yukarıda kullanılan formüller:

Hücre Formülleri
Aralık	Formül
J15: J38	J15	= EĞER (MOD (SATIRLAR ($ J $ 15: J15), 6) = 0,6, MOD (SATIRLAR ($ J $ 15: J15), 6))
K15: K38	K15	= OFSET ($ A $ 3, J15, J $ 15,1,1)
L15: L38	L15	= BİRLEŞTİR ("Çalışan", "", YUVARLAK (SATIRLAR ($ J $ 15: J15) / 6,0))
M15: P38	M15	= OFSET ($ A $ 3, $ J15, MAÇ ($ L15, $ B $ 3: $ AA $ 3,0) + MOD (SÜTUNLAR ($ A: A), 5))
S15: S38	S15	= TOPLA (M15: P15)

René Martin, bu formül çözümünü üç benzersiz formülle gönderdi:

3 formül çözümü

Yukarıda kullanılan formüller:

Hücre Formülleri
Aralık	Formül
I12: N12	I12	= A3
I13: O13, O14: O36	I13	= EĞER (SÜTUN () = 9, OFSET ($ A $ 2, MOD (SATIR (A1), 6) +1,0), EĞER (SÜTUN () = 10, "Çalışan" & YUVARLAK (SATIR (A1) / 6, 0), EĞER (SÜTUN () = 15, TOPLA (E13: H13), OFSET ($ G $ 3, MOD (SATIR (A6), 6) + 1, YUVARLAK (SATIR (A1) / 6,0) * 5- 7 + SÜTUN (A1)))))
I14: N36	I14	= EĞER (SÜTUN () = 9, OFSET ($ A $ 2, MOD (SATIR (A2), 6) +1,0), EĞER (SÜTUN () = 10, "Çalışan" & YUVARLAK (SATIR (A2) / 6, 0), OFSET ($ G $ 3, MOD (SIRA (A7), 6) + 1, YUVARLAK (SIRA (A2) / 6,0) * 5-7 + KOLON (A2))))

René Martin'den alternatif bir çözüm:

Hücre Formülleri
Aralık	Formül
I12: N12	I12	= A3
I13: O13, O14: O36	I13	= EĞER (SÜTUN () = 9, OFSET ($ A $ 2, MOD (SATIR (A1), 6) +1,0), EĞER (SÜTUN () = 10, "Çalışan" & YUVARLAK (SATIR (A1) / 6, 0), EĞER (SÜTUN () = 15, TOPLA (E13: H13), OFSET ($ G $ 3, MOD (SATIR (A6), 6) + 1, YUVARLAK (SATIR (A1) / 6,0) * 5- 7 + SÜTUN (A1)))))
I14: N36	I14	= EĞER (SÜTUN () = 9, OFSET ($ A $ 2, MOD (SATIR (A2), 6) +1,0), EĞER (SÜTUN () = 10, "Çalışan" & YUVARLAK (SATIR (A2) / 6, 0), OFSET ($ G $ 3, MOD (SIRA (A7), 6) + 1, YUVARLAK (SIRA (A2) / 6,0) * 5-7 + KOLON (A2))))

Excel MVP Roger Govier bir formül çözümü gönderdi. İlk olarak Roger, orijinal verilerden gereksiz sütunları sildi. Roger, onları orada bırakabileceğinizi, ancak o zaman sütun dizin numaralarını uygun şekilde ayarlamanız gerektiğini belirtir.

Roger, üç adlandırılmış aralık kullandı. Bu şekil seçilen _rows'u göstermektedir.