見出し画像

タイムを管理し、速い順に並び替えて、チーム分けまでできる自動化ツールの作り方

さくさく🌸AI使って時短するExcel先生

今回作成した自動化ツールは、

徒競走タイム管理や運動会のチーム分け、100m走の走順を作りたい学校の先生…
顧客のデータ管理を徹底したいスポーツトレーナー…
職場で何かしらのタイムを管理し人事に生かしたい人事部…

の方々に使えるであろうものです♪
実際に自分で作ってみることで、かなりAI力、Excel力が上がりますので、今後の事務仕事で時短できること間違いなしです✨


簡単な「概要・使い方」と「作り方」をご紹介します！

概要・使い方

シートは２つあります。

「名簿＆記録」シート

学級、番号、名前、性別を入力します。
フリガナは関数で自動出力されます。

記録枠は3つです。増やすことも減らすことも可能です。
平均と最高タイムは自動で出力されます。

150人まで入力可能で、１、２、３学級までは条件付き書式で色が付きます。
学級が入ってない行は、罫線等見えなくしてあります。

「チーム分け」シート

平均か最高（記録）か選択して、データを抽出します。

青色は「平均」または「最高」を抽出したもので、
速い順にオレンジ色に出力しています。

このオレンジ色の記録をもとに、チームを2個か3個に分けます。
チーム分けは早い順に①、②、③に割り当てられます。

設定を付け加えれば、男女の偏りをなくしたり、指定の人同士を違うチームにしたりすることも可能です♪

作り方

枠組み

①名簿＆記録シート

・文字入れ
まずは、年組入力欄やタイトル、見出しを記入しましょう。

・罫線
【Shift+PgDn】×４で「153」まであっという間に選択できます！
この状態で【Alt→H→B→A】で格子状の罫線を付けられます。

マウスだとここからできます！

・情報入力
番号は基本１から連番ですので、【Ctrl】を押しながらオートフィルで一発入力できます♪
【Alt→H→F→I→S→Enter】でもOK！

名前と性別は今はダミーでいれておくのでChatGPTにお願いしてみましょう。

コピペできないテーブル形式の時は、もう一度…

50m走のタイムも入れてみます！

記録１，２，３のところを範囲選択して、セルの書式設定の「ユーザー定義」で「０．００」と入力すると「７．２」という表記も「７．２０」と下2桁で統一されて見やすくなります！

データを入れてやりやすくなってきたら色付けしてみましょう♪
・色付け
範囲を選択して【Ctrl+T】でテーブル化してしまいましょう。


これで、一瞬で見やすい色が付きました。
後は、「範囲に戻す」でテーブル解除すればOK！

同じやり方で、他も見やすくテーブル化して色付けし、解除していきましょう。

残りは関数や機能で解説します。

②チーム分けシート
もう一つのシートも同じように進めていきましょう。

・文字入れ
見出しやメモをまとめて入力しました。

・罫線
・色付け
同じやり方で罫線、色付けを行っていきます。

こちらはこのままでOKです。


関数・機能

では、より機能性を高めていきましょう。

まずは「名簿＆記録」シートから…

・AVERAGE

平均のところにAVERAGE関数を入れて平均を出していきます。
「＝aver」くらいまで入力したら候補が出てくるので、【Tab】キーでOK！

画像のようになったら、範囲を設定！

【Shift+矢印】でマウスを使わずに記録１～３を選択できたら【Enter】！

※便利技紹介！

この画像のように、矢印キーで範囲を選択しようとしたら、カーソル「｜」（縦棒と呼ぶのですが…）が動いちゃった！
って時。
【F2】を押してください。
これだけで入力モードと編集モードが切り替わり、矢印キーで範囲を選択できるようになりますよ！

あとは、下のセルにも反映させていきましょう！
やり方を二つご紹介！
僕は2つ目の方法でいつもやっています！

・下のセルにコピーする方法《1つ目》
範囲を選択して…【Shift＋↓】

【Ctrl+D】で下にコピー！
ただし、書式までコピーされて見づらくなっちゃった…。

青と白交互の見やすい列の書式をコピーします。
見た目が良いままの列を適当にコピー【Ctrl+C】して…

平均のところで、【Ctrl+Alt+V】で、形式を選択して貼り付けの「書式」【T】を選択してOK！

これで書式による見た目ももとにもどりました！

・下のセルにコピーする方法《2つ目》
先ほどと同じように範囲を選択します。【Shift+↓】

【F2】を押して、編集モード（文字を入力できる状態）にして、

【Ctrl+Enter】を押す！

これで書式は変わらずに、中身の関数だけが一括で入力されます！
背景色や罫線はコピーしたくないときは、こちらの方法がダントツ速くておすすめです♪

・IFERROR

AVERAGE関数はできましたが、このままだと、記録が入っていないときはエラーになったしまいます。

これを回避するために、IFERROR関数を使います。

やり方は簡単！
先ほどのAVERAGE関数を挟み込むように、IFERROR関数を入力します。

関数の前に、「ife」まで入力して、【Tab】でOK！
「=IFERROR(AVERAGE(F28:H28)」という状態になるので、

「値」はAVERAGE関数がこれに該当するので、「エラーの場合の値」を入力してあげます。
エラーの時に空白にしたい場合は、「””」ダブルクォーテーション２つ。ハイフン（－）など表示したい場合は、「”－”」と、ダブルクォーテーションの間に指定の文字を入れます。
今回は、空白処理します。

これで、先ほどエラー表記されていたところが空白になりスッキリしました！

これを、先ほどと同じように他のセルにも反映させます。


AVERAGE関数が入ってる範囲を指定して、先ほど入力したIFERROR付きのセルで【F2】からの【Ctrl+Enter】で完璧です！

・MIN

次に最高記録を抽出していきましょう。

記録のうち、一番速いタイム（数字的には小さい記録）を抽出します。

一人目の記録でいうと、「7.65」が抽出できたらいいですね。
それを実現するのが、MIN関数です。

使い方はAVERAGE関数と同じです。
「＝MIN」と入力し【Tab】。
その後範囲を【Shift＋矢印】で選択します。
これでEnterを押すと…

できましたね！
先ほどと同じように、範囲を選択して、

【F2】で編集モードにして、【Ctrl+Enter】。

これで最高記録の抽出列が完成しました！

ただし、このままだと記録が入っていないセルは「０」表記されてしまいますので、オプションで非表示にしていきましょう。

左上の「ファイル」から

左下の「オプション」へ

「詳細設定」の下の方に「ゼロ値のセルにゼロを表示する」のチェックを外します。

これで、このようにスッキリ表記することができました。

・PHONETIC

ここは、なくてもいい列ではありますが、簡単に存在だけでも知って欲しいので解説します。
現状フリガナが空欄のままです。
ChatGPTにお願いすればすぐにでも埋めることはできますが、名前が変わるたびに書き直さなきゃいけないのはめんどくさいですね。

ここは、関数の力で自動化していきましょう。
使うのがPHONETIC関数です。ただ、参照元のフリガナを出力するという単純な関数になります。
「＝pho」と入力して【Tab】を押して、

参照先である隣の名前を選択【←】（またはクリック）すればOK！

実際の名前を手入力したり、別のExcelファイルからコピペしている方はいい感じに出力されたと思いますが、ChatGPTにお願いして貼り付けた方はこのようになっているのではないでしょうか。

これは、「名前」の方の漢字にフリガナが認識されていないため起こります。なので、「名前」に一度フリガナがあることを自覚させましょう。

ここの「ふりがなの表示」を押しても、

空白のままです。だからPHONETIC関数も漢字をそのまま出力したんですね。
なので、やることとしては…「ふりがなの編集」です。

これで、一般的な読み方が上に表示されます。
読み方が良ければこれでOK！

これで、PHONETIC関数もふりがなを表示するようになりました。

あとは、今まで通り下までコピペしてください。

「名前」の「ふりがなの編集」はコピペできないので、
【Alt→H→G→E】【Enter】を高速で１５０回繰り返す必要があります。なので、ChatGPTでダミーデータを入れただけの人は今はやる必要はないです…。

・列幅調整

データが埋まってくると、列幅調整の必要性を感じてきたのではないでしょうか。
このような作業も一瞬で終わらせてしまいましょう！

【Ctrl+A】で全選択します。

【Alt→H→O→I】で、列幅自動調整を起動させます。
よく使うので、オルトでホイ♪と覚えてしまいましょう。

見栄えが良くなりましたね。
では、次に行きましょう！！



・IF

次に「チーム分け」シートの関数入力をしていきます。
「学級」見出しのところに「名簿＆記録」シートの「学級」の値を出力します。

やり方としては、単純に参照すればよいのですが…

「＝」を入力した状態で、隣のシートへ移動し【Ctrl+PgUp】

チーム分けシート

該当するセルを選択【矢印キー】（マウスクリック）します。

名簿＆記録シート

ただし、この方法だと、参照先に文字が入っていないと「０」表記になってしまいます。

先ほどと同じように「ゼロ値のセルにゼロを表示する」設定すれば消えはするのですが、後々設定する条件付き書式のために、この方法は使いません。

また、色々なやり方で見た目を整理する方法を知っていた方が自分に合った方法を選べるかと思いますので、今回は、IF関数を使った方法をやってみましょう♪

Sheet1の「A3」を参照していたセルに戻りまして、「＝IF」と入力し【Tab】で画像のような状態にします。

IF文は（条件を表す数式、条件を満たす場合、条件を満たさない場合）で構成されています。

言葉で表すと、
「条件を表す数式」として、Sheet1（名簿＆記録）の「A3」が空白だった場合…
「条件を満たすとき」、このSheet２（チーム分け）の「A3」も空白。
「条件を満たさないとき」、このSheet２にSheet１のA3の値を出力する（写す）。

これを数式として入力していきます。

まず「条件を表す数式」のために「＝」等号を使います。
【Sheet1のA3が空白の時】
画像の数式が上記の意味になります。

「条件を満たすとき」は、Sheet1が空白の時はここも空白にしたいので、
【空白】を表す「””」を入力します。

最後に「条件を満たさないとき」は、Sheet1が空白じゃなく、何かしらの文字が入っているとき…なので、このシートに、そのSheet１の情報をそのまま出力したい状況になります。
なので、最初に記入していた、参照をすればOKです。

これで、何か文字が入っているときは、Sheet１と同じものを出力できるようになりました。
番号も同じやり方でいいので、数式を書き込んでみましょう！

このような形の数式になっていて、30番まで出力されていればOKですね！

次に名前を出力していきます。
こちらも同じ方法で大丈夫ですのでやってみましょう！
実は、いちいち入力しなくてもあるコピペ方法を使えば一瞬で終わります！

今回のシートでは、Sheet１もSheet２も番号の隣に名前が入っているので、「B3」を参照していたものを「C3」にずらすだけでよくなります。

なので…番号の列を選択して【Shift＋Ctrl＋↓】

Ctrlを離して【Shift+→】で２列選択したら…

【Ctrl+R】で右にコピー！

一瞬で名前の表示が完了しました＾＾

では、いよいよ記録の入力です。
次から少しずつ難しくなります。
がんばっていきましょう！

まずは、このような自動化ツールを作るとき、ひたすら仮定を繰り返します。
学級数が２つなら、同じ名前の子がいたら、タイムが同じなら‥‥どんな状況でもバッチリ動くためには、このような思考が必要です。

そして今回、「平均記録と最高記録どっちでチーム分けをするか…」これを解決しつつ、記録を出力するように作っていきます。

一応お伝えしておくと、Excelの関数の組み合わせ方は人によりそれぞれです。
僕よりもっと簡単にシンプルに作り上げることができる人もいると思いますので、この方法が最善かどうかはわかりません。という点については、ご了承下さい。

・VLOOKUP

今回使うのは、VLOOKUP関数です。聞いたことある方は多いでしょう。
ただ、使い方がわからない！という方も多いでしょう。
簡単に説明すると、特定の文字から検索して、同じ行の文字列を引っ張ってきます。
例えば、出席番号１の子！と指定すると、１番の子の名前が出力されます。２番！と指定すれば、２番の子の名前が出力されます。

それでは、いつものように「＝vl」と途中まで入力したら【Tab】キー！

VLOOKUP関数は画像の通り、（検索値、検索値を探す範囲、その範囲の中
で何列目を出力するか、[検索方法]）の３つ（＋１）で構成されています。
[中かっこ]は、省略可能な部分（引数）ですので、ひとまず置いておきましょう。

今回は、「名前」を検索して、その子の記録を引っ張ってきたいです。
ひとまず、平均記録を出力するよう作ってみましょう。

隣の「C3」が名前に当たるので、「検索値」の部分として、選択して…
「検索値を探す範囲」を隣のSheet１で選択します。
「名前」と「平均の記録」どちらも入るように範囲を選択してください。

最後に、「その範囲の中で何列目を出力するか」は、”７列目”が平均記録に当たる列ですので、そのまま「７」と入力しましょう。

これで１番目の山本翔太くんの”7.945”という平均記録が…取れてないですね。VLOOKUP関数は、結構あいまいな答えを出してきます。
もっと厳格に！正しい物を出力しろよ！！！
と命令するのが、４つ目の省略可能と話していた「[検索方法]」になります。
「７」列目の後に「,」を付けると、次に入力するべき文字の候補が出てきます。

ここで、「完全一致」のものを出力せよ。と指示したいので「FALSE」を入力します。
これで…

正しい値が出ましたね。

基本的な形は、理解できたでしょうか？ここから、もう少し複雑になっていきます。

現状だと、「７」と直接条件である何列目かを指定しています。
そのため、「平均記録」しか出力されません。
今回は、「平均」も「最高」も選べるものを作ってみたいので…

・MATCH

このMATCH関数を使います。
まずはMATCH関数だけを理解していきましょう。

簡単に言うと、MATCH（検索値、検索したい値があるであろう範囲、[照合の種類]）を指定することで、指定した範囲の中で●番目に検索値があったよ！って探してくれるマッチング専用の関数です。

例えば、「７．９」秒のタイムの子は何番目にいる？とMATCH関数で求めて、８番目！と、分かれば、VLOOKUP関数で、８番目の子の名前を出力！
なんて組み合わせ技もできたりします。

実際にやってみましょう。
今回は、「平均」と「最高」のタイムが何番目にあるかを出力してもらいます。

「A1」セルに「平均」と入力しておいて、

どこか空いている適当なところに
「＝ma」で【Tab】！
ここの時短ワザは、もういいですね＾＾

検索範囲は、Sheet1の見出し部分（横１行）ですね。
このような形にして‥‥

省略可能ではあるものの、入力しておかないと厳格でないので、「０」を入力すると…

「９」が返ってきました。

確かにSheet１の２行目では、左から数えて９番目に「平均」という文字列がありますね！正しく機能してくれているようです！

「A1」に「最高」と文字を入れてみると…

ちゃんとSheet１の１０番目にあることを検索して「１０」と表示してくれましたね。

これで、MATCH関数を使うと、何番目にあるかを数字で出力してくれるという基本の仕組みは理解できたかと思います。

ここで、先ほどのVLOOKUP関数に戻ります！
さっきはこのような状態でした。

「７」列目という指示が、数字で行っていたため、わかりづらいし、変更がめんどくさいし…とよろしくない状態でした。
この、「７」の部分にさっきのMATCH関数の答え部分を入れてみると…

REFとエラーが出ました。
それはそうですね。VLOOKUP関数の「C3：I153」という指定した範囲の中には、「C,D,E,F,G,H,I」と７列しかなくて、１０列目はありませんからね…。

ということで、それぞれの関数の範囲を合わせていきます。
MATCH関数では、２行目全体。つまり、A列からXXD列（右端）まで選択されていました。
これを、CからJ列までに変更します。

すると当然、この範囲の中では、「最高」という文字列は８番目なので、「８」という数字を返します。

また、VLOOKUP関数の方での範囲も「最高」の部分が入っていないので、

同じくCからJ列の、データ部分を範囲としました。
すると…

山本翔太さんの８列目にある「最高」タイムである「７．６５」が表示されました👏！！！

現状、VLOOKUP関数の「列番号」を指定するところで、D1セルに出力したMATCH関数の答えを参照しているので、一つの関数にまとめていきましょう♪

MATCH関数の数式をそのままコピーして【Ctrl+A、Ctrl+C】

VLOOKUP関数にぶち込む！

もちろん「＝」は邪魔ですので消してください。
すると…

「最高」のタイムと

「平均」のタイムが「A1」のセルを変えるだけで、自在に抽出できるようになりました。

このままだと、空白や、誤字のときに「＃N/A」とエラーになってしまうため、ここにも一工夫加えていきます！

と、その前に…
いつものように下にコピーしてみたら大変なことに！？

何が起きたかというと…

正しい出力

エラー出力

中身を見てみると、「平均」「最高」を入力するのは、「A1」なのに「A3」に…
Sheet１の指定した範囲も全部少しずつずれてしまっています…。

このような数式は下にコピーすると基本的には、ズレた分だけ数式も参照を自動でずらしてくれちゃいます。便利なのですが、一つ手を加える必要があります。

それが、絶対参照という機能です。

絶対に範囲がズレては困る！という場所に「＄」をつけます。

やり方は簡単で、【F4】を押すだけ！

今回は、「C3」以外の３か所、「＄」をつけて絶対参照にしました。

この状態で下までコピーすると…

名前のあるところは無事うまくできました！

が、名前のないところはエラー表記されてしまいますね。
そんな時は！！！









そうですね、IFERROR関数です。
さっきのやり方を思い出して、つけてみましょう。


このような付け方ができていればOK！
これで下までコピーすると…エラー表記がなくなりました！

では、先ほど話に上がった、「A1」の「平均」「最高」をミスなく、そして簡単に入力できるように一工夫加えていきましょう。

・データの入力規則

「データの入力規則」という機能を使います。
これで、指定の文字のみを入力したり、指定範囲外の文字が入力されたときにエラー告知がされるようになります！

使い方も簡単です！
設定したい「A１」セルを選択した状態で、「データ」の「データの入力規則」をクリック！

開かれたウィンドウで、種類を「リスト」にし、元の値に「,」区切りで入力できるようにしたい文字列を入力します。

これで、プルダウンでこの２つのみを選択できるようになりました！

指定外の文字を入力するとこのようなエラー告知が出るため間違える心配がないですね！

・SMALL

次に、速い順に並び替えてあげたいとおもいます。
そのためにも、「平均」または「最高」記録からタイムの早い順。
すなわち、数字の小さい順に並び替える作業になります。

それができるのが、SMALL関数！
指定範囲内でN番目に小さい数。と指定することで該当する物が抽出されます。

この関数は、SMALL（範囲、順位）と2つのことを指定します。
今回も範囲は動かないで欲しいので、【F4】で「＄」をつけて絶対参照にしましょう！
そして、「順位」を1番とすると…

一番早い「6.9秒」という記録が抽出されました！

しかし皆さんお気づきの通り…
「順位」を直接「１」と入力しているため、下にコピーをしても…

こんなことに…。
なので、この「順位」の数字も自動で切り替わるように関数を使っていきましょう。

・ROW

順位をつけるといっても、1番上から1，2，3，4と連番になるように数字が入ればいいわけです。
そんな都合よく連番になってくれる関数がこのROW関数です。

この関数は、ただ「行番号」を出力する。
ただそれだけのために存在します。

＝ROW（B2）と入力すれば当然2行目なので、「２」と返してくれます。
＝ROW（）と引数を指定しないと、このROW関数が記入されたセルの行番号を数字で返してくれます。

この特性を使うことで、全セルに「＝ROW」と入力しても

10行目は「１０」、11行目は「１１」…と連番のようになってくれます！

先ほどのSMALL関数の「順位」を「１」と指定したところに

ROW関数を入れてみると…

先ほどとは違う記録になりました。
そりゃあそうですね。

このセル3行目ですから。
ROW()は「３」となってしまい、3番目に早い記録が出力されてしまったわけですね。

「１」という数字としてこのセルでは入って欲しいので、少し計算をします。
3行目の「３」が「１」になるには…


そう。２を引けばいいですね。
関数でもこのやり方でOKです！

「ROW（）－２」とすることで、「１」という計算になり、

最初と同じように「6.9秒」が出力されましたね！
今度こそ、ROW関数のおかげで行数に応じて連番になってくれるので、下にコピー（オートフィル）してみると…

できました！！
が、またまた空欄に対してエラーがでてしまったので、対処します。

IFERROR関数ですね＾＾

これでばっちりです！
記録は早い順に表示することができました！

では次に、この記録を出した子の名前も出力していきましょう！
VLOOKUP関数で…

右側にないからできない！！

ってことで他の方法を出しましょう。

バージョン2019以降をご利用の方は、「XLOOKUP関数」という右も左も関係なく使える便利な関数があるので、そちらをご利用ください！
それ以前のバージョンの方は、残念ながら今回最難関の関数を使って対応していきます。

・INDEX

それでは、いよいよ超難関部分に行きたいと思います。
INDEX関数は、指定した範囲の中で、X列目、Y行目をしていして、交差して重なる部分を抽出してくれる関数です。

INDEX（この範囲の , 2行目 , 3列目）
のように入力すると、「F」が返ってくる。

（※「F」のところにも同じ数式が入っています。）

実際に使ってみましょう。
今回は「名前」さえ出力されればよいので、配列（参照）は、C列になります。

この後、「何行目」するのですが、何行目か探すのもその行数を入力するのも他の関数に任せてしまいましょう！

特定の文字数字を検索して、何番目（何行目）にあるよ！
と見つけてその数字を返してくれる関数ありましたね！

そうです、MATCH関数です！

MATCH関数で、速い順に並び替えられた記録を検索して、D列の何行目にあるかさがしてもらいましょう！

MATCHは（検索値、検索する範囲、[検索方法]）でしたね！

ひとまず空いているところに関数を入れてみます。
速い順に並び替えた「F列」を検索します。
検索範囲は、「D列」の記録からですね。
これで、D列の記録の中で何番目に、F列の記録があるかがわかります。


21番目なようです！
確かに、21番目ですね！「高橋愛」さんです。

先ほどのINDEX関数の「何行目」かにこの「21」という数字を入れると…

バッチリ出ました！

「何列目」かは省略可能ですので、
INDEX関数にMATCH関数をいれて下までコピーするとできました！

ただ…このままだと同じタイムの人がいた場合、上の人の名前だけが複数回抽出されてしまっています…。
ここでもう一工夫必要ですね。

やりたいこととしては、

・速い順になっているタイム（F列）を、番号順に並んでいるタイム（D列）から探す。
・F列のタイムで同じのがないか確認する。（登場が1回目かどうか）
・2回目以降であれば、それに準じてD列からそのタイムを探す。

というところでしょうか。
ここまで複雑になると、僕でもAIに頼んだ方が早いです。

頼んだ結果出てきた関数がこちら…

=IF(F4="", "", IFERROR(INDEX($C$4:$C$153, SMALL(IF($D$4:$D$153=F4, ROW($D$4:$D$153)-ROW($D$4)+1), COUNTIF($F$4:F4, F4))), ""))

重要ポイント
この数式は配列数式です。数式を入力した後、Ctrl + Shift + Enter を押して配列数式として入力してください。これにより、IF と SMALL が配列内で正しく動作します。

このようなことも伝えられました。
配列数式というものですね。
確かに、この関数を入力すると、同じタイムの子もちゃんと名前が出力されました！

この関数の中身を具体的に学びたい方はこのまま読んでみてください！
これはちょっときつい！という方は次の見出しまでスキップ！！（目印あります）

「解説して」と伝えるだけで、このようにChatGPTが教えてくれました！

  1. IF(F4="", "", ...):

    • まず、この IF 文は、セル F4 が空白かどうかをチェックします。

    • もし F4 が空の場合、空白 ("") を返します。

    • F4 が空でない場合、次の条件式を実行します。

  2. IFERROR(..., ""):

    • この IFERROR は、エラーが発生した場合に空白 ("") を返すために使用されています。エラーが発生しなければ、INDEX 関数の結果を返します。

  3. INDEX($C$4:$C$153, SMALL(...)):

    • INDEX 関数は、範囲 $C$4:$C$153 の中から特定の行番号に対応する値を返します。

    • 行番号は、次に解説する SMALL 関数によって決定されます。

  4. SMALL(IF($D$4:$D$153=F4, ROW($D$4:$D$153)-ROW($D$4)+1), COUNTIF($F$4:F4, F4)):

    • IF 関数内で、範囲 $D$4:$D$153 が F4 に一致するかどうかをチェックしています。

    • 一致する行があれば、その行番号を計算しています。具体的には、ROW($D$4:$D$153)-ROW($D$4)+1 により、行番号を基準からの相対値として取得します。

    • 例えば、もし D5 が F4 と一致する場合、結果は 2 となり、C 列の2行目に対応する値を取得することができます。

  5. COUNTIF($F$4:F4, F4):

    • この COUNTIF 関数は、現在までの F 列のセルの中で F4 と同じ値が何回出現しているかを数えます。

    • この結果を基に、SMALL 関数がその一致する行の中から何番目の値を取り出すかを決定します。

    • 例えば、F4 が D 列の中に2回目に出現する場合、SMALL 関数は2番目の該当行を返します。

動作の流れ

  • F4 が空白なら、空白を返します。

  • そうでなければ、D4:D153 の中から F4 と一致する値を持つ行を探し、その行番号を基に C4:C153 の対応するデータを抽出します。

  • COUNTIF によって、同じ値が複数回出現している場合には、SMALL 関数で順番に処理され、最初の出現、2番目の出現...という順に対応するデータを返します。


◆
◆
◆
◆
◆
スキップ終了！！！
◆
◆
◆
◆
◆

お疲れさまでした。急に難易度が上がってしまいましたね。
今回は、AI練習もかねて関数メインで作成していますが、この記事を書いている途中にシンプルバージョンも思いついたので少しだけ書いておきます。

シンプルVer.

【Ctrl+Shift＋L】でフィルターを付けます。

記録のところで「昇順」にします。

できました＾＾

ただ、表を並び替えただけなので、書式（見た目）もぐちゃぐちゃになります。
また、「平均」・「最高」を変えるたびに、このフィルターでの操作が必要になります。
（「最高」記録にするとタイム順がバラバラ…）

また、行全体が並び替えられてしまうので、この後マクロで作るチーム分けの表示場所もずらしたり別シートに作る必要があったりするのであまりよろしくありません…

できるだけ手動の操作をなくすためにも、Excel理解を深めるためにも、関数での作成が個人的にはお勧めです！

ではでは、チーム分け機能を付ける前に、最後に見栄えをよくする「条件付き書式」を設定していきましょう。

・条件付き書式

現状、Sheet1（名簿＆記録）もSheet2（チーム分け）も一番下の名前から下は空欄のままです。

Sheet1
Sheet2

これが153行目まで続いています。

僕はこの状態が気になってしまうタイプなので、文字が入っていない行は見えなくなってもらいましょう。

範囲を選択したら、

条件付き書式の「新しいルール」

今回「A2:J153」という範囲で、現在「A2」がアクティブセル（指定範囲の中で暗くなってない）ですので、このセル値を数式に使います。

「A2」が「””」空白だったら…
「＝A２＝””」
これだけです。

ただし、今回は「A列」判断で1行丸ごと消えてほしいので、
「＄A２」とAの前にだけ絶対参照で出てきた「＄」をつけます。

参照についてわからない！という方はこちらでより詳しく解説しているのでご覧ください！

あとは、書式を見えない風に設定します。
罫線をなくして、背景色も色なしにして…

完成！

今後2組や転校生などで31番の子が増えたら、文字を入力するだけで、表の書式（見栄え）が整っていきます！


それではお待たせしました！
いよいよマクロを使ってチーム分けをしていきましょう。

マクロ

今回はなぜ関数ではなくマクロを使うのかというと…理由は2つ。

・関数だとチーム分けした後に手動で微調整できないから。（関数が消えてしまう）
・2チーム、3チームと分けれるように設定するには関数が複雑すぎて重くなるから（少し）

まぁ、1つ目の理由が大半を占めています。
また、ChatGPTに頼めば、マクロも自分で書かなくていいので、誰でもできてしまいます♪

こんな風に頼んでみると…あっという間に出してくれました！

コードの解説も付けてくれています。

また、やり方も教えてくれました♪

マクロの使用方法

VBAエディタを開く
　Excelで Alt + F11 を押して、VBAエディタを開きます。
新しいモジュールを挿入
　メニューから「挿入」→「標準モジュール」を選択します。
コードを貼り付ける
　開いたモジュールに、上記のコードをコピーして貼り付けます。
マクロを実行
　VBAエディタを閉じてExcelに戻ります。
　Alt + F8 を押して、「SplitNamesIntoTwoTeams」を選択し、「実行」をクリックします。

この通りにやってみますね。
【Alt+F11】を押して…
挿入の標準モジュールをクリックして…

コードを貼り付けて【Ctrl+V】

【Alt+F8】でマクロを開いて…
実行！！！

おおお～名前がちゃんと入っています！
速い人順に交互にチームに配置されていますね！

記録も名前と一緒に出力は可能ですが、後々チームメンバーを変更したときに記録も自動で変わるように、個々は関数を入れておきます。

VLOOKUP関数覚えていますかね！？
（検索する値、検索する範囲、その中の何列目を出力するか…）
でしたね！

これでチームメンバーを微調整しても記録は自動で、正しい物に更新されます！

あとは、ChatGPTに頼んで見栄えをよくしたり、3チームバージョンも作ってもらいましょう。

こんなお願いをしてみました↓

・チームごとに見やすくなるように色と罫線を付けて
・チーム分けの下にタイムの合計、人数、平均の枠と関数を入れて
・3チーム分けバージョンも作って

そんなこんなで、お願いしたり修正したりを繰り返して完成したコードがコチラ！

2チーム分け用

Sub SplitTeams2()
    Dim i As Integer
    Dim team1Row As Integer
    Dim team2Row As Integer
    Dim lastRow As Integer
    Dim summaryRow As Integer
    
    ' H3:M最終行までクリア
    lastRow = Cells(Rows.Count, 8).End(xlUp).Row ' H列の最終行を取得
    If lastRow >= 3 Then
        Range("H3:M" & lastRow).Clear
    End If
    
    team1Row = 3 ' H列の開始行
    team2Row = 3 ' J列の開始行
    
    ' E列の見た目上の最終行を取得
    lastRow = Cells(Rows.Count, 5).End(xlUp).Row
    For i = lastRow To 3 Step -1
        If Cells(i, "E").Value = "" Or Cells(i, "E").Value = 0 Then
            lastRow = lastRow - 1
        End If
    Next
    ' 各チームに割り当て
    For i = 3 To lastRow ' E3からE列の最終行まで処理
        If (i - 3) Mod 2 = 0 Then
            ' チーム1に割り当て (H列)
            Range("H" & team1Row).Value = Range("E" & i).Value
            team1Row = team1Row + 1
        Else
            ' チーム2に割り当て (J列)
            Range("J" & team2Row).Value = Range("E" & i).Value
            team2Row = team2Row + 1
        End If
    Next i
    
    ' 合計、人数、平均の行を追加
    summaryRow = Application.WorksheetFunction.Max(team1Row, team2Row)
   
    ' 合計
    Range("H" & summaryRow).Value = "合計"
    Range("J" & summaryRow).Value = "合計"
    Range("I" & summaryRow).Formula = "=SUM(I3:I" & summaryRow - 1 & ")"
    Range("K" & summaryRow).Formula = "=SUM(K3:K" & summaryRow - 1 & ")"
    
    ' 人数
    Range("H" & summaryRow + 1).Value = "人数"
    Range("J" & summaryRow + 1).Value = "人数"
  　Range("I" & summaryRow + 1).Formula = "=COUNTA(I3:I" & summaryRow - 1 & ") - COUNTBLANK(I3:I" & summaryRow - 1 & ")"
　　Range("K" & summaryRow + 1).Formula = "=COUNTA(K3:K" & summaryRow - 1 & ") - COUNTBLANK(K3:K" & summaryRow - 1 & ")"

    ' 平均
    Range("H" & summaryRow + 2).Value = "平均"
    Range("J" & summaryRow + 2).Value = "平均"
    Range("I" & summaryRow + 2).Formula = "=AVERAGE(I3:I" & summaryRow - 1 & ")"
    Range("K" & summaryRow + 2).Formula = "=AVERAGE(K3:K" & summaryRow - 1 & ")"
    
    ' 罫線と背景色の設定（合計、人数、平均の行）
    With Range("H" & summaryRow & ":K" & summaryRow + 2)
        .Borders.LineStyle = xlContinuous
        .Interior.Color = RGB(255, 242, 204) ' 薄黄色
    End With
    
    ' 列ごとに色付け（H列: 薄緑, J列: 薄赤）
    With Range("H3:H" & summaryRow - 1)
        .Interior.Color = RGB(226, 239, 218) ' 薄緑
        .Borders.LineStyle = xlContinuous
    End With
    
    With Range("J3:J" & summaryRow - 1)
        .Interior.Color = RGB(252, 228, 214) ' 薄赤
        .Borders.LineStyle = xlContinuous
    End With
    
    ' VLOOKUP関数をI列、K列に挿入
    For i = 3 To summaryRow - 1
        Range("I" & i).Formula = "=IFERROR(VLOOKUP(H" & i & ",E:F,2,FALSE),"""")"
        Range("K" & i).Formula = "=IFERROR(VLOOKUP(J" & i & ",E:F,2,FALSE),"""")"
    Next i
    
    ' I列、K列に灰色と罫線を付ける
    With Union(Range("I3:I" & summaryRow - 1), Range("K3:K" & summaryRow - 1))
        .Interior.Color = RGB(210, 210, 210) ' 灰色
        .Borders.LineStyle = xlContinuous
    End With
End Sub

3チーム分け用


Sub SplitTeams3()
    Dim i As Integer
    Dim team1Row As Integer
    Dim team2Row As Integer
    Dim team3Row As Integer
    Dim lastRow As Integer
    Dim summaryRow As Integer
    
    ' H3:M最終行までクリア
    lastRow = Cells(Rows.Count, 8).End(xlUp).Row ' H列の最終行を取得
    If lastRow >= 3 Then
        Range("H3:M" & lastRow).Clear
    End If
    
    team1Row = 3 ' H列の開始行
    team2Row = 3 ' J列の開始行
    team3Row = 3 ' L列の開始行
    
    ' E列の見た目上の最終行を取得
    lastRow = Cells(Rows.Count, 5).End(xlUp).Row
    For i = lastRow To 3 Step -1
        If Cells(i, "E").Value = "" Or Cells(i, "E").Value = 0 Then
            lastRow = lastRow - 1
        End If
    Next
    ' 各チームに割り当て
    For i = 3 To lastRow ' E3からE列の最終行まで処理
        If (i - 3) Mod 3 = 0 Then
            ' チーム1に割り当て (H列)
            Range("H" & team1Row).Value = Range("E" & i).Value
            team1Row = team1Row + 1
        ElseIf (i - 3) Mod 3 = 1 Then
            ' チーム2に割り当て (J列)
            Range("J" & team2Row).Value = Range("E" & i).Value
            team2Row = team2Row + 1
        Else
            ' チーム3に割り当て (L列)
            Range("L" & team3Row).Value = Range("E" & i).Value
            team3Row = team3Row + 1
        End If
    Next i
    
    ' 合計、人数、平均の行を追加
    summaryRow = Application.WorksheetFunction.Max(team1Row, team2Row, team3Row)
   
      ' 合計
    Range("H" & summaryRow).Value = "合計"
    Range("J" & summaryRow).Value = "合計"
    Range("L" & summaryRow).Value = "合計"
    Range("I" & summaryRow).Formula = "=SUM(I3:I" & summaryRow - 1 & ")"
    Range("K" & summaryRow).Formula = "=SUM(K3:K" & summaryRow - 1 & ")"
    Range("M" & summaryRow).Formula = "=SUM(M3:M" & summaryRow - 1 & ")"
    
    ' 人数
    Range("H" & summaryRow + 1).Value = "人数"
    Range("J" & summaryRow + 1).Value = "人数"
    Range("L" & summaryRow + 1).Value = "人数"
    Range("I" & summaryRow + 1).Formula = "=COUNTA(I3:I" & summaryRow - 1 & ") - COUNTBLANK(I3:I" & summaryRow - 1 & ")"
　　Range("K" & summaryRow + 1).Formula = "=COUNTA(K3:K" & summaryRow - 1 & ") - COUNTBLANK(K3:K" & summaryRow - 1 & ")"
　　Range("M" & summaryRow + 1).Formula = "=COUNTA(M3:M" & summaryRow - 1 & ") - COUNTBLANK(M3:M" & summaryRow - 1 & ")"

    
    ' 平均
    Range("H" & summaryRow + 2).Value = "平均"
    Range("J" & summaryRow + 2).Value = "平均"
    Range("L" & summaryRow + 2).Value = "平均"
    Range("I" & summaryRow + 2).Formula = "=AVERAGE(I3:I" & summaryRow - 1 & ")"
    Range("K" & summaryRow + 2).Formula = "=AVERAGE(K3:K" & summaryRow - 1 & ")"
    Range("M" & summaryRow + 2).Formula = "=AVERAGE(M3:M" & summaryRow - 1 & ")"
    
    ' 罫線と背景色の設定（合計、人数、平均の行）
    With Range("H" & summaryRow & ":M" & summaryRow + 2)
        .Borders.LineStyle = xlContinuous
        .Interior.Color = RGB(255, 242, 204) ' 薄黄色
    End With
    
    ' 列ごとに色付け（H列: 薄緑, J列: 薄赤, L列: 薄青）
    With Range("H3:H" & summaryRow - 1)
        .Interior.Color = RGB(226, 239, 218) ' 薄緑
        .Borders.LineStyle = xlContinuous
    End With
    
    With Range("J3:J" & summaryRow - 1)
        .Interior.Color = RGB(252, 228, 214) ' 薄赤
        .Borders.LineStyle = xlContinuous
    End With
    
    With Range("L3:L" & summaryRow - 1)
        .Interior.Color = RGB(221, 235, 247) ' 薄青
        .Borders.LineStyle = xlContinuous
    End With
    
    ' VLOOKUP関数をI列、K列、M列に挿入
    For i = 3 To summaryRow - 1
        Range("I" & i).Formula = "=IFERROR(VLOOKUP(H" & i & ",E:F,2,FALSE),"""")"
        Range("K" & i).Formula = "=IFERROR(VLOOKUP(J" & i & ",E:F,2,FALSE),"""")"
        Range("M" & i).Formula = "=IFERROR(VLOOKUP(L" & i & ",E:F,2,FALSE),"""")"
    Next i
    
    ' I列、K列、M列に灰色と罫線を付ける
    With Union(Range("I3:I" & summaryRow - 1), Range("K3:K" & summaryRow - 1), Range("M3:M" & summaryRow - 1))
        .Interior.Color = RGB(210, 210, 210) ' 灰色
        .Borders.LineStyle = xlContinuous
    End With
End Sub

そしてできたチーム分けがこちら！

職場で共有しても十分なものが完成しました＾＾

最後に…
こんなボタンも作りたい！という方は、ラストスパート頑張ってください！

・ボタン作り

開発タブから「挿入」で左上のボタンを選択します。

「開発タブ」がないよ！という方は、オプションの「リボンのユーザー設定で「開発」に☑チェックを入れて、OK！

ボタンをテキストボックスのように、マウスのクリック＆ドラッグで作成します。

すると、このボタンにどのマクロを登録するのか出てくるので、先ほど作ったコードのタイトルを選択します。

ボタンができました！
右クリックで名前を変えましょう！

同じようにもう一つ作れば…

完成！！

どちらもちゃんと機能してくれました！






ここまで本当にお疲れさまでした！
かなりボリューミーでしたが、その分皆さんのAI×Excel力は確実に上がっています！
この自動化ツールとAI×Excel力で仕事時短できること間違いなしです♪

この下に配布データや個別レッスン、SNSなどの情報を載せていますのでぜひご覧ください！

本当にお疲れさまでした＾＾


配布データ

こちらからデータのダウンロードをしてください。

もちろんそのまま使用しても構いませんが、自分用にアレンジしたり、何かバグが発生したときに対応できる力を身に付けるためにも、自分で手を動かしてみることをおススメします。


答え合わせにも使えますので、ぜひご利用ください。


また、このような自動化ツール作成のレッスンやExcel力診断＆悩み相談というものの、応募者募集中です♪

自分用にカスタマイズしたいけどどうしたらいいの？
もっとExcel作業を時短したい！
事務仕事を楽しくできるようにしたい！
職場で評価されたい！

という方は、ぜひご参加ください＾＾
もちろん無料です！

自力で作成された方、大変お疲れさまでした！
かなりのExcel力がついたかと思いますので自信をもって事務仕事を楽しんじゃってください！

他にも作って欲しい解説してほしいものがありましたら、ご連絡ください♪

また、個別でレッスンも受付中です！お気軽にDMください！

この記事が気に入ったらサポートをしてみませんか？