- ベストアンサー
【エクセル】特定の文字以外を返す計算式
申し訳ありません。どなたか助けてください。 【Sheet1】 A B C D 1 実績月 部署等 氏名 金額 2 4月 事業部 佐藤 100 3 4月 実績なし 田中 0 4 4月 実績なし 鈴木 0 5 5月 事業部 佐藤 40 6 5月 営業部 田中 50 7 5月 販売部 鈴木 200 8 6月 実績なし 佐藤 0 9 6月 営業部 田中 70 9 6月 販売部 鈴木 30 ※【Sheet1】の情報を【Sheet2】のようにまとめたいのです。 ※C、D、E列にはSUMPRODUCT関数を使用して集計しています。 【Sheet2】 A B C D E 1 氏名 部署等 4月 5月 6月 2 鈴木 販売部 0 200 30 3 佐藤 事業部 100 40 0 4 田中 営業部 0 50 70 ※B列(B2)に以下のような計算式を埋め込みました。 INDEX(Sheet1!$B:$B,MATCH($A2,Sheet1!$C:$C,0)) ※すると以下のようになってしまい、部署名が正しく表示されません。 ※B列が「実績なし」になっている場合はその値は返さず、 「実績なし」以外の部署名を表示させたいのです。 【Sheet2】 A B C D E 1 氏名 部署等 4月 5月 6月 2 鈴木 実績なし 0 200 30 3 佐藤 事業部 100 40 0 4 田中 実績なし 0 50 70 ※どなたかお力をお貸しください。 ※そもそも部署等欄に、「実績なし」という文字を表示させなければいいのかもしれませんが、訳あってこのようになっています。 ※「実績なし」以外の部署名を表示させる関数を教えてください。
- 01o00
- お礼率50% (4/8)
- オフィス系ソフト
- 回答数5
- ありがとう数2
- みんなの回答 (5)
- 専門家の回答
質問者が選んだベストアンサー
こんにちは。 ものすごく根本的なことですが、実績表を以て、それをINDEX で部署を表示させることに問題があるのではありませんか。それは、ピボットテーブルでもうまくいくとは思いません。 実績なし : 鈴木 左辺と右辺の対応がないものが混ぜてあるのですから、その考え方に、数学的な無理があります。 確かに、SUMPRODUCT 関数で =INDEX(Sheet1!$B:$B,SUMPRODUCT(MAX((A2=Sheet1!$C$1:$C$100)*(Sheet1!$B$1:$B$100<>"実績なし")*ROW($A$1:$A$100))),1) このような数式を埋め込めばでますが、実務上では、負担が大きすぎて、他にも同じような数式があるとすると、潜在的に問題が出てくるように思います。 もし、お仕事でしたら、ぜひ、氏名と部署のリスト原簿を作ることをお勧めします。 Sheet1 適当な場所で、 =IF($B1<>"実績なし",$C1,"") =IF($B1<>"実績なし",$B1,"") として、一覧を出して、後は、ツール--フィルタ--フィルタオプションの選択 で、重複するレコードは無視するで、一覧のリストが取れますから、それを使えばよいと思います。 また、 数式で、 氏名: =INDEX($B$1:$C$100,SMALL(INDEX(($B$1:$B$100<>"実績なし")*ROW($C$1:$C$100),),ROW(A1)+COUNTIF($B$1:$B$100,"実績なし")),2) 部署等: =INDEX($B$1:$C$100,SMALL(INDEX(($B$1:$B$100<>"実績なし")*ROW($C$1:$C$100),),ROW(B1)+COUNTIF($B$1:$B$100,"実績なし")),1) で、重複は出ますが、これを、コピー・値貼り付けにして、それを並べ替えて重複を削除し、再び並べ替えて、重複を取り去ってもよいと思います。
その他の回答 (4)
- hallo-2007
- ベストアンサー率41% (888/2115)
例えば 4月だけの時点ではどうでしょう。 【Sheet1】 A B C D 1 実績月 部署等 氏名 金額 2 4月 事業部 佐藤 100 3 4月 実績なし 田中 0 4 4月 実績なし 鈴木 0 田中さんがどの部署なのか情報がありません。このシートでは無理なのです。 別途 Sheet3に 部署名 担当者名 販売部 鈴木 事業部 田中 営業部 佐藤 といったマスターが必要です。 また マスターがあればSheet1の部署名 或いは 氏名のどちらかが 関数で表示できますので入力作業も省けます。 別途疑問は Sheet1の部署名に なぜ実績なし と表示する必要なのでしょうか? 条件付書式で色を変えて表現するなどの方がベターではないでしょうか?
お礼
御助言ありがとうございます。 おっしゃるとおり、マスタを作成するのが王道だと思います。 ですが、いろいろな理由で、#4様が御提案してくださったSUMPRODUCT 関数を使用させていただきたいと思います。 >別途疑問は Sheet1の部署名に なぜ実績なし と表示する必要なのでしょうか? >条件付書式で色を変えて表現するなどの方がベターではないでしょうか? 実は、Sheet1を作成するために次のような流れがあります。 Sheet3(データ貼り付けシート)→Sheet4(Sheet3を参照して集計)→Sheet1(Sheet3の見出し&計以外をコピー値貼付))→Sheet2 ※Sheet4→Sheet1はボタンを作成してマクロで実行します。 私も違うシートを作成する場合は、 "hallo-2007"様に御提案いただいた視点を忘れないようにいたします。 今後ともよろしくお願いします。 ありがとうございました。
#2の者です。 この回答は破棄してください。 エラーが出ました。 失礼しました。
作業列使っちゃだめですかね。 ■Sheet1 の E2 に =if(B2="実績なし","",B2) と入れて、下まで引っ張る。 ■Sheet2 の B2 に =vlookup(A2,Sheet1!$C$2:$F$65536,4,1) それで下に引っ張る。 これでどうでしょう。 ご意向に沿っていれば幸いです。
“苦手な”関数を一切使わない[ピボットテーブルレポート]で実現できますよ。 結果だけを示しておくので、勉強してください。 合計/金額 実績月 氏名 部署等 4月 5月 6月 佐藤 事業部 100 40 0 田中 営業部 0 50 70 鈴木 販売部 0 200 30
補足
早速のご回答ありがとうございます。 [ピボットテーブル]については、私も最近勉強しました。 最近、エクセル2002からエクセル2007に変えたのですが、 随分使い勝手がよくなったと思っています。 折角[ピボットテーブルレポート]の御提案をいただいたのですが、 私よりもエクセルが苦手な者にファイルを使用してもらうことを想定しているため、できれば[ピボットテーブル]ではない選択肢を探しています。(ピボットテーブルの操作自体は自分も難しいと思わないのですが、できれば計算式での対応ができないでようか。) いろいろ申し訳ありません。
関連するQ&A
- エクセルで2つの条件を元に
エクセル2007で名簿を作っています。 1ヶ月ごとにメンバーの順が変わります。 シート1(番号順に名簿を作りました) A B C 1 1 山田 2 2 木村 3 3 副長 佐藤 4 4 鈴木 5 5 班長 田中 6 6 長田 シート2(こちらがメンバーに配る名簿です) A B C D 1 班長 1 3 2 田中 山田 鈴木 3 4 副長 2 4 5 佐藤 木村 長田 A2に田中,A5に佐藤を選んでくるのはvlookupで成功しました。 質問したいのは C列D列に上記の番号のようにシート1の名前を入れていきたいのですが,田中,佐藤の分を抜いた上で番号の若い順に選んできたいのです。 どのような関数をどのように使えば成功するでしょうか。
- ベストアンサー
- その他MS Office製品
- エクセルで条件に一致したセルの隣のセルを取得したい
下のような「得点」という名前のシートがあります。 (「田中」のセルがA1です。) [ 田中 ][ 10 ][ 200 ] [ 山田 ][ 21 ][ 150 ] [ 佐藤 ][ 76 ][ 250 ] [ 鈴木 ][ 53 ][ 350 ] 別のシートのA1セルに、「佐藤」と入力すると、 [ 佐藤 ] 「得点」シートから「佐藤」の列を見つけて、B1、C1に [ 佐藤 ][ 76 ][ 250 ] のように表示させたいのですが、B1、C1にはどのような式を書けば良いのでしょうか。 「得点」シートでは氏名が重複する事はありません。 IF文を使うと思うのですが、いまいち良く分かりませんでした。 よろしくおねがい致します。
- ベストアンサー
- その他MS Office製品
- エクセル関数
シート1という名前のシートと シート2という名前のシート2つのシートがあります。 シート1は ・A列には従業員の氏名が入っています ↓このように A 空欄 鈴木 伊藤 佐藤 ・資格名は見出しとしてB2~Q2まで入ってます(見出しとして) ↓シート1の全体はこのような感じ A B C D 空欄 資格1 資格2 資格3 鈴木 伊藤 佐藤 次にシート2は ・A列に個人コードが入ってます ・B列に名前が入ってます ・C列に資格名称が入ってます ・D列に資格取得日が入ってます ↓シートの全体はこのような感じ A B C D 1 鈴木 資格1 6月 1 鈴木 資格2 7月 2 伊藤 資格3 8月 2 伊藤 資格1 9月 2 伊藤 資格2 10月 3 佐藤 資格3 11月 上記のようなシートがあります。 ここでシート1にシート2の値を返していきたいのですが 見ての通り書式はバラバラです 例えばシート2を見て鈴木は資格1、資格2、資格3を持っています 資格1だったら○をシート1の鈴木と資格1がぶつかってるセルに 資格2だったら○をシート1の鈴木と資格2がぶつかってるセルに 返していきたいのです 何か有効な関数などはありませんでしょうか? むしろ関数でできるのでしょうか? 説明が訳分からなくてすみません。
- 締切済み
- Windows 7
- エクセルシート間の照合について
エクセルシート間で照合をしたいと思っています。 照合する項目は2つ。2つともあえばそのA列のデータを表示するというものです。 データは シート1(前年) A B C D 1 田中 123 東京都・・ 5 佐藤 124 千葉県・・ 3 山本 128 埼玉県・・ シート2(今年) A B C D 山本 128 埼玉県・・ 佐藤 124 北海道・・ 田中 123 東京都・・ C列とD列を照合し2つともあえばシート2のA列にシート1のA列のデータを入力する。なければ「なし」と入力できるようにしたいのです。 上の場合は山本のA列に「3」佐藤には「なし」田中には「1」と入るのうになればと思います。 入力されている列は同じですが順番はばらばらです。データ数は1万件を超えています。 どなたかアドバイスいただければと思います。よろしくお願いします。
- ベストアンサー
- その他([技術者向] コンピューター)
- エクセルで、次のような自動入力をしたい
A B C D E ・・ AA 1 田中 鈴木 佐藤 山田 2 鈴木 山田 海岡 3 田中 佐藤 佐藤 というような表があり、AAの列に名前を入力したとき、 D1に「山田」、C2「海岡」と、その行に関してAA列に新規の名前が入力されたときにD1,C2に自動入力することは、VBAで簡単にできますか? 3列目には「佐藤」さんがすでいるので入力不要です。 よろしくお願いします。(マクロ初心者です)
- ベストアンサー
- オフィス系ソフト
- エクセルのデータ並べ替え(抽出)の方法
エクセルのデータ並べ替え(抽出)の方法 を教えてください。 下記のようなデータがあるとします。 A B C D 1 田中 東京 千葉 福岡 2 山田 京都 滋賀 3 佐藤 奈良 青森 USA 4 鈴木 カナダ 愛媛 A列は名前、B列以降は文字列です。B列以降はC列までの行、D列までの行とさまざまです。重複セルはありません。 これを下記のように並べ替えたいです。 A B C D 1東京 田中 2千葉 田中 3福岡 田中 4京都 山田 5滋賀 山田 6奈良 佐藤 7青森 佐藤 8USA 佐藤 9カナダ 鈴木 10愛媛 鈴木 こういうことは可能でしょうか??? 教えてください。 よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- エクセルVBAの質問です
sample.xlsがあり内容は下記のように A列に名前、B列に数字が入り、行数は一定でありません 田中一郎 3 鈴木健一 5 佐藤太郎 8 田中一郎 5 田中一郎 2 佐藤太郎 7 鈴木健一 3 佐藤太郎 9 鈴木健一 54 佐藤太郎 8 田中一郎 9 このエクセルシートにコマンドボタンを付け、VBAでコマンドボタンを押した場合 指定したエクセルファイル”kekka.xls”に 田中一郎 19 鈴木健一 62 佐藤太郎 32 と言うように、名前別でその氏名の横の数字の合計を表示させたいと考えています。 何卒お知恵をお貸しください、お願いします。
- ベストアンサー
- Visual Basic
- (エクセル)日付に相当するデータを入力する
シート1に以下のように、A列には氏名、C列に日付がランダムに入力されています。 A B C 1 山田 4/4 3:00 2 佐藤 4/3 2:00 3 石井 4/4 3:00 4 加藤 4/3 2:00 5 田中 4/1 5:00 シート2に上から順位に並び替えたいです A B C D E F G 1 4/1 4/2 4/3 4/4 4/5 2 1 田中 4/1 5:00 3 2 佐藤 4/3 2:00 4 3 加藤 4/3 2:00 5 4 山田 4/4 3:00 6 5 石井 4/4 3:00 1行目のC~Gには既に4/1~4/5が入力されています。 対応するところに日付を入力し、さらにB列には氏名を表示したいです。 C2には「=IF(AND(SMALL(Sheet1!$C$1:$C$5,$A2)<D$1,SMALL(Sheet1!$C$1:$C$5,$A2)>=C$1),SMALL(Sheet1!$C$1:$C$5,$A2),"")」としてうまくいきました(C1:F6も同様)。 問題はB列なのですが、B2に「=INDEX(Sheet1!$A$1:$A$5,MATCH(SUM(C2:G2),Sheet1!$C$1:$C$5,0))」や「=INDEX(Sheet1!$A$1:$A$5,MATCH(SMALL(Sheet1!$C$1:$C$5,A2),Sheet1!$C$1:$C$5,0))」としても同じ失敗結果になりました。 両方とも、上から順に 田中 佐藤 佐藤 ←失敗(加藤が正解) 山田 山田 ←失敗(石井が正解) となってしまい、重複する日付が失敗してしまいます。 B列にどのようにしたら良いか教えてください。 よろしくお願いします。
- 締切済み
- Excel(エクセル)
- エクセルで質問です。
エクセルで質問です。 sheet1に A B C D 1 No. グループ 氏名 住所 2 3 1 C 佐藤 東京 4 2 B 鈴木 埼玉 5 3 A 加藤 千葉 6 4 B 本多 東京 このようなデータがたくさんあります。(左の数字は行番号、上のアルファベットは列番号) これを別シートにグループ順に A B C D 1 No. グループ 氏名 住所 2 3 1 A 加藤 千葉 4 2 B 鈴木 埼玉 5 3 B 本多 東京 6 4 C 佐藤 東京 と表示させる方法はありますか。 エクセルに詳しくないのですが どなたか助けていただければと思います。
- ベストアンサー
- その他(インターネット・Webサービス)
お礼
>>お仕事でしたら、ぜひ、氏名と部署のリスト原簿を作ることをお勧めします。 アドバイスありがとうございます。 おっしゃるとおり、氏名・部署マスタを作成するのが王道だと思います。 ですが、いろいろな理由で、御提案いただいたSUMPRODUCT 関数を使用させていただけないでしょうか。 =INDEX(Sheet1!$B:$B,SUMPRODUCT(MAX((A2=Sheet1!$C$1:$C$100)*(Sheet1!$B$1:$B$100<>"実績なし")*ROW($A$1:$A$100))),1) 今後ともよろしくお願いします。 ありがとうございました。