自分で作る簡単 ビンゴシート① ツール(EXCEL版)
=====================================================
【作成方法】
①EXCELを新規で作成する。
②青いバーの下に書かれている「入力＆ビンゴ」の行から
青いバーの上の行までコピーする。
（１）青いバーの下から青いバーの上までを選択する。
（２）選択した状態で、マウスの右クリック後、コピーを押す
③EXCELのA1に②でコピーした物を貼る。
（１）EXCELシートのA1を選択する。
（２）マウス右クリック後、貼り付けを押す。
※もし貼り付けた内容が数式のままの場合は、下記の操作を行って下さい。
1:Ａ列を選択する。
2:EXCEL上のデータ ⇒ 区切り位置を選択
3:[カンマやタブなどの区切り文字によってフィールドごとに区切られたデータ]
を選択した状態で【次へ】を押す。
4:タブとスペースにチェックを入れて【完了】を押す
④下の図になったら、EXCELを保存する。
入力&ビンゴ
入力&ビンゴ
入力&ビンゴ
入力&ビンゴ
入力&ビンゴ
入力&ビンゴ
マークシート記入内容
1通り目 =IF($B$1="","",$B$1) =IF($C$1="","",$C$1) =IF($D$1="","",$D$1)
=IF($E$1="","",$E$1) =IF($F$1="","",$F$1) =IF($G$1="","",$G$1)
=IF($G$6="","",IF((COUNTIF(B9:G9,B9))+(COUNTIF(B9:G9,C9))+(COUNTIF(B9:G9,D9))+(COUNTIF(B9:G9,E9))+(COUNTIF(B9:G9,F9))+(COUNTIF(B9:G9,G9))<=6,"","←重複してます"))
2通り目 =IF($B$2="","",$B$2) =IF($C$2="","",$C$2) =IF($D$2="","",$D$2)
=IF($E$2="","",$E$2) =IF($F$2="","",$F$2) =IF($G$2="","",$G$2)
=IF($G$6="","",IF((COUNTIF(B10:G10,B10))+(COUNTIF(B10:G10,C10))+(COUNTIF(B10:G10,D10))+(COUNTIF(B10:G10,E10))+(COUNTIF(B10:G10,F10))+(COUNTIF(B10:G10,G10))<=6,"","←重複してます"))
3通り目 =IF($B$3="","",$B$3) =IF($C$3="","",$C$3) =IF($D$3="","",$D$3)
=IF($E$3="","",$E$3) =IF($F$3="","",$F$3) =IF($G$3="","",$G$3)
=IF($G$6="","",IF((COUNTIF(B11:G11,B11))+(COUNTIF(B11:G11,C11))+(COUNTIF(B11:G11,D11))+(COUNTIF(B11:G11,E11))+(COUNTIF(B11:G11,F11))+(COUNTIF(B11:G11,G11))<=6,"","←重複してます"))
4通り目 =IF($B$4="","",$B$4) =IF($C$4="","",$C$4) =IF($D$4="","",$D$4)
=IF($E$4="","",$E$4) =IF($F$4="","",$F$4) =IF($G$4="","",$G$4)
=IF($G$6="","",IF((COUNTIF(B12:G12,B12))+(COUNTIF(B12:G12,C12))+(COUNTIF(B12:G12,D12))+(COUNTIF(B12:G12,E12))+(COUNTIF(B12:G12,F12))+(COUNTIF(B12:G12,G12))<=6,"","←重複してます"))
5通り目 =IF($B$5="","",$B$5) =IF($C$5="","",$C$5) =IF($D$5="","",$D$5)
=IF($E$5="","",$E$5) =IF($F$5="","",$F$5) =IF($G$5="","",$G$5)
=IF($G$6="","",IF((COUNTIF(B13:G13,B13))+(COUNTIF(B13:G13,C13))+(COUNTIF(B13:G13,D13))+(COUNTIF(B13:G13,E13))+(COUNTIF(B13:G13,F13))+(COUNTIF(B13:G13,G13))<=6,"","←重複してます"))
6通り目 =IF($B$6="","",$B$6) =IF($C$6="","",$C$6) =IF($D$6="","",$D$6)
=IF($E$6="","",$E$6) =IF($F$6="","",$F$6) =IF($G$6="","",$G$6)
=IF($G$6="","",IF((COUNTIF(B14:G14,B14))+(COUNTIF(B14:G14,C14))+(COUNTIF(B14:G14,D14))+(COUNTIF(B14:G14,E14))+(COUNTIF(B14:G14,F14))+(COUNTIF(B14:G14,G14))<=6,"","←重複してます"))
7通り目 =IF($B$1="","",$B$1) =IF($B$2="","",$B$2) =IF($B$3="","",$B$3)
=IF($B$4="","",$B$4) =IF($B$5="","",$B$5) =IF($B$6="","",$B$6)
=IF($G$6="","",IF((COUNTIF(B15:G15,B15))+(COUNTIF(B15:G15,C15))+(COUNTIF(B15:G15,D15))+(COUNTIF(B15:G15,E15))+(COUNTIF(B15:G15,F15))+(COUNTIF(B15:G15,G15))<=6,"","←重複してます"))
8通り目 =IF($C$1="","",$C$1) =IF($C$2="","",$C$2) =IF($C$3="","",$C$3)
=IF($C$4="","",$C$4) =IF($C$5="","",$C$5) =IF($C$6="","",$C$6)
=IF($G$6="","",IF((COUNTIF(B16:G16,B16))+(COUNTIF(B16:G16,C16))+(COUNTIF(B16:G16,D16))+(COUNTIF(B16:G16,E16))+(COUNTIF(B16:G16,F16))+(COUNTIF(B16:G16,G16))<=6,"","←重複してます"))
9通り目 =IF($D$1="","",$D$1) =IF($D$2="","",$D$2) =IF($D$3="","",$D$3)
=IF($D$4="","",$D$4) =IF($D$5="","",$D$5) =IF($D$6="","",$D$6)
=IF($G$6="","",IF((COUNTIF(B17:G17,B17))+(COUNTIF(B17:G17,C17))+(COUNTIF(B17:G17,D17))+(COUNTIF(B17:G17,E17))+(COUNTIF(B17:G17,F17))+(COUNTIF(B17:G17,G17))<=6,"","←重複してます"))
10通り目 =IF($E$1="","",$E$1) =IF($E$2="","",$E$2) =IF($E$3="","",$E$3)
=IF($E$4="","",$E$4) =IF($E$5="","",$E$5) =IF($E$6="","",$E$6)
=IF($G$6="","",IF((COUNTIF(B18:G18,B18))+(COUNTIF(B18:G18,C18))+(COUNTIF(B18:G18,D18))+(COUNTIF(B18:G18,E18))+(COUNTIF(B18:G18,F18))+(COUNTIF(B18:G18,G18))<=6,"","←重複してます"))
11通り目 =IF($F$1="","",$F$1) =IF($F$2="","",$F$2) =IF($F$3="","",$F$3)
=IF($F$4="","",$F$4) =IF($F$5="","",$F$5) =IF($F$6="","",$F$6)
=IF($G$6="","",IF((COUNTIF(B19:G19,B19))+(COUNTIF(B19:G19,C19))+(COUNTIF(B19:G19,D19))+(COUNTIF(B19:G19,E19))+(COUNTIF(B19:G19,F19))+(COUNTIF(B19:G19,G19))<=6,"","←重複してます"))
12通り目 =IF($G$1="","",$G$1) =IF($G$2="","",$G$2) =IF($G$3="","",$G$3)
=IF($G$4="","",$G$4) =IF($G$5="","",$G$5) =IF($G$6="","",$G$6)
=IF($G$6="","",IF((COUNTIF(B20:G20,B20))+(COUNTIF(B20:G20,C20))+(COUNTIF(B20:G20,D20))+(COUNTIF(B20:G20,E20))+(COUNTIF(B20:G20,F20))+(COUNTIF(B20:G20,G20))<=6,"","←重複してます"))
13通り目 =IF($B$1="","",$B$1) =IF($C$2="","",$C$2) =IF($D$3="","",$D$3)
=IF($E$4="","",$E$4) =IF($F$5="","",$F$5) =IF($G$6="","",$G$6)
=IF($G$6="","",IF((COUNTIF(B21:G21,B21))+(COUNTIF(B21:G21,C21))+(COUNTIF(B21:G21,D21))+(COUNTIF(B21:G21,E21))+(COUNTIF(B21:G21,F21))+(COUNTIF(B21:G21,G21))<=6,"","←重複してます"))
14通り目 =IF($G$1="","",$G$1) =IF($F$2="","",$F$2) =IF($E$3="","",$E$3)
=IF($D$4="","",$D$4) =IF($C$5="","",$C$5) =IF($B$6="","",$B$6)
=IF($G$6="","",IF((COUNTIF(B22:G22,B22))+(COUNTIF(B22:G22,C22))+(COUNTIF(B22:G22,D22))+(COUNTIF(B22:G22,E22))+(COUNTIF(B22:G22,F22))+(COUNTIF(B22:G22,G22))<=6,"","←重複してます"))
【使い方】
※下の図の様に、罫線を引いたら使いやすいです。
①EXCELのB1～G6に1～43の数字を適当に重複しない様に入力すると
マークシート記入内容に値が入っていきます。
②G6を入力した時点で、H9～H22に重複している物があれば表示されます。
例では、29を重複させています。
=====================================================
【抽選結果確認】
作成したEXCELをコピーして抽選結果用に使用する
抽選数字が該当する入力エリアの数字を★マークに変更すると
該当通り目が★マークに代わるので見やすいです。
