ミスチルの大名曲の数々が配信決定!! 31日間無料!!

エクセルのデータでセル内容(電話番号)を検索して表示しる方法を教えてください。

オートフィルターは、登録件数が1000件?位しかないので、それを越える件数の検索を

したいのですが、方法が解りません 

解る方、教えてください。

このQ&Aに関連する最新のQ&A

A 回答 (9件)

正直、数式を人に教えてもらっても自分で実状に合わせて変更できないのならそれは使わないほうがいいと思います。



フィルタオプションでやってみたら如何ですか?

それか、表示用シートと検索用シートを=でリンクさせて抽出したデータが出てくるようにするとか。
(すでに回答していただいてますよね。)

でももし数式をご自分で変更してみたいのでしたら、

=IF(COUNTIF(Sheet1!$A$5:$A$3000,$A$1)<ROW(A1),"",INDEX(Sheet1!$A$5:$I$3000,SMALL(IF(Sheet1!$A$5:$A$3000=$A$1,ROW($1:$2996)),ROW(A1)),COLUMN(B1)))

Sheet1にデータ、別シートのA2に上の数式を入れています。
どこにシート名を入れるかわかりますね?

それから、Sheet1のA5:A3000に「リスト」と名前を付け、別シートのA1にリストという名前を参照するようにプルダウンリストを設定します。


条件の後出しというのはこういった掲示板の質問では非常に敬遠されます。
回答者に二度手間三度手間をかけますので。
抽出系の質問をされる場合は、データの配置や抽出したリストをどこに出したいか、作業列は使えるのかなどの情報を最初に提示されることをお勧めします。
    • good
    • 0
この回答へのお礼

有り難う御座いました。

どうやって良いか解らず質問も適切に出来ずに、

お手数をおかけしまして誠にすいませんでした。

今までの回答で大変助かりました、後は何とか

やってみます。

お礼日時:2011/04/15 13:54

どうも!No.3・4です。



マクロってご存知ですか?
マクロの記録を使えばボタン等にマクロを設定できますが、
毎回データ数が変わったりするのであれば、いろいろ工夫しないと
いけないと思います。
コードがいじれないと難しいです。

単にフィルタオプションの設定を一発で出したいのなら
ツール→ユーザー設定の「コマンド」タブで
左の一覧から「データ」をクリック
右の一覧の「フィルタオプションの設定」を
マウスでツールバーまでドラッグすれば
ツールバーに表示されます。
    • good
    • 0

うお、ごめんなさい。

また変なところの式をコピーしてしまいました。

L2=IF(COUNTIF($A$5:$A$3000,$K$2)<ROW(A1),"",INDEX($A$5:$I$3000,SMALL(IF($A$5:$A$3000=$K$2,ROW($1:$2996)),ROW(A1)),COLUMN(B1)))

Ctrl+Shift+Enter

で。
    • good
    • 0
この回答へのお礼

ご回答有り難う御座います。

いまさらですが・・・・

別のワークシート(検索用の)を作ってやる方法は、ありますか?

よく解らないので、すいません m__m

お礼日時:2011/04/14 18:34

ははぁ、そうきましたか…



やっぱり、並べ替えちゃえばいいのでは?と思いますが一応関数で出してみます。

データの上に抽出すると5件以上ある場合に面倒ですので、今回はK2に検索値、その右側にデータを出すようにします。
K2に入力規則でプルダウンリストを作成します。(前回のA2と同じ。)
そして、L2に

=IF(COUNTIF($A$5:$A$3000,$K$2)<ROW(C12),"",INDEX($A$5:$I$3000,SMALL(IF($A$5:$A$3000=$K$2,ROW($1:$2996)),ROW(C12)),COLUMN(D12)))

と入力し、Ctrl+Shift+Enterで確定します。
その後必要な分右と下にフィルコピーして下さい。

また、重複する番号があるという事ですのでプルダウンリスト設定時に重複しないリストを作成したいようでしたらNo.4さんのやり方で別に抽出してからそれをリストに設定してもいいと思います。
    • good
    • 0

…すみません。

範囲指定が間違ってました。

=VLOOKUP($A2,$A$5:$I$3000,COLUMN(),0)

第二引数がA5からI300を範囲指定です。


A列しか指定しなかったらそりゃ出ませんね。
失礼しました。
    • good
    • 0
この回答へのお礼

ご回答有り難う御座いました。

入力してみました、検索表示できました

しかし、1行しか出来ないのですが・・・

一つの電話番号をすべて表示したいのですが、どうすれば様でしょうか

説明不足だったかもしれません、すいません

お礼日時:2011/04/13 15:06

早とちり失礼しました!



それでしたら
データ→フィルタ→フィルタオプションの設定
で抽出可能です。

検索条件範囲は、別のシートを用意して
一覧を作っておけば良いです。
(検索したい電話番号はどのようになっているのでしょうか?上記シートに「="="&セル番号」などと表示しておけば反映されます。)

フィルタオプションの設定についてはネットにたくさん載ってます。
http://allabout.co.jp/gm/gc/297791/2/
などご参照ください。

わかりづらかったらすみません!
    • good
    • 0
この回答へのお礼

ご回答有り難う御座いました。

表示出来るようになりました。

助かりました、有り難う御座います。

出来れば、この作業をコマンドボタンとかに登録出来ないでしょうか?

検索結果はデータの右横に作って見ました

「データ」 → 「フィルタ」 → 「フィルタオプションの設定」を

簡単な操作で出来ないでしょうか

何か有りましたら、お教えください

お礼日時:2011/04/14 00:01

こんにちは。


私の勉強不足でしょうか?

>オートフィルターは、登録件数が1000件?位しかないので

というのがわかりませんが…

Excel2003:▼ボタン押下→(オプション)で
      [希望の電話番号] [と等しい]→[OK]
Excel2007:▼ボタン押下→数値フィルタ→指定の値に等しい
      [希望の電話番号] [と等しい]→[OK]

で出ませんか?
ご存じでしたら失礼しました;

あ、それとも検索したい電話番号が1000件以上あるということでしょうか?

この回答への補足

ご回答有り難う御座いました。

そうです電話番号が1000件以上有ります

Excel2003を使っています

オートフィルターでは、途中までしか表示出来ません

困っているのですが、良い方法が解りません

良い方法が有りましたら、お教えください。

補足日時:2011/04/13 03:41
    • good
    • 0

レイアウトは分かりました。



元表の電話番号をA列に持ってくることは可能ですか?
それができるようであれば、A2から右に表示されるように配置します。

まず、A2に入力規則を設定します。ちなみに当方は2003です。

データ→入力規則→入力値の種類:リスト、元の値:=$A$5:$A$3000

とすると、A2で電話番号が選択できるようになります。
一応検証しているので全て表示されるはずです。

その後、B2に=VLOOKUP($A2,$A$5:$A$3000,COLUMN(),0)

と入力し、右にコピーしてみて下さい。

この回答への補足

ご回答有り難う御座いました。

早速やってみたのですが・・・・Excel2003番を使っています

A列に挿入でセルを増やしてI列をコピーして持ってきまして

A2に入力規則を設定しました、

B2に関数の=VLOOKUP($A2,$A$5:$A$3000,COLUMN(),0)を入力しましたが

「#REF!」と表示してしまいます、どうしてでしょう・・・?

何を、間違っているのかも、解りません 何が悪いか解りましたら

お教えください。

補足日時:2011/04/13 03:34
    • good
    • 0

探すだけなら普通にCtrl+Fで検索したらいいんじゃないですか?



オートフィルターが使えない…という事は該当セルを探し出せればいいんですよね?

もしくは、並べ替えてしまうとか。

探した後、何をどう表示させたいのか、表のレイアウトはどうなっているのかなどが分からないから回答が付かないんだと思いますよ。

この回答への補足

回答有り難う御座いました。

検索をした後でセルの内容(電話番号)が含まれている行をすべて

表示させたいのですが、オートフィルターでは、登録される数が決まっている

様なので、それを越えた数は登録出来ないようなのです

オートフィルターと同じ表示をしたいのですが、登録数が越えた時に、どうすれば

良いかわかりません、良い方法が有りましたら お教えください。

A:5~I:3000のセルを使っています

  A  B   C   D   E   F    G    H    I  
5 年  日付     名前 支店  内容  住所  時間  電話番号
6 2011 3月22日   山田 中区  ・・・   ・・・  ・・・   090-0000-9999

こんな、レイアウトなのですが 何とかなりませんでしょうか

お願いします。

補足日時:2011/04/12 01:14
    • good
    • 0

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

QExcelで電話帳を作りたいのですが…

こんにちは!
初めての質問ですので要領を得ない書き方でしたらごめんなさい!
私のパソコンはWindows7でOfficeは2010がインストールされています。
まだまだパソコンには不慣れですけどExcelで電話帳を作成したいと思っています。
項目は名前、読み方、住所、電話番号、FAX番号、携帯番号(3項目位)、担当者の名前などで、
入力するデータは400名ほどあります。
名前などで検索出来るようにしたいのですが…
簡単なやり方を教えて下さい。
それから、色んな人の質問や答えを見ていると答える方が何故か怒っているような文章になっているのをよく見掛けます。
申し訳ないのですが、怖い方や怒ったような文章を書く方はアドバイスいりません。
あくまで紳士的でメチャメチャ初心者でも優しく教えて下さる方のみお願いします。
何回も質問する事があるかとは思いますが、そんな時でも怒らないでよろしくお願いします。

Aベストアンサー

こんばんは!

>名前などで検索出来るようにしたいのですが…
というコトですので一例です。

↓の画像で上側が検索データを表示させたいSheet1で、下側が元データのSheet2とします。

まず、Sheet2のデータはコツコツご自身で作成する必要があります。
(Sheet2のB列はあらかじめ関数を入れておいた方が良いかもしれません)
画像ではSheet2のB2セルに
=IF(A2="","",PHONETIC(A2))
という数式を入れフィルハンドルでずぃ~~~!っと下へコピーしておきます。
A~F列(B列以外)はすべて手作業で入力しておいてください。
Sheet2の表が作成できたうえで
Sheet2のG列を作業用の列として使用します。
G2セルに
=IF(OR(A2="",COUNTIF(A2,"*" & Sheet1!A$1&"*")=0),"",ROW())
という数式を入れこれもフィルハンドルでずぃ~~~!っと下へコピー!

最後にSheet1のA4セルに
=IFERROR(INDEX(Sheet2!A:A,SMALL(Sheet2!$G:$G,ROW(A1)))&"","")
という数式を入れ、列・行方向にコピー!

最後にSheet1のA1セルに検索したい氏名の一部分でも良いので入力すると
その文字が含まれるデータが表示されます。m(_ _)m

こんばんは!

>名前などで検索出来るようにしたいのですが…
というコトですので一例です。

↓の画像で上側が検索データを表示させたいSheet1で、下側が元データのSheet2とします。

まず、Sheet2のデータはコツコツご自身で作成する必要があります。
(Sheet2のB列はあらかじめ関数を入れておいた方が良いかもしれません)
画像ではSheet2のB2セルに
=IF(A2="","",PHONETIC(A2))
という数式を入れフィルハンドルでずぃ~~~!っと下へコピーしておきます。
A~F列(B列以外)はすべて手作業で入力しておいてく...続きを読む

Qエクセルの住所録の電話番号から名前を明記する方方

質問ですが
エクセルで下のような表があるとして
名前を入れたら電話番号とかは
VLOOKUPで出るようにできますよね
そんな感じで電話番号を入れたら
その番号の名前が出るように
したいのですが何故かVLOOKUPでは
出来ないようで(私の知識不足かもしれませんが。。)
なんとか良い方法はないでしょうか?
エクセルに下記のような情報を
入れて行ってるので出来ればエクセルで出来る方法
があればうれしいです。
宜しく御願いします。


名前     電話番号      住所
山田 花子090-222-111大阪市・・・

Aベストアンサー

LOOKUP関数を使用すれば良いと思います。
VLOOKUP関数でできない左側の値を返すことができます。
次のページを参考にしてください。

参考URL:http://arena.nikkeibp.co.jp/tec/excel/20040310/107767/

QExcelでの名前検索

苗字、住所、電話番号が並んだ表があります。
苗字は当然のごとく重複してくるのですが、この苗字を検索した時に該当者全員分の住所、電話番号が出てくるようなセルを作りたいのですが可能でしょうか。
vlookupでの検索をイメージしていたのですが…。
フィルタで絞るにも200件からあるので探すのがめんどくさく、使用するときには時間がなかったりします。
という条件で、何かよい方法があったら教えて下さい。

Aベストアンサー

A~C列:住所録(リスト)
E2:検索する苗字(入力)
F2:住所
=IF($H2="","",INDEX(B$1:B$999,$H2))
G2:TEL
=IF($H2="","",INDEX(C$1:C$999,$H2))
H2:検索された場合の行数
=IF(ISERROR(MATCH($E$2,OFFSET($A$1,$H1,0):$A$999,0)),"",MATCH($E$2,OFFSET($A$1,$H1,0):$A$999,0)+$H1)
F2~H2、まとめて下に適当数コピー
H1は空白にすること。
最大行数は適当に変更。

Qエクセルで番号を入力すると横の列に名前を表示するように・・

エクセルで、番号を入力すると名前も自動的に表示される・・
その様な事は出来ますでしょうか?

123 新垣結衣
456 上戸彩
789 戸田恵梨香

上のように、左の列の枠に番号を入れると、その右横の列の枠に
その番号に合った名前が表示される というものです。

毎週社員の作業予定表を作っているのですが、以前は社員番号順
だったので、頻繁に名前等の順番を変更することはなかったのですが、
表が見にくいということで、先日から番号順でなく作業内容によって
名前記載順を変えることになりました。
作業内容変更がある度に社員の名前順を変えないといけないので、
間違えずに番号と名前の両方を打ち込むのが面倒です・・
何か良い方法をご存知の方いらっしゃいましたら是非教えてください!

宜しくお願い致します(。・ω・))(。uωu))ペコリ

Aベストアンサー

VLOOKUP関数で出来ます。

VLOOKUP(参照する元になるセル番号,参照する一覧表範囲,参照する一覧表の何列目を表示するのか)

まず社員番号と名前の一覧表をどこかに作ります。
(例)社員番号をD1セル、名前をE1セルに作り、社員番号の入力をA1セル、表示させるセルをB1セルにした場合

(1)一覧表を作る
    D列  E列
1行目 123 新垣結衣
2行目 456 上戸彩
3行目 789 戸田恵梨香

(2)B1セルに
 =IF(A1="","",VLOOKUP(A1,$D$1:$E$3,2))
を入力です。後は表示させる所まで下にドラッグコピー。
※IF関数は未入力時に何も表示させないようにするものです。

(3)A1セルに社員番号を入力すれば名前がB1セルに自動表示する

QExel VBA 別ブックから該当データを検索し、必要なデータを取得する方法について

部品表というブックがあります
A列に商品名、B列に商品番号が入力してあります。C列のコードは未入力です。
A列     B列     C列      
商品名  商品番号  コード
モータ  U-1325-L  
ホルダ  R-134256

また、コード一覧表という別のブックには、A列に商品番号と、B列にコードが、何千件も入力されています。

やりたいことは
部品表のC列のコード欄に、コード一覧表ブックから商品番号と一致するコードを貼り付けしたいのです。

部品表は、何百種類もありますので、関数ではなく、マクロで処理を希望します。

自分では、部品表の商品番号をコピーして、コード一覧表で検索し、検索結果の右隣のセル(B列のコード)の値を部品表のC列に貼り付ければよいかと思い、書いてみたんですが…

Sub 別ブックから貼り付ける()
  Dim 検索する As Long
Windows("部品表.xls").Activate
検索する = cells(i,2).Value
Windows("コード一覧表.xls").Activate
ActiveWindow.SmallScroll Down:=-3
Selection.AutoFilter Field:=3, Criteria1:="=検索する", Operator:= xlAnd

と、してみたものの、検索しても、その検索結果の隣のセルのコードをどうやって取得すればいいのかが、わかりませんでした。

基本事項は本で学びましたが、呪文のようなコードはよく理解できません。懸命にネットで検索して、訳して理解する努力をしてはいますが。

どうぞよろしくお願いします。

部品表というブックがあります
A列に商品名、B列に商品番号が入力してあります。C列のコードは未入力です。
A列     B列     C列      
商品名  商品番号  コード
モータ  U-1325-L  
ホルダ  R-134256

また、コード一覧表という別のブックには、A列に商品番号と、B列にコードが、何千件も入力されています。

やりたいことは
部品表のC列のコード欄に、コード一覧表ブックから商品番号と一致するコードを貼り付けしたいのです。

部品表は、何百種類もありますので、関数...続きを読む

Aベストアンサー

こんにちは。
とりあえず実用性も踏まえました。
メインの動作はワークシート関数のVLOOKUPをVBA上で使用していますので理解はしやすいかと思います。
また、質問文から察するに「部品表.xls」と「コード一覧表.xls」の両方を開いて処理されていますが「コード一覧表.xls」はプログラム内で開いて閉じているので実行するときは「コード一覧表.xls」は閉じて置いてください。
Option Explicit
Sub Sample()
 Application.ScreenUpdating = False
 Dim I As Long
 Dim xlBook
 Set xlBook = Workbooks.Open("C:\★★\コード一覧表.xls") '★要変更★
 I = 2
 Do While Range("A" & I).Value <> ""
  ThisWorkbook.Worksheets("Sheet1").Range("C" & I).Value = Application.VLookup(ThisWorkbook.Worksheets("Sheet1").Range("B" & I).Value, xlBook.Worksheets("Sheet1").Range("A2:B65535"), 2, 0)
  I = I + 1
 Loop
 xlBook.Close
 Application.ScreenUpdating = True
 MsgBox ("完了")
End Sub

こんにちは。
とりあえず実用性も踏まえました。
メインの動作はワークシート関数のVLOOKUPをVBA上で使用していますので理解はしやすいかと思います。
また、質問文から察するに「部品表.xls」と「コード一覧表.xls」の両方を開いて処理されていますが「コード一覧表.xls」はプログラム内で開いて閉じているので実行するときは「コード一覧表.xls」は閉じて置いてください。
Option Explicit
Sub Sample()
 Application.ScreenUpdating = False
 Dim I As Long
 Dim xlBook
 Set xlBook = Workbooks....続きを読む

Q(Excel)あるセルに文字を入力しただけで、同じブック内のほかのワークシートにも、同じ文字が自動的に入るようにするには?

こんにちは。
質問内容はタイトルのとおりです。

あるワークシートのあるセルに文字を入力すると、ほかのワークシートのセルにも同じ文字が自動的に書き込まれる方法を知りたいです(ブックは同じ)。複数のワークシートを制御するには、やはりマクロを使うのでしょうか?

よろしくお願いします。

Aベストアンサー

clam_chowderさん、こんにちは。

Sheet1のA1にたとえば「100」と入力しますね。
Sheet2のA1に、これと同じ数値を表示するには、
 =Sheet1!A1
でOKです。

数式をいれるのが苦手なら、
Sheet2のA1セルで「=」を入力すると、数式入力状態に入りますから、
ここでSheet1のシートタブをクリックし、
リンクしたいA1セルをクリックしてEnterすると、
自動的にさきほどと同じ式が入ります。

Qエクセルの検索方法教えてください!!

こんばんは、超エクセル初心者です。

エクセルの列に名前がずらっ~と並んでいるとして・・・、
例えばその中から”高橋さん”を見つけたい時の
方法を教えてください!!

一応、編集→検索で高橋と入力してみたのですが
”検索条件に一致するデータはありません”とメッセージが
でてしまいます。どうしてでしょうか??
詳しい方、教えてください、よろしくお願いいたします。

Aベストアンサー

その方法でまったく問題ないはずですが・・・

考えられる原因として、

1."高橋"という文字が Excelシート内にない。

2.検索オプションが「完全一致」になっている。
ようするに、セル内に "高橋さん" という文字列があったとして、検索するときに "高橋" と入力すると、完全に一致していないので、検索されないという状態になります。
一度、検索ウィンドウを開いて、「セル内容が完全に同一であるものを検索する」にチェックが入っていないか確認してください。

Qエクセルで打ち込んだ数字を自動で別シートに表示したい

エクセルでセルに打ち込んだ数字を自動で別シートに表示できる方法があれば、教えてください。

例えば、シート1のC1に5を打ち込んだら、シート2のD2にシート1で打ち込んだ5が自動で表示される。

また1列すべてを自動で表示させる場合、一つのセルの時と違いがありましたら教えてください。よろしくお願いします。

Aベストアンサー

こんばんは。
入力したセルの値を合計とかでなくて、
純粋に別のシートに自動的に表示したいのであれば、
以下の方法があります。

1.1つのセルだけの場合
例)シート1のC1に5を打ち込んだら、
  シート2のD2にシート1で打ち込んだ5が自動で表示される

⇒シート2のD2のセルをアクティブにして「=」を入力
 した後、シート1のC1をクリックする。
 そうするとD2のセルに「=Sheet1!C1」と表示され、値が自動的に
 表示されるようになります。

2.1列全てコピーしたい場合。
  コピー&リンク貼り付けを使うと便利です。

例)例)シート1のC1~C5に何かを入力したら、
  シート2のD2~D7にシート1で打ち込んだものが自動で表示される

  シート1にあるコピー元のセルを範囲選択して、
  シート2のD2の上で「右クリック」⇒「形式を選択して貼り付け」
  をクリックします。

  そして出てきた小さな画面の左下にある「リンク貼り付け」という
  ボタンをクリックすると完成です。
  試してみてください。。

  念のためにリンク貼り付けを図解しているURLを載せておきます。
  参考にしてみてくださいね。。
  http://www.geocities.jp/office_inoue/excel/eq21.htm

こんばんは。
入力したセルの値を合計とかでなくて、
純粋に別のシートに自動的に表示したいのであれば、
以下の方法があります。

1.1つのセルだけの場合
例)シート1のC1に5を打ち込んだら、
  シート2のD2にシート1で打ち込んだ5が自動で表示される

⇒シート2のD2のセルをアクティブにして「=」を入力
 した後、シート1のC1をクリックする。
 そうするとD2のセルに「=Sheet1!C1」と表示され、値が自動的に
 表示されるようになります。

2.1列全てコピーしたい場合。
  コ...続きを読む

Qエクセル 数値だけ抽出するには?

エクセルで、文字列+スペース+数字と入力されているセルから数値だけ取り出す方法を教えていただけますか?
文字列とスペースの字数は一定で、数値の桁数は6-8桁と変動します。
A列からb列のように変換したいのです。

A列          B列
PMID:_12345678    12345678
PMID:_123456     123456

宜しくお願いいたします。

Aベストアンサー

B1に
VALUE(REPLACE(A1,1,6,""))
でよいでしょう.

数値の桁数には依存しません.

QEXCEL(IF関数)でCELLの色を変える。

例えば、IF関数で真ならセルの色を赤色にしたり、文字の色を変えたりする関数とかはあるのでしょうか?わかりません・・・知ってる方いらっしゃれば教えて下さい。

Aベストアンサー

こんばんは。

関数ではありませんが‥

<条件付き書式>ではダメなのでしょうか?

メニュー<書式>-<条件付き書式> です。

どのような条件を想定していらっしゃるのかわかりませんが、
<条件付き書式>については↓の参考URLをご覧ください。

▽条件付き書式・その1
http://homepage1.nifty.com/kenzo30/ex_kisosyo/ex_ks_syokyu4.htm

▽条件付き書式・その2
http://homepage1.nifty.com/kenzo30/ex_kisosyo/ex_ks_syokyu5.htm

参考URL:http://homepage1.nifty.com/kenzo30/ex_kisosyo/ex_ks_syokyu4.htm


人気Q&Aランキング