MS-Office Excel を使おう

Q&A

注意：

Q1.大量のレコードが入力されている！表示するレコードを絞り込みたい！

A1.オートフィルタを利用する。

表の表題（系列名）を選択した状態で、[データ]メニューの[フィルタ]をポイントし、[オートフィルタ]をクリック！

例

Q2.大きな表を編集するとき、行見出しや表見出しを画面にずっと表示させておきたい！

A2.ウィンドウ枠を固定します。 　

セルまたは列、行を選択して 　[ウィンドウ]メニューの[ウィンドウ枠の固定]をクリックします。

• [セル] 選択したのがセルの場合はそのセルの左と上が固定されます。 　
• [列] 選択したのが列の場合はその左側が固定されます。 　
• [行] 選択したのが行の場合はその上側が固定されます。

Q3.セル内で複数行表示はできますか？

A3. セルをクリックした後、[書式]メニューから[セル]を選択します。ダイアログが表示されたら、[配置]タブをクリックします。[文字の制御]項目にある[折り返して全体を表示する]チェックボックスにチェックを入れます。

Q4.セル内で複数行表示は出来る、ではセル内で改行するには？

A4. これは、A3の設定をしなくても複数行表示になります。セル内で改行したい場所にポインタをおき、その位置で[Alt]+[Enter]キーとしてみてください。

Q5.シートを越えた参照の仕方を教えてください。

A5.別のシートのセルを参照する場合も、同じシート内のセルの場合とほとんど同じです。
まず、数式を入力したい場合は「=」を、関数を入力したい場合はツールバーの「関数貼り付け」ボタンを押し、数式又は関数を編集します。 他のセルを参照したい場合は、参照元のセルをクリックします。別のシートのセルを参照する場合は、別のシートに移動してからセルをクリックします。 数式又は関数の編集が終わったら「OK」をクリックします。これでシートを超えた参照ができます。

Q6.時間合計が24時間を超える場合、『28時間40分』の様に累積時間数をそのまま表示したい

A6.「セル書式設定」を開き、「表示形式」の「ユーザー定義」を選択し、累積表示にしたい部分を大括弧で括ります。次のような書式制御をすれば、累積時間が表示できます。

[h]:mm

セルに「20:00」と「8:40」の合計を取った場合「28:40」と累積表示されます。この書式制御を使わないと「1日と4時間40分」が答えとなり、表示は「4:40」となります。

分の累積表示の場合は、

[m]:ss

のように分を表す制御文字mを大括弧で修飾すれば、65分などの1時間を超える分の表示が可能になります。

Q7.コメントを印刷するには

A7.「ファイル(F)」メニューの「ページ設定(U)」から、「シート」タブを開き、「コメント(M)」のドロップダウンリストから「シートの末尾」または「表示イメージ」を選択します。「表示イメージ」を選択した場合は、予めコメントを表示しておく必要があります。

Q8.数式の入ったセルを一括で選択するには

A8.「編集(E)」メニューの「ジャンプ(G)」をクリック。[ジャンプ]ダイアログの[セル選択]ボタンを押す。自分の探したいタイプを選択する。
ジャンプコマンドを利用する前に、特に範囲を指定しない場合は、数式の入ったセル全部が一括で選択できる。

Fig.8-1 ［ジャンプ］ダイアログの［選択オプション］

Q9.検索関数を使って、振り仮名から正式名称を取り出す

A9.Sheet1のA列に取引先の正式名称が入力されており、B列に振り仮名が入力されているものとする。この表をSheet2から振り仮名で検索して正式名称を取り出せるようにしたい。

Fig.9-1 Sheet1の企業名リスト

直感的には、VLookup関数を使用して操作できそうなので、まずSheet2にVLookup関数を作成してみる。結果は、#N/Aが表示されエラーとなる。原因は2つあり、VLookup関数は、左端列を検索対象とするため、フリガナ列を左端にしなければならないことと、更にデータが昇順に並び替えられていないと正しく検索されないためである。
Lookup関数を利用した場合は、検索対象が表のどの位置にあっても構わないが、やはり、並べ替えは必須。

A2に検索値としてフリガナ“ジュンサイカブシキガイシャ”を入力したものとして試してみる。
VLookup関数を上図の表に対して実行すると、#N/Aエラーとなる。
=VLookup(A2,Sheet1!$A$1:$B:$8,1,1)

A列とB列を入れ替え、フリガナを昇順に並び替えた後では、
=VLookup（A2,Sheet1!$A$1:$B:$8,2,1)
この式で、成功する。

Lookup関数では、フリガナを昇順にさえしておけば、次の式で成功する。
=Lookup（A2,Sheet1!$B$2:$B$8,Sheet1:$A$2:$A$8)

では、元の表を生かしたまま正しい結果を表示できないかというと、Match関数を利用すれば可能になる。Match関数も基本的には昇順、降順に並んだデータリストから一致するセルを検索できるが、完全一致を指定した場合にはワイルドカードを指定できるため、あいまい検索が可能になる。
=Match("*" & A2 & "*",$B$2:$B$8,0)

但し、Match関数が返す値は検索対象になる表中の相対位置なので、セルの値を参照するためには、Index関数と組み合わせて使う。
=Index（$A$2:$B$8,Match("*" & A2 & "*",$B$2:$B$8,0),1)

この記事は、派遣スタッフからの質問に対する回答です。
質問のある方はメールをご利用下さい。登録スタッフの方でサンプルの必要な方はこちらまで。 　　　　

2007/06/22 パワレイド

Q10.関数を使って1行おきにセルを参照するには？

A10.Address関数とIndirect関数を使用します。
1行おきに、A1,A3,A5...（又はA2,A4,A6...）とセルを参照する方法を考えよう。A列にはデータが入っており、その奇数行（又は偶数行）の値だけを参照したい。整数nを使って、奇数は2n-1（偶数は2n）とかけるので、このnを行数と思えば、2*ROW(A1)-1=1, 2*ROW(A2)-1=3, 2*ROW(A3)-1=5,...となる。

Address関数にこの関係を使って
    =Address( 2*ROW($A1)-1, COLUMN($A$1) )  →  $A$1
    =Address( 2*ROW($A2)-1, COLUMN($A$1) )  →  $A$3

Indirect関数の引数にAddress関数の返すセル位置を表す文字列を指定すれば、その値を間接参照して呉れるので、
    =Indirect( Address( 2*ROW($A1)-1, COLUMN($A$1) ) )
のようにすれば、セルの値が表示されるようになる。

この処理はスタッフの質問へ回答した時点での発想です。「何行おきに...」という処理をしたいときは、MOD関数も試す価値アリです。

2009/09/17 パワレイド

Q11.（関数を使わず手作業で）1行おきにコピーして表を2つに分割するには？

A11.補助列を利用して並べ替えてから、コピー&ペーストします。
表の右側に「番号」「分類」の二列を作成する。「番号」列はデータの1行目から最終行までオートフィル機能で連続データを作成し「1,2,3,…,n」と入力する。「分類」列には1行目のデータには「A」、二行目のデータには「あ」となるように入力し、フィルハンドルをダブルクリックして、データの最終行まで「A,あ,A,あ,…,A,あ」と繰り返し入力する。
すると、奇数番目のデータの「分類」には「A」、偶数番目のデータには「あ」が割り振らた状態になる。
次に、表全体を並べ替える。「データ(D)」→「並べ替え(S)」から最優先されるキーに「分類」、2番目に優先するキーに「番号」を指定して表を並べ替える。
「分類」キーを最優先するので、「分類」が「A」の行（元の奇数行）と「あ」の行（元の偶数行）が分離される。
あとは、分類が「A」と「あ」のグループをまとめてコピー&ペーストすれば、完成。
元の順番に戻すときには、「番号」順に並べ替えをすればよい。

2009/10/28 パワレイド

Q12.単位付きのデータを数値と単位に分離するには？

A12.「データ」メニューの「区切り位置」コマンドを使用します。
例えば、C列にデータが「1個，12個，42個，8個，...」の様に、「個」という単位を伴って入力されているとする。

(1)D列に空白列を挿入する。
(2)C列全体を選択する。
(3)「データ(D)」メニューから「区切り位置(E)」コマンドを実行する。
(4)［区切り位置指定ウィザード 1/3］で［カンマやタブなどの区切り文字によって...］オプションを選択し、［次へ >］を押す。
(5)［区切り文字］グループの［その他］チェックボックスをONにし、テキストボックスに「個」を指定したら、［次へ >］を押す。
(6)次のステップで、分割後の各列の書式を「G/標準」・「文字列」・「日付」・「削除」などのオプションから指定できるが、通常「G/標準」のままで構わない。

「個」以外に「セット」等の単位も併用されている場合は、(2)〜(6)の処理を(5)のテキストボックスに「セ」と入力して実行すればよい。

複数の単位を一括除去する配列数式を使った例も紹介しています。この方法がスマートなのかどうかは分かりませんが、興味のある方はこちらも参考にして下さい。質問のある方はメールをご利用下さい。

2009/11/10 パワレイド

Q13.別のブックから挿入したシートの数式が、コピー元のブックにリンクされてしまうのを解除したい

A13.置換コマンドで数式を編集します。
既存のExcelブック(1)からワークシートの「移動またはコピー(M)」を実行して、編集中のExcelブック(2)にワークシートを追加する。このとき(1)のシートに数式が含まれていた場合、(2)のブックから(1)のブックに含まれるセルを参照してしまう。
これを解除し、(2)のブックに含まれるセルを参照するようにするには数式を再編集する必要があり、複数の式が含まれていると一回の操作で済ましたくなる。
そこで、「編集(E)」メニューの「置換(E) Ctrl+H」コマンドを利用する。

便宜上、(1)と(2)のファイルを次の名前にしておく。
(1)コピー元.xls，ワークシート：入力, 見積書, 契約書
(2)編集中.xls, ワークシート：入力, 見積書

(1)の契約書シートを右クリックして「移動またはコピー」コマンドを実行、［シートの移動またはコピー］ダイアログで、［移動先ブック名(T)］に(2)編集中.xlsを指定し、［挿入先(B)］に［（末尾へ移動）］を指定、［コピーを作成する(C)］チェックボックスをONにする。

すると、(2)編集中.xlsに「契約書」シートが追加される。但し、契約書シート中に含まれる式は、「=[コピー元.xls]入力!D9」のように「=ブック名＋シート名＋セル位置」の形で参照される。
これを編集中.xlsブック中で完結するには、“[コピー元.xls]”を“空白”と置き換えるか、“[コピー元.xls]入力”までを“入力”と置き換える。そうすれば式は「=入力!D9」となり、同じブック内のセルを参照するようになる。
「編集(E)」メニューの「置換(E) Ctrl+H」を実行し、［検索する文字列(N)］に“[コピー元.xls]入力”、［置換後の文字列(E)］に“入力”と入力し、［すべて置換(A)］ボタンを押す。
以上の操作で完了。後は式の入ったセルをチェックする。

追記:
1. 数式の一部を置換してもエラーにならない限り、セルには何かしらの値が表示されるため正しく置換されたかどうかは、セルを選択して数式バーの内容を確認する必要がある。
2. 「ツール」メニューの「オプション」コマンドを実行し、［表示］タブ→［ウィンドウオプション］→［数式R］チェックボックスをONにすれば、式の結果ではなく、入力した式そのものを表示するようになるので、複数の式が正しく置換されているか確認しやすくなる。
3. 置換完了後に値が変更されない場合は、F9キーを押して再計算させてみる。

質問のある方はメールをご利用下さい。

2009/11/17 パワレイド

Q14.セルに日付に基く曜日を表示するには？

A14.表示形式のユーザー定義を利用します。
日付には、そもそも曜日の情報も含まれている（日付が決まれば曜日も決まる）ので、表示形式のユーザー定義で曜日を表示するように書式制御する。
B列に日付が入力されているとする。C列に対応する曜日を入力しよう。このとき、最初の曜日だけ入力してオートフィル機能で連続データを作成しても良いが、今回は、C列にB列を参照する式を入力する。
例えば、C4には「=B4」と隣のセルへの参照だけを入力する。こうするとC列とB列は同じ日付が表示される。 C列を選んで、「書式(O)」→「セル Ctrl+1」コマンドを実行すると、［セルの書式設定］ダイアログ（下図）が開く。

Fig.14-1 セルの書式設定ダイアログ
C4がアクティブセル。数式バーを見ると、名前ボックスにC4と表示され、内容が「=B4」という式であることが分かる。

［表示形式］タブに切り替え、［分類(O)：］リストから［ユーザー定義］を選択し、［種類(T)：］のテキストボックスに“aaa”と入力する。［サンプル]の表示が日付から曜日一文字に変わったら［OK］ボタンをクリックする。
この方法は必ず参照もとの日付データと一致する曜日が表示されるので、日付部分を再入力すれば、曜日も自動的に更新される。

    aaa → 漢字1文字の曜日，aaaa → 漢字3文字の曜日
    ddd → 英語略表記(Mon)，dddd → 英語表記（Monday）

もちろん「年月日」と「曜日」の書式制御文字を組み合わせて、“yyyy/m/d ddd”とすれば“2009/12/9 Wed”と一つのセルに表示することも出来る。

質問のある方はメールをご利用下さい。

2009/12/09 パワレイド

Q15.Find関数で、複数の検索文字列との比較結果をすべて知るには？

A15.各検索文字列との比較結果を複数列にわたって表示します。
下図のように、文字と数字で構成されている「文字列」から「0〜9」の何れかが含まれているかテストしてみる。

Fig.15-1 Find関数による数字の検出

B列に対象となる文字列（ここではOffice製品の名前）を入力し、C1〜L1に入力された0〜9のそれぞれが見つかるか、Find関数を作成する。

例えば、B2の値「Excel2002」からC1の値「0」を検索するには「C2」セルに

=FIND(C1,B2)

とすれば、初めて「0」の現れる位置「7」が返される。次に、D1の値「1」を検索するには「D2」セルに

=FIND(D1,B2)

とすると、「1」は含まれていない為「#VALUE!」エラー値が返される。L1の値「9」まで同様に式を訂正しても良いが、FIND関数では複数個の検索文字列を範囲で与えることも出来るので、次のように1つの式にまとめられる。「C2」セルに次のようなFind関数を入力する。

=FIND($C$1:$L$1,$B2)

これを、「L2」までコピーすればよい。
すると「C2」には「B2からC1の検索結果」、「D2」には「B2からD1の検索結果」、「E2」には「B2からE1の検索結果」、...「L2」には「B2からL1の検索結果」が表示される。

質問のある方はメールをご利用下さい。

2010/06/11 パワレイド