横方向にスプレッドしたExcel表から条件を満足する人数をカウントするには？
解決したいこと
こんな感じで複数のフラグから成り立つ属性をそれぞれの人がもってます
name,1st,2nd,3rd,4th
Alice,1,1,0,0
bob,0,1,1,1
Clara,1,0,1,0
joe,0,1,1,1
john,0,1,1,1
michel,0,1,1,1
そんでもって、この中で3番目のフラグが立っている人数を数えるなら
はなしは簡単です。
しかし、プロジェクトごとにグルーピングされた下のようなExcel表があたえられてます。（組織の人がExcelしか扱えないので、なにかとExcelの範囲内で何とかする文化なのです。）
私の環境です
- Windows10
- Excel2019（いわゆる買い切り、v16.0.....)をデスクトップで使ってます
- WSL2やパワーシェルも導入済みです
希望など
- 主観的な理由ですみませんがVBAはなるべく避けたい
- CSVを中間ファイルとして作って処理をawk/python/bash/powershellで行うのはOK
- 処理結果をExcelにコピペで貼り付けるのもOK
- 中間ファイルは同僚に見せないのでawk/python/bash/powershellでもいいといえばいいのです
- SQL初心者なので、そちらもなるべくさけたい。
話の続き
Prj1,Alice,Bob,Joe
Prj2,Clara,Michel,
Prj3,Bob,Joe,John
たとえばプロジェクト1の参加者のうち、3番目のフラグが立っている人数をカウント
してって言われてます。どうしたらいいですか？
あとそれぞれのプロジェクトの参加人数は一定ではありません。2人だったり3人だったり、いろいろ・・（人生いろいろ）
プロジェクト2も3もそういう条件を満足する人数をカウントせねば・・・それはプロジェクト1がうまくいけば、あとはコピペでいけますよね？
発生している問題
あとで提示した横方向にスプレッドしたテーブルにおける名前フィールドの右側に属性をインサートしていくという作業が必要になりそうですが、それのやり方がわかりません。
属性における4つのフラグを一つにするために16進数（ヘキサゴナル）に変換することをおもいつきました。Excelならこんな感じで。。
=DEC2HEX(B2*8+C2*4+D2*2+E2)
コピーペーストで逐一作業するのではなく、半自動で、横方向にスプレッドしたテーブルにヘキサゴナル化属性を右側に追加していくにはどうしたらいいですか？
Excelでやらせるより、Awkスクリプト書いたほうがいいですか？
bashの場合
まだ動作検証してませんが、Copilotはつぎのようなコードを提案してきました
(1.csvが、名前と16進数からなるテーブルで、2.csvがグルーピングした横ベタです）
#!/bin/bash
declare -A scores
while IFS=',' read -r name score
do
scores["$name"]=$score
done < 1.csv
while IFS=',' read -r -a names
do
for name in "${names[@]}"
do
name=$(echo "$name" | xargs)
echo -n "$name,${scores[$name]},"
done
echo
done < 2.csv
もし属性を右側にインサートできたとしたら、テーブルのたとえば第2行はつぎのようになります。
name,hex,name,hex,name,hex
alice,C,bob,7,clara,A
ここまでなんとかこぎつければ、たとえば、3番目のフラグが立っている人数は簡単に求められますね
自分で試したこと
=--(MID(DEC2BIN(HEX2DEC(B2), 4), 3, 1)="1") + --(MID(DEC2BIN(HEX2DEC(D2), 4), 3, 1)="1") + --(MID(DEC2BIN(HEX2DEC(F2), 4), 3, 1)="1")
こんな感じでカウントできると思います。どうしたらここまでもっていけますか？ご助言ください。このMWEではフラグの数が4つなので、ヘキサゴナルなら1ケタで表現できますが、もっとフラグ数が増えた場合でも同じような考え方で拡張可能ですか？
