☰

共有値によるデータ列のグループ化

tag-icon • tag-icon microsoft-excel
共有値によるデータ列のグループ化

何をすべきかを適切に説明する方法がわからないので、例を挙げます。同僚が Excel に次のようなデータ セットを持っています。

Col A    Col B    Col C
aaaaa    aaaaa    bbbbb
bbbbb    ccccc    ccccc
ccccc    ddddd    eeeee

最終結果は次のようになります。

Col A    Col B    Col C
aaaaa    aaaaa
bbbbb             bbbbb
ccccc    ccccc    ccccc
         ddddd
                  eeeee

あるいは：

      Col A    Col B    Col C
aaaaa Yes      Yes      No
bbbbb Yes      No       Yes

等

(参考までに、列はタンパク質抽出方法、文字はタンパク質 ID です。どのタンパク質がどの方法で抽出されるかを判断する必要があります)

私の同僚はこれを手作業で行っていますが、データが十分にあるので、自動化すると非常に役立ちます。

これを自動的に実行する数式は Excel にありますか?

答え1

これは「ターンキー」ソリューションではありませんが、何千もの行がある場合は、いくらかの労力を節約できる可能性があります。(「元に戻す」が常に機能するとは限らないため、何かが爆発したり崩壊したりした場合に備えて、ファイルのスクラッチ コピーでこれを実行してください。) 注: この手順は Excel 2007 用に開発されました (ただし、Excel 2013 で再検証しました)。

まず、すべてのデータをスクラッチ列 (ここでは V と呼びます) にコピーします。列 A から見出しをコピーするか、セル V1 にダミーの値を入力する必要があることに注意してください。

データのコピーの図

次に、「データ」タブの「並べ替えとフィルター」グループに移動し、「詳細設定」をクリックします。

                        「並べ替えとフィルター」グループ、「詳細」が強調表示されている

「詳細フィルター」ダイアログボックスが表示されます。

                  「高度なフィルター」ダイアログボックス

「リスト範囲」の列 V にデータが表示されていることを確認します。「別の場所にコピー」と「一意のレコードのみ」を選択します。「コピー先」フィールドに「W1」と入力するか、フィールドをクリックしてから W1 をクリックします (同じ結果を得るには、いくつかの方法があります)。「OK」をクリックします。次のようになります。

                              V のデータは重複を削除して W にコピーされます

つまり、一意のデータ値のリストです。列 W を並べ替える必要がある場合があります。

次に、=NOT(ISNA(VLOOKUP($W2,A$2:A$4,1,FALSE)))X2 を入力し ( 4データが含まれている最後の行の番号に置き換えます)、列 W (つまり、元のデータ内の一意の値ごとに 1 行) と一致するように下にドラッグ/入力し、列 Z (つまり、データ内の列の数) まで右にドラッグ/入力してください。

                              各列に各値があるかどうかの TRUE/FALSE を示す表

これにより、質問の2番目の形式に対応する真理値表が得られます（ただし、「はい」と「いいえ」の代わりに「TRUE」と「FALSE」を使用します）。たとえば、

  • X2は列Aに「aaaaa」が含まれているためTRUEです。
  • X3は列Aに「bbbbb」が含まれているためTRUEです。
  • Y2は列Bに「aaaaa」が含まれているためTRUEです。
  • 列 B には「bbbbb」などが含まれていないため、Y3 は FALSE です。

列 V を削除し、必要に応じて見出し (行 1) を修正します。スプレッドシートに列 AC を保持したくない場合は、列 WZ をコピーして値を貼り付けます。

数式についての説明: 上で示した数式は列Xで使用するためのものです。 これは列 A に相当します。  を使用したので、これは列Wへの絶対参照であり、 数式が行にドラッグ/入力されたときに $W2セルを参照します。Wnん任意の列の 。対照的に、A$2:A$4は行 2 から 4 への絶対参照ですが、列 A への相対参照です。数式を列 Y にドラッグすると、この参照は自動的に に変更されます B$2:B$4。数式を列 Z にドラッグすると、この参照は自動的に に変更されます C$2:C$4。

関連情報