一覧表から抽出？

質問

皆さんいつもアドバイスありがとうございます。
また今回も宜しくお願い致します。
エクセルの2003を使用しています。

図のような一覧表があり、その中にお客様から指定された時間を入力しています。
この一覧表をソート？し、左側のB列に指定時間の早い順に、A列にはそれに対応する部屋番号を抽出表示させたいのです。
このような事は可能でしょうか？

回答No.5

tom04

No.4です。
補足を読ませていただきました。
データ量がもっと多く、また日付が入っても対応できるように！というご希望なので・・・

無理矢理って感じになりますが、↓の画像のように作業用の列をSheet2に作成するようにしてみました。
今回は作業用の列を3列使っています。

とりあえずSheet1の1行目～12行目・E列～L列までのデータの場合としています。
（データ範囲は適宜変更してみてください。）
尚、日付セルはシリアル値ではなく単純に18日であれば「18」という数値だけを入力するとします。
（シリアル値にすると作業列の数式を変える必要があります）
セルの表示形式は　ユーザー定義から　0日 としておきます。
これで日付セルに　「18」のように数値だけを入力すると「18日」と表示されます。

Sheet2の作業列A2セルに
=IF(COUNTIF(Sheet1!$E$1:$L$12,">1000")<ROW(A1),"",LARGE(Sheet1!$E$1:$L$12,ROW(A1)))

B2セルに
=IF(A2="","",INDEX(Sheet1!$E$1:$L$12,SUMPRODUCT((Sheet1!$E$1:$L$12=A2)*ROW($A$1:$A$12))+3,SUMPRODUCT((Sheet1!$E$1:$L$12=A2)*COLUMN($A$1:$H$1)))+INDEX(Sheet1!$E$1:$L$12,SUMPRODUCT((Sheet1!$E$1:$L$12=A2)*ROW($A$1:$A$12))+2,SUMPRODUCT((Sheet1!$E$1:$L$12=A2)*COLUMN($A$1:$H$1))))

C2セルに
=IF(OR(B2="",B2=0),"",COUNTIF(B:B,">"&B2)+COUNTIF($B$2:B2,B2))

という数式を入れ、A2～C2セルを範囲指定　→　C2セルのフィルハンドルでずぃ～～～！っと下へコピーしておきます。

そしてSheet1のA1セルに
=IF(COUNT(Sheet2!C:C)<ROW(A1),"",INDEX(Sheet2!A:A,MATCH(LARGE(Sheet2!C:C,ROW(A1)),Sheet2!C:C,0)))

B1セルに
=IF(A1="","",TEXT(INT(INDEX(Sheet2!B:B,MATCH(LARGE(Sheet2!C:C,ROW(A1)),Sheet2!C:C,0))),"0日"))

C1セルに
=IF(A1="","",TEXT(MOD(INDEX(Sheet2!B:B,MATCH(LARGE(Sheet2!C:C,ROW(A1)),Sheet2!C:C,0)),1),"h:mm"))

という数式を入れ、A1～C1セルを範囲指定し、C1セルのフィルハンドルで下へコピーすると
画像のような感じになります。

以上、かなり強引な方法ですが他に良い方法があればごめんなさいね。m(_ _)m

回答No.4

tom04

こんばんは！
お示しの配置ですと抽出も大変になるかと思いますので、一例ですが、
↓の画像のように1列に表示させます。
尚、範囲はお示しの画像通りとします。

作業列1のI2セルに
=INDEX($D$1:$G$12,INT((ROW(A4)/4)-1)*4+1,MOD(ROW(A1)-1,4)+1)

作業列2のJ2セルに
=INDEX($D$1:$G$12,SUMPRODUCT(($D$1:$G$12=I2)*ROW($A$1:$A$12))+2,SUMPRODUCT(($D$1:$G$12=I2)*COLUMN($A$1:$D$1)))

という数式を入れ、I2・J2を範囲指定　→　J2セルのフィルハンドルで12行分下へコピーしておきます。
このデータを利用します。

結果のB1セル（セルの表示形式は時刻にしておきます。）に
=IF(COUNTIF($D$1:$G$12,"<1")<ROW(A1),"",SMALL($D$1:$G$12,ROW(A1)))

A1セルに
=IF(B1="","",INDEX($I$2:$I$13,SMALL(IF($J$2:$J$13=B1,ROW($A$1:$A$12)),COUNTIF($B$1:B1,B1))))
これは配列数式になってしまいますので、この画面からA1セルにコピー＆ペーストする場合は
A1セルに貼り付け後、数式バー内で一度クリック　→　編集可能になりますので
Shift＋Ctrlキーを押しながらEnterキーで確定！
数式の前後に｛　｝マークが入り配列数式になります。

最後にA1・B1セルを範囲指定し、B1セルのフィルハンドルで下へコピーすると画像のような感じになります。
以上、参考になれば良いのですが・・・m(_ _)m

補足

tom04さんありがとうございます。

コレコレ！まさにコレです！凄いです！ありがとうございます！
ちなみに日付指定がある場合や、表がもっと大きくなってもこの数式は有効ですか？

│￣￣￣￣│￣￣￣￣│
│ 1401 │ 1402 │
│ A様 │ B様 │
│ 18日 │ 20日 │
│ 9：00 │ 14：00 │
│＿＿＿＿│＿＿＿＿│

のように日付指定がある場合に

---A-------B------C---
1401 18日 9：00
1313 18日 9：00
1201 18日 13：00
1408 19日 10：00
1203 19日 10：00
1404 20日 9：00
1209 20日 13：30
1402 20日 14：00

上のようにB列に指定日の早い順→C列に指定時間の早い順→A列に部屋番
という抽出&表示は可能でしょうか？
後から付け足しで申し訳ありません (^_^;)
お暇な時にでも解答を頂けたら嬉しいです。
私もアドバイス頂いた数式を元に色々と勉強してみます。

回答No.3

imogasi

疑問
＞図のような一覧表があり
Ａ、Ｂ列かＤ列より右の枠内か
＞A列にはそれに対応する部屋番号を抽出表示させたいのです
であればＤ列より右ーー＞Ａ，Ｂ列か？
普通は、Ａ，Ｂ列を基本にして、判りやすいようにＤ列以下の表（図示）を作成する。
ーー、
既に言及があるように、
部屋割りのようなデータの書き方（記録し方）はエクセルでは使いにくい。むしろ最終結果で閲覧やチェック用には良いかもしれない。
しかし表の組み換えは、エクセル関数では式が難しいか長くなる。
こういう仕事がらみのことをやるにはＶＢＡを勉強しないとならないでしょう。
ーー
１３０１号室は９：００とあるが、１３：００からの利用申し込みがあれば、下に時刻を、継ぎ足すのか。
そういう数が不定なものの処理も、エクセル関数では式を作るのが難しい。
ーー
この質問からはなれて、質問者がしたいことを本格的に実現するのは、他のソフトとか、プロが専用のソフトを開発するもので、エクセルだけしか知らない人の課題を超えると思う。
＝＝＝
例データ　質問そのまま
Ｄ１：Ｇ１２　　（４列）
1401 1402 1403 1404
A B C D
13:00 14:00 10:00 9:00

1301 1302 1303 1304
A B C D
9:00 14:00 9:00

1201 1202 1203 1204
A B C D
13:00 10:00 9:00
ーー
コード
標準モジュール
Sub test01()
i = 1
For k = 2 To 13 Step 4 '４行で１かたまり
For j = 4 To 7 'D列から４列
Cells(i, "A") = Cells(k, j)
Cells(i, "B") = Cells(k + 2, j) '時刻は２行下にある
i = i + 1
Next j
Next k
End Sub
簡単な７行のコードだが、私がＶＢＡを必須といっているのは、それだけが理由ではない。他の局面でも使った方が
良い場合が頻出するから。
ーーー
結果Ａ１：Ｂ１２
1401 13:00:00
1402 14:00:00
1403 10:00:00
1404 9:00:00
1301 9:00:00
1302 14:00:00
1303
1304 9:00:00
1201 13:00:00
1202 10:00:00
1203
1204 9:00:00
＞左側のB列に指定時間の早い順に
Ｂ列をキーとしてＡ，Ｂ列をソートすれば仕舞い。
並べ替えは操作でもＶＢＡ（略）でも、出きる。

お礼

imogasiさんありがとうございます。

いつもアドバイスして頂き感謝です (*^_^*)
相変わらず勉強不足で申し訳ありません…
実は以前のアドバイスの後にVBA入門の本を購入したのですが…
奥が深すぎて理解不能でした (T_T)
少しずつ勉強していきますのでこれからも宜しくお願いします。

回答No.2

mu2011

図の様な一覧表の構成はデータ活用がし辛いので普通に２次元の表を作成されては如何でしょうか。
例えば、D1:G3をコピー→別シートのA1を選択、形式を選択して貼り付け→値を選択、行列を入れ替えるを選択→ＯＫ（以下同様）とすれば、Ａ列に部屋番号、Ｂ列に名前、Ｃ列に指定時刻の表ができます。
後は見出し行を挿入すれば、エクセル機能の並び替えやオートフィルタ等が活用できます。

お礼

mu2011さんありがとうございます。

>形式を選択して貼り付け→値を選択、行列を入れ替えるを選択
この形式を選択するというのがミソなんですね。
イマイチこの機能と使い方が理解できずに悩んでます(^_^;)
もっと勉強しなきゃ。。。
これからもアドバイス宜しくお願い致します。

回答No.1

pai3_14

１．まず　A列、B列に　部屋番号と到着時間セルをコピーします。
　　A1セルを選んで　「＝」を入力し D1セルをクリックしてリターン
　　B1セルを選んで　「＝」を入力し D3セルをクリックしてリターン
　　同じ操作を A列：D2～G2　B列：D3～G3　などと繰り返します。
２．A1～B11 セルをドラッグして範囲選択し　右クリック
　　「並べ替え」＞「ユーザ設定の並べ替え」で
　　「最優先されるキー」に B列を指定する
で出来ます。

一旦この操作をしてからシートのタブをクリックして
シートのコピーをしておけば、違う日のお客様の到着を
一覧表で記入すると　A列、B列には　そのデータが転記されますから
２．の操作をするだけで並び変わります。

お礼

pai3_14さんありがとうございます。
《ユーザ設定の並べ替え》という便利な機能があるんですね！
とても参考になりました。しかも優先されるキーを複数指定出来るなんてビックリです。
今後活用…出来るように頑張ります (^_^)v