フィルタの抽出結果の表示された行のみに自動的に連番を付ける
添付ファイルを参照してください。
データの横に連番を表示している場合、オートフィルタで絞り込むと連番がくずれてしまいます。
SUBTOTAL関数を使うと常に表示されているデータのみの連番を自動的に「1・2・3…」と表示することができます。
セルB2に「=SUBTOTAL(3,$C$2:C2)」という数式を入力し、これをデータの最終セルB26までコピーします。
この数式では、データの個数を返す数式を、開始セルを絶対参照として、最終セルを相対参照で指定していることがポイントとなります。
コピされた数式では、
B3→SUBTOTAL(3,$C$2:C3)
B4→SUBTOTAL(3,$C$2:C4)
B8→SUBTOTAL(3,$C$2:C8)
となります。
SUBTOTAL関数は、Excel 2002以前のバージョンでは使用できませんから注意してください。
オートフィルタによって自動的に隠される非表示行は、常に計算の対象から除外されます。
SUBTOTAL関数の説明は「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
「数学+三角」
SUBTOTAL
http://kiyopon.sakura.ne.jp/kansuu/abs.html#SUBTOTAL
を、参照してください。
オートフィルターの、”以上””より大きい”とかが使用できません
データを文字列から数値に変更すればいいですね。
参考になる質問が「質問と回答」http://kiyopon.sakura.ne.jp/situmon/index.htm
の「計算式・関数」「2007/06/04」
「文字列として入力された数字データを数値に変換する」にあります。
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
その内容
関係のないセルに1と数値を入力し、このセルを選択し、「編集」「コピー」します。
次に、「編集」「ジャンプ」で、「セル選択」ボタンを押し、「定数」のラジオボタンを押し、「ＯＫ」ボタンを押します。
これで、文字列をすべて選択できました。
「編集」「形式を選択して貼り付け」を選択し、「演算」グループの「乗算」にラジオボタンを押して、「ＯＫ」ボタンを押します。
これで、文字列として入力された値が、数値になります。
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
反対に、数値は、「で始まる」の検索はできません。
参考になる質問が「質問と回答」http://kiyopon.sakura.ne.jp/situmon/index.htm
「データベース」「2004/02/14」
「フィルタで、数値452145が「4」「で始まる」で検索できない」にあります。
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
その内容
Excelでワイルドカード文字や比較演算子を使って目的のデータの一部(先頭または末尾の文字列、含まれる文字列)を検索条件として指定する場合、文字列値のみが検索され、数値データは検索されません。
したがって、数値データの検索をする場合は、セルの値を文字列に変換する必要があります。
「エクセルで使えるソフト」の「アポストロフィー」
http://kiyopon.sakura.ne.jp/soft/appostrofie.htm
を使えば、セルの値の先頭にアポストロフィー[']を追加して、数値を文字列として認識させることができます。
アドインソフトを使用する場合の注意
http://kiyopon.sakura.ne.jp/soft/addin.html
を必ずお読みください。
２行を結合したデータをフィルターで検索してデータを抽出するには
２行のセルを結合している場合は、上の行のみしかデータが入力されていませんので、下の行は検索できません。
下の行にも上の行と同じデータを入力する必要があります。
「エクセルで使えるソフト」http://kiyopon.sakura.ne.jp/soft/index.htm
に、私の作成した「セルの結合と解除」アドインソフトがあります。
マウスの右クリックのショートカットメニューで「セル結合」「解除」が簡単にできるアドインソフトです。
検索する行のセル範囲を選択し、「セル結合」「結合を解除して同上値貼付」を選択すると、選択したセル範囲のセルの結合が解除され下の行に上の行の値がコピーされます。
このあとで、データの検索をすれば、2行分のデータが正しく抽出できます。
説明はこちら
http://kiyopon.sakura.ne.jp/soft/ketugo.htm
ソフトはこちら
http://kiyopon.sakura.ne.jp/soft/ketugo.exe
注意:「アドインソフト」または「ユーザー定義関数」を利用する場合は、「アドインソフトを使う場合の注意点」
http://kiyopon.sakura.ne.jp/soft/addin.html
を必ずお読みください。
エクセル2003で作成したピボットテーブルがエクセル2010で更新エラー
「ピボットテーブルのフィールド名は正しくありません。ピボットテーブルを作成するには、ラベルの付いた列でリストとして編成されたデータを使用する必要があ
ります。
ピボットテーブルのフィールド名を変更する場合は、フィールドの新しい名前を入力する必要があります。」
というメッセージが表示されます。
2003で作成したピボットテーブルは、2010では、正しくフィールド名を使用できないことがあります。
こちらにMicrosoftの解説があります。
# Excel 2010 でサポートされていない Excel 2007 と Excel 97-2003 の機能
http://office.microsoft.com/ja-jp/excel-help/HA010342994.aspx#BM5e
* サポートされていないピボットテーブルの機能
並び替えで、「同じサイズのセルの結合が必要です」と表示される
注意メッセージ「同じサイズのセルの結合が必要です」のとおりです。
セルの結合をしてある場合は、セルのサイズが違いますので、セルの並べ替えが出来ません。
対象のセル範囲のセル結合を解除してください。
重複データを削除して別のシートに抽出する
「フィルタオプションの設定」を使い、重複データを抽出します。
この場合、B列の「苗字」とC列の「都道府県」と、D列の「性別」の3つが全て同じなら、重複しているとみなすことにします。
この場合番号3,4,5のデータが重複しています。
まず、リスト範囲(A2:D7)のいずれかのセルをクリックし、「データ」メニューから「フィルタ」 →「フィルタオプションの設定」を選びます。
「フィルタオプションの設定」ダイアログボックスで、この画面で比較するデータ範囲を指定します。
「リスト範囲」欄の右にあるボタンをクリックし、B列の「苗字」とC列の「都道府県」と、D列の「性別」が入力された列番号（ここでは「B,C,D」列）を選択します。
選択した範囲が「フィルタオプションの設定-リスト」という画面に表示されますで、その画面の右にある「×」をクリックして元のダイアログボックス「フィルタオプションの設定」を表示します。
次に、「重複するレコードは無視する」にチェックを付けて、「OK」ボタンを押します。
これで、重複するデータのうち、最初に出てくるもの以外は非表示になります。
データはそのままで行が非表示になっただけですから、重複したデータは削除されたわけではありません。
重複データを完全に削除したいときは、重複データが非表示の状態で[Shift]+[Ctrl]+[:]キーを押してデータ範囲を選択し、「編集」「コピー」して、別のシートに貼り付けます。
重複データを一気に削除する(2007)
リスト内のいずれかのセルを選択した状態で、リボンの「データ」タブを開き、
「データツール」グループの「重複の削除」ボタンをクリックすると「重複の削除」ダイアログボックスが表示されます。
「重複の削除」ダイアログボックスでは、どの項目を重複とみなすかを指定できます。
「OK」ボタンをクリックすると、指定した項目が一致する重複データが一気に削除されます。
重複データを削除しない状態に戻すには、画面の左上にある「クイックアクセスツールバー」の「元に戻す」ボタンをクリックしよう。
データを追加すると自動的にリストが追加できる表を作る
「リストの作成」は、Excel2003以降で追加されたメニューです。
既存の表を「データ」の「リスト」から「リストの作成」をすると、（エクセル2007の場合は「挿入」「テーブル」「テーブル」で）
データを追加した場合に自動的に書式や計算式などが、新規 のデータ入力行にコピーされ、書式や計算式のコピーをする手間がなくなります。
また、リストはオートフィルタの指定も自動的に行われ、データの検索も簡単になります。
「リスト」に変換する
すでに作成してある表の範囲(項目名を含んで選択すると後でオートフィルタの指定がわかりやすくなります。)を選択し、「データ」「リスト」を選択し、
「リストの作成」（エクセル2007の場合は「挿入」「テーブル」「テーブル」）を選択します。
この場合、リストは行の先頭に項目名があり、下の行に順に入力する形式で、最終行の合計などはリスト範囲に入りません。
「リストの作成」ダイアログボックス（エクセル2007の場合はテーブルの作成）に「リストに変換するデータ範囲を選択してください」で、作成されたリストが範囲になっていることを確認し(あるいは
範囲を選択し直し)、「OK」ボタンを押します。
表のデータが「リスト」に変換され、「*」が付いた行が新規にデータを追加する行になります。
「リスト」の変換されたセル範囲には、「青」い枠線が表示されます。
リスト内の任意のセルを選択すると、「*」の新規入力行の表示と、「リスト」ツールバーが表示されます。
(リスト範囲以外のセルを選択すると、「*」の新規入力行の表示と、「リスト」ツールバーが表示されません。)
リストの見出しにはオートフィルタの「▼」矢印が表示され、データの検索ができます。
リストの各セルの書式や計算式は自動的に新規の入力行コピーされ採集の集計「合計」などの計算式も自動的に拡張されます。
タイトル行の文字は「太字」に変換されます。
罫線を設定したリストの場合は、罫線は自動的に引き継がれない場合があります。必要に応じて罫線を入力します。
「*」の行にデータを新規に入力すると「このリストによってワークシートの行が挿入されました。これによって、リストより下のセルのデータ
が下方向に移動する可能性があります。」とメッセージが表示されます。
「次回からこのダイアログボックスを表示しない」にチェックを入れ、「OK」ボタンを押します。
リストにデータが入力され、リスト範囲も下に拡張され、次の入力行に「*」が表示されます。
計算のセルがあれば、自動的に計算式もコピーされ結果が反映されます。
合計行が無いリストでも、集計行を表示したいときは、「リスト」ツールバーで「集計行の表示/非表示」を選択します。
▼ボタンで、集計の方法を選択できます。
「リスト」を解除する
すでにリストに変換したデータ範囲を標準に範囲に戻すには、リスト内の任意のセルをクリックし、「リスト」ツールバーの「リスト」「範囲に変換」を選択
し、「リストを標準の範囲に変換しますか？」で、「はい」ボタンを押します。
（いったんリストに変換するとタイトル行の文字は「太字」のままになりますので、必要により「書式」「セル」「セルの書式設定」（エクセル2007の場合は、「ホーム」「セル」「書式」「セルの書式設定」）で、フォントのスタイルを
変更してください。）
同じ日付に対して売上の総合計を求めたい
「データ」「集計」を使います。
データリストのいずれかのセルを選択し、「データ」「集計」を選択します。
「集計の設定」ダイアログボックスが表示されますので、「グループの基準」に「該当日付」を、「集計の方法」に「合計」を、「集計するフィールド」に「合計/売上」と「合計/粗利」を選
択し、「現在の集計表と置き換える」「集計行をデータの下に挿入する」のチェックを入れ「ＯＫ」ボタンを押します。
「該当日付」毎に集計されて、行番号の左に「アウトライン」が追加されます。
アウトラインの番号[1]で「総計」のみ、[2]で「集計」のみ、[3]ですべてのデータが表示できます。
データ集計については、「エクセルの操作説明」
http://kiyopon.sakura.ne.jp/sousa/index.html
の「自動集計」
http://kiyopon.sakura.ne.jp/sousa/Pg000077.htm
を参照してください。
頭文字3文字程で検索をかけることは可能ですか
検索の方法が示してありませんので、的確な回答ができません。
「データ」「フィルタ」の「オートフィルタ」では、次のようになります。
リスト範囲のいずれかのセルを選択し、「データ」「フィルタ」「オートフィルタ」を選択し、該当の項目名のドロップダウンリストの「オプション」を選択します。
「オートフィルタオプション」ダイアログボックスで、任意の文字列を表すにはアスタリスク（＊）を使用します。
例えば、
=岡崎市*
と等しい
とすれば、先頭の3文字に"岡崎市"を含む文字列が抽出されます。
具体的には、「エクセルの操作説明」
http://kiyopon.sakura.ne.jp/sousa/index.htm
の「データベース 」
「任意条件の指定におけるワイルドカード文字の利用 」
に説明があります。
オートフィルタの抽出結果のデータだけを自動的に合計したい
SUM関数では、抽出結果のデータだけでなく非表示セルのデータも集計してしまいます。
フィルタの抽出結果のデータだけを集計したい場合は、SUM関数ではなくSUBTOTAL関数を使います。
SUBTOTAL関数は、非表示のデータを計算対象から除外して「合計」「セル数」「平均」などさまざまな計算
を行うことができます。
SUBTOTAL関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「数学+三角」SUBTOTAL
http://kiyopon.sakura.ne.jp/kansuu/abs.html#SUBTOTAL
を参照してください。
その内容
書式 =SUBTOTAL(集計方法,範囲1,範囲2,...)
集計方法リストの集計に使用する関数を、1から11の番号で指定します。
集計方法 関数
1 AVERAGE関数（セル範囲の平均を返す）
http://kiyopon.sakura.ne.jp/kansuu/avedev.html#average
2 COUNT関数（数値が入力されているセルの個数を合計を返す）
http://kiyopon.sakura.ne.jp/kansuu/avedev.html#COUNT
3 COUNTA関数（空白セル以外のセルの個数を合計を返す）
http://kiyopon.sakura.ne.jp/kansuu/avedev.html#COUNTA
4 MAX関数（セル範囲の最大値を返す）
http://kiyopon.sakura.ne.jp/kansuu/avedev.html#MAX
5 MIN関数（セル範囲の最小値を返す）
http://kiyopon.sakura.ne.jp/kansuu/avedev.html#MIN
6 PRODUCT関数（数値の積を返す）
http://kiyopon.sakura.ne.jp/kansuu/abs.html#product
7 STDEV関数（母集団の標本を使って標準偏差を返す）
http://kiyopon.sakura.ne.jp/kansuu/avedev.html#STDEV
8 STDEVP関数（母集団全体を対象に標準偏差を返す）
http://kiyopon.sakura.ne.jp/kansuu/avedev.html#STDEVP
9 SUM関数（合計を返す）
http://kiyopon.sakura.ne.jp/kansuu/abs.html#sum
10 VAR関数（母集団の標本を使って分散を返す）
http://kiyopon.sakura.ne.jp/kansuu/avedev.html#VAR
11 VARP関数（母集団全体を対象に分散を返す）
http://kiyopon.sakura.ne.jp/kansuu/avedev.html#VARP
範囲1, 範囲2, 集計するリストの範囲を 1 から 29 個まで指定します。
解説
範囲 内に他の集計値が挿入されている場合、ネストされている集計値は、計算の重複を防ぐために無視されます。
リストを抽出した結果として非表示になっている行は無視されます。このため、抽出されたリストに表示されているデータだけを集計すること
ができます。
参照先に 3-D 参照が含まれている場合は、エラー値
#VALUE! が返されます。
使用例
SUBTOTAL(9,C3:C5) では、SUM 関数を使用してセル範囲
C3:C5 の集計値を返します。
重複した商品番号がたくさんあります。これを、商品番号毎に集計したい
ピボットテーブルを使います。
添付ファイルを参照してください。
項目名を含んで、セル範囲A9:D34まで選択し、「データ」「ピボットテーブルとピボットグラフレポート」(エクセル2007の場合は「挿入」「テーブル」「ピボットテーブル」「ピボットテーブル」)を選択します。
「ピボットテーブル／ピボットグラフウィザード-1/3」ダイアログボックスの「分析するデータのある場所を選択してください。」で、
Excelのリスト／データベースを選択し、「作成するレポートの種類を指定したください。」の「ピボットテーブル」を選択して、「次へ」ボタンを押します。
「ピボットテーブル／ピボットグラフウィザード-2/3」ダイアログボックスの「範囲」に項目名を含んで正しくデータベースの範囲が選択されていることを確認し「次へ」ボタンを押します。（違っている場合は、セル範囲を選択し直します。）
「ピボットテーブル／ピボットグラフウィザード-3/3」ダイアログボックスの「ピボットテーブルの作成先を指定してください。」で「新規ワークシート」あるいは「既存のワークシート」（この場合は集計を表示する範囲の「セルの左上端」を指定します。）を選択し、「完了」ボタンを押します。
ピボットテーブルのフィールドリスト（メニューバーのように表示されている項目名のリスト）から、「商品番号」の項目を「ここに行のフィールドをドラッグします」というエリアにドラッグします。
「単位」の項目を「商品番号」の右隣のセル境界にドラッグします。
集計したい「数量」の項目を「ここにデータアイテムをドラッグします」というエリアにドラッグします。
各商品番号の計が表示されていますので、これを表示しないようにするため、ピボットテーブルの商品番号のいずれかのセルを選択し、「ピボットテーブルツールバー」の「フィールドの設定」を選択し、「集計」の「自動」のラジオボタンがおされていますので、「なし」のラジオボタンを押し、「ＯＫ」ボタンを押します。
「数量」の表示形式を小数点以下１桁までにするため、ピボットテーブルの「数量」のいずれかのセルを選択し、「ピボットテーブルツールバー」の「フィールドの設定」を選択し、「表示形式」を選択し、「分類」ので「数値」を選択し、「小数点以下の桁数」を「１」として「ＯＫ」ボタンを押します。
ピボットテーブルで集計したデータは、元のデータに変更が加えられた場合でも、ピボットテーブルが自動で更新されることはありません。ピボットテーブルを更新（再計算）するには、ピボットテーブルの任意のセルを1つ選択し、「ピボットテーブルツールバー」の「データの更新」ボタンを押してください。
エクセルのデータをワードに差し込んだ場合の日付表示を平成19年3月31日とするには
| |
A |
B |
C |
| 1 |
連番 |
契約日 |
更新日 |
| 2 |
No-01 |
平成18年3月1日 |
平成19年3月31日 |
| 3 |
No-02 |
平成18年4月1日 |
平成19年4月30日 |
| 4 |
No-03 |
平成17年8月1日 |
平成18年8月31日 |
| 5 |
No-04 |
平成15年11月1日 |
平成16年11月30日 |
| 6 |
No-05 |
平成18年1月1日 |
平成19年1月31日 |
計算結果の日付をTEXT関数で文字列とします。
シート「Sheet2」
セルC2には、計算結果の日付を文字列で"2007年3月1日"と表示するため
=TEXT(EOMONTH(B2,12),"yyyy年m月d日")
と入力します。
"平成19年3月1日"
と表示するには、
=TEXT(EOMONTH(B2,12),"ggge年m月d日")
と入力します。
このセルをコピーして下のセル範囲に貼り付けます。
TEXT関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「文字列関数」TEXT
http://kiyopon.sakura.ne.jp/kansuu/val.html#text
を参照してださい。
「得意先名」「年」「物件名」別に「売上金額」「実績金額」「損益」を集計する
添付ファイルを参照してください。
ピボットテーブルを使います。
データ範囲(A2:F16)を選択し、
「データ」「ピボットテーブルとピボットグラフレポート」(エクセル2007の場合は「挿入」「テーブル」「ピボットテーブル」「ピボットテーブル」)を選択します。
「ピボットテーブル／ピボットグラフウィザード-1/3」ダイアログボックスの「分析するデータのある場所を選択してください。」で、Excelのリスト／データベースを選択し、「作成するレポートの種類を指定したください。」の「ピボットテーブル」を選択して、「次へ」ボタンを押します。
「ピボットテーブル／ピボットグラフウィザード-2/3」ダイアログボックスの「範囲」に項目名を含んで正しくデータベースの範囲が選択されていることを確認し「次へ」ボタンを押します。（違っている場合は、セル範囲を選択し直します。）
「ピボットテーブル／ピボットグラフウィザード-3/3」ダイアログボックスの「ピボットテーブルの作成先を指定してください。」で「新規ワークシート」あるいは「既存のワークシート」（この場合は集計を表示する範囲の「セルの左上端」を指定します。）を選択し、「完了」ボタンを押します。
ピボットテーブルのフィールドリスト（メニューバーのように表示されている項目名のリスト）から、「得意先」、「年」と「物件名」を「ここに行のフィールドをドラッグします」というエリアにドラッグします。
集計したい「売上金額」と「実績金額」と「損益」の項目を「ここにデータアイテムをドラッグします」というエリアにドラッグします。
「物件名」を「二輪その他」「４輪その他」「6輪BOX」の順に並べ替える方法「ツール」「オプション」の「ユーザー設定リスト」タグを開き、「リストの項目」に「二輪その他,４輪その他,6輪BOX」のように、カンマで項目名を区切って入力し、「追加」ボタンを押し、「ＯＫ」ボタンを押します。
ピボットテーブルの「物件名」のリスト範囲を選択し、「データ」「並べ替え」を選択し、「オプション」ボタンを押し、「並べ替え順序の指定」で、「▼」ボタンを押し、「二輪その他,４輪その他,6輪BOX」を選択し、「方法」グループで、「ふりがなを使わない」を選択し、「降順」のラジオボタンを押して、「ＯＫ」ボタンを押します。
集計行を表示しないために、集計行を選択し、「ピボットテーブル」メニューバーの「ピボットテーブル」の「集計」のチェックを外します。
ピボットテーブルで集計したデータは、元のデータに変更が加えられた場合でも、ピボットテーブルが自動で更新されることはありません。ピボットテーブルを更新（再計算）するには、ピボットテーブルの任意のセルを1つ選択し、「ピボットテーブルツールバー」の「データの更新」ボタンを押してください。
「コード」と「証券会社」と「株数」のデータから「コード」「証券会社」をキーにして「株数」の集計表を作る
ピボットテーブルを使います。
添付ファイルを参照してください。
| |
A |
B |
C |
D |
| 1 |
コード |
株数 |
証券会社名 |
|
| 2 |
1111 |
200 |
Ａ |
|
| 3 |
2222 |
1,000 |
Ｂ |
|
| 4 |
1111 |
2,000 |
C |
|
| 5 |
1155 |
0 |
Ａ |
|
| 6 |
1155 |
0 |
Ｂ |
|
| 7 |
1155 |
0 |
C |
|
| 8 |
|
|
|
|
| 9 |
|
|
|
|
| 10 |
合計 / 株数 |
証券会社名▼ |
|
|
| 11 |
コード▼ |
Ａ |
Ｂ |
C |
| 12 |
1111 |
200 |
|
2000 |
| 13 |
1155 |
0 |
0 |
0 |
| 14 |
2222 |
|
1000 |
|
まず、項目名を含んでデータの範囲を選択します。
この場合セル範囲A1:C7を選択し、「データ」「ピボットテーブルとピボットグラフレポート」(エクセル2007の場合は「挿入」「テーブル」「ピボットテーブル」「ピボットテーブル」)を選択します。
「ピボットテーブル／ピボットグラフウィザード-1/3」ダイアログボックスの「分析するデータのある場所を選択してください。」で、Excelのリスト／データベースを選択し、「作成するレポートの種類を指定したください。」の「ピボットテーブル」を選択して、「次へ」ボタンを押します。
「ピボットテーブル／ピボットグラフウィザード-2/3」ダイアログボックスの「範囲」に項目名を含んで正しくデータベースの範囲が選択されていることを確認し「次へ」ボタンを押します。（違っている場合は、セル範囲を選択し直します。）
「ピボットテーブル／ピボットグラフウィザード-3/3」ダイアログボックスの「ピボットテーブルの作成先を指定してください。」で「新規ワークシート」あるいは「既存のワークシート」（この場合は集計を表示する範囲の「セルの左上端」セルA10を指定します。）を選択し、「完了」ボタンを押します。
ピボットテーブルのフィールドリスト（メニューバーのように表示されている項目名のリスト）から、「コード」の項目を「ここに行のフィールドをドラッグします」というエリアにドラッグします。
「証券会社名」の項目を「ここに列のフィールドをドラッグします」というエリアにドラッグします。
集計したい「株数」の項目を「ここにデータアイテムをドラッグします」というエリアにドラッグします。
行と列の合計を表示しないようにするには、ピボットテーブルの行と列の交点(この場合セルA10の「合計/株数」のボタン)
マウスの右ボタンでクリックして「オプション」を選択するか、ピボットテーブルメニューバーの「ピボットテーブル」「オプション」を選択します。
「書式オプション」の「列の総計」「行の総計」のチェックを外し、「ＯＫ」ボタンを押します。
項目名の「証券会社名」の順に並べ替えるには、項目名を含んでセル範囲B11:D14を選択し、「データ」「並べ替え」を選択し、「ＯＫ」ボタンを押します。
ピボットテーブルで集計したデータは、元のデータに変更が加えられた場合でも、ピボットテーブルが自動で更新されることはありません。ピボットテーブルを更新（再計算）するには、ピボットテーブルの任意のセルを1つ選択し、「ピボットテーブルツールバー」の「データの更新」ボタンを押してください。
性別、年齢、日数のデータリストから、男20〜30歳の平均日数を返す
添付ファイルを参照してください。
DAVERAGE関数を使います。
| |
A |
B |
C |
D |
| 1 |
性別 |
年齢 |
日数 |
|
| 2 |
女 |
41 |
5 |
|
| 3 |
男 |
20 |
10 |
|
| 4 |
女 |
50 |
6 |
|
| 5 |
女 |
31 |
5 |
|
| 6 |
男 |
45 |
9 |
|
| 7 |
|
|
|
|
| 8 |
|
|
|
|
| 9 |
|
|
|
|
| 10 |
|
|
|
|
| 11 |
性別 |
年齢 |
年齢 |
|
| 12 |
男 |
>=20 |
<=30 |
10 |
セルD12には、
=DAVERAGE($A$1:$C$6,3,A11:C12)
数式の意味
セル範囲$A$1:$C$6のリストから、検索条件範囲A11:C12に合致する、3列目の項目の平均値を返します。
リスト範囲と検索条件では項目名が同じでないと正しく検索が出来ません。
検索条件では、同じ行に入力されている条件はAND条件です。（全ての条件に該当する場合に抽出されます）
DAVERAGE関数の説明は「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「データベース」のDAVERAGE
http://kiyopon.sakura.ne.jp/kansuu/database.html#daverage
を参照して下さい。
リストの中から区分１の「○」に該当、かつ、区分２の「2〜4」に該当、かつ、年令が「60才以下」の人数の求め方
添付ファイルを参照してください。
データベース関数を使います。
| |
A |
B |
C |
D |
E |
F |
G |
H |
| 1 |
区分１ |
区分２ |
年令 |
|
区分１ |
区分２ |
年令 |
4 |
| 2 |
○ |
2 |
60 |
|
○ |
<=2 |
<=60 |
|
| 3 |
○ |
5 |
63 |
|
○ |
>=4 |
<=60 |
|
| 4 |
● |
3 |
61 |
|
|
|
|
|
| 5 |
○ |
4 |
59 |
|
|
|
|
|
| 6 |
○ |
2 |
58 |
|
|
|
|
|
| 7 |
● |
2 |
52 |
|
|
|
|
|
| 8 |
● |
1 |
61 |
|
|
|
|
|
| 9 |
○ |
1 |
60 |
|
|
|
|
|
セルE1:G3に検索条件を、第一行目に項目名を含んで入力します。
セルH1には、
=DCOUNT(A1:C9,B1,E1:G3)
と入力します。
数式の意味
セル範囲A1:C9に対して、検索条件範囲E1:G3の条件に合致する、B1項目(区分２)
に数値の入力されたセルの数を返します。
DCOUNT関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「データベース」DCOUNT
http://kiyopon.sakura.ne.jp/kansuu/database.html#dcount
を参照してください。
同じレイアウトの各シートのセルの和を他のシートに集計したい
同じレイアウトのシートの場合は、串刺し計算による統合集計ができます。
添付ファイルを参照してください。
「１日」「２日」「３日」のワークシートの表について、串刺しするように「月計」のワークシートに、統合集計しています。
操作方法
１ 統合集計先のワークシート「月計」（行見出しや列見出しが何も入力してない白紙のシートでもＯＫです）のセルA2を選択します。
２ 「データ」「統合」を選択します。
３ 「集計の方法」を「合計」とし、「統合元範囲」のテキストボックスの中をクリックして、シート見出し「１日」を選択し、セル範囲「A2:E10」(行見出しと列見出しと合計列と合計行を含んだセル範囲)を選択し「追加」ボタンを押します。
４ 同様に、シート見出し「２日」を選択し、セル範囲「A2:E10」(行見出しと列見出しと合計列と合計行を含んだセル範囲)を選択し「追加」ボタンを押します。
５ シート見出し「３日」も同様に、セル範囲「A2:E10」(行見出しと列見出しと合計列と合計行を含んだセル範囲)を選択し「追加」ボタンを押します。
６ 「統合の基準」グループの「上端行」と「左端列」にチェックを入れ、「統合元データとリンクする」にチェツクを入れて、「ＯＫ」ボタンを押します。
この操作によって、元のシートのデータとリンクした集計表が作成されます。
統合したシートには、左側にアウトラインが追加されて、「１」ボタンを押すと集計データが表示され、「２」ボタンを押すと詳細データが表示されます。
「＋」ボタンを押すとその項目行の詳細データが表示され、「−」ボタンを押すとその項目の集計データが表示されます。
登録した日付順のデータを他のファイルで商品ごとに並べる
「データ」「並べ替え」で、「最優先されるキー」に「商品名」を選択すれば、商品名毎にデータが並べ替えられます。
並べ替えられたデータリストをコピーして、別のファイルに貼り付けすればいいですね。
元のデータの入力順が狂わないように、1列追加して1からの連番を振っておくとその連番で並べ替えをすれば、簡単に元の日付順に戻すことができます。
簡単に連番を振るには、はじめのデータに1と入力し、セルを選択し、そのセルの右下境界をマウスで選択するとカーソルが+になります。その状態でマウスの左ボタンを押しつつ、{Shift}キーを押しながら下にドラッグします。
数字が2,3,4．．．．．と、1ずつ増加します。
並べ替えの説明は、「エクセルの操作説明」
http://kiyopon.sakura.ne.jp/sousa/index.htm
の「データベース」「リストおよびセル範囲の並び替え 」
http://kiyopon.sakura.ne.jp/sousa/Pg000070.htm
を参照してください。
参考に、
私の作成した「項目別シート作成」というアドインソフトを使えば、商品毎のリストが別々のシートに振り分けられます。
「項目別シート作成」の説明はこちら
http://kiyopon.sakura.ne.jp/soft/datalist.htm
ご利用前に、「アドインソフト使用上の注意」
http://kiyopon.sakura.ne.jp/soft/addin.html
を必ずお読みください。
先頭の2文字の条件に合致するデータを抽出する
A列にデータ区分の数値、B列以降の列にデータが入力されているとします。
「データ」「オートフィルタ」を選択し、A列の▼で、「オプション」を選択し、「33」「で始まる」によってデータの抽出をします。
しかし、ご質問の場合は、A列に数値が入力されていますので、先頭の2文字のみを検索条件として抽出できません。
先頭の2文字を抽出するには、A列の数値を文字列として入力する必要があります。
膨大なデータを文字列として入力し直すのは大変面倒です。
私が作成した、データを一度に文字列として入力することができるアドインソフト「アポストロフィ」を使えば、選択範囲のデータを一度に文字列に変換できます。
このソフトを使う場合は、「アドインソフト使用上の注意」を必ずお読みください。
| |
A |
B |
C |
D |
| 1 |
|
上代 |
枚数 |
計 |
| 2 |
3800011 |
\59,000 |
0枚 |
\0 |
| 3 |
3300011 |
\49,000 |
0枚 |
\0 |
| 4 |
3300021 |
\39,000 |
0枚 |
\0 |
| 5 |
3400011 |
\25,000 |
0枚 |
\0 |
| 6 |
3300031 |
\49,000 |
0枚 |
\0 |
| 7 |
3300041 |
\49,000 |
0枚 |
\0 |
| 8 |
3400021 |
\27,000 |
0枚 |
\0 |
| 9 |
3400031 |
\27,000 |
0枚 |
\0 |
| 10 |
3400271 |
\24,000 |
0枚 |
\0 |
| 11 |
3500011 |
\29,000 |
0枚 |
\0 |
| 12 |
3500021 |
\29,000 |
0枚 |
\0 |
| 13 |
3300051 |
\39,000 |
0枚 |
\0 |
| 14 |
3300061 |
\39,000 |
0枚 |
\0 |
| 15 |
3400041 |
\21,000 |
0枚 |
\0 |
| 16 |
3500031 |
\24,000 |
0枚 |
\0 |
| 17 |
3200011 |
\35,000 |
0枚 |
\0 |
| 18 |
3300071 |
\39,000 |
0枚 |
\0 |
| 19 |
3300081 |
\49,000 |
0枚 |
\0 |
データの抽出方法を示します。
データリストのいずれかのセルを選択し、「データ」「フィルタ」「オートフィルタ」を選択します。
データ項目の横に▼ボタンが表示されます。
A列の▼を選択して、リストから「オプション」を選択し、「33」「で始まる」を
選択すれば、33で始まるデータ行のみが表示され、ほかのデータは、非表示となります。▼は、「青」となってデータの抽出状態を示しています。
抽出されたデータのみを他のシートやセルにコピーすることもできます。
データ範囲を選択し、「編集」「コピー」します。
貼り付け先のシートのセルの左上端を選択し、「編集」「貼り付け」します。
すべてのデータを表示するには、「(すべて)」を選択します。
データの抽出については、「エクセルの操作説明」の「データベース」を参照してください。
グループＡの中の項目ＢのデータＣを集計したい（ピボットテーブル）
「ピボットテーブル」を使えば簡単に集計できます。
→ 
具体的には、データ範囲のいずれかのセルを選択した状態で、「データ」「ピボットテーブルとピボットグラフレポート」(エクセル2007の場合は「挿入」「テーブル」「ピボットテーブル」「ピボットテーブル」)を選択します。
「ピボットテーブル／ピボットグラフウィザード-1/3」ダイアログボックスの「分析するデータのある場所を選択してください。」で、Excelのリスト／データベースを選択し、「作成するレポートの種類を指定したください。」の「ピボットテーブル」を選択して、「次へ」ボタンを押します。
「ピボットテーブル／ピボットグラフウィザード-2/3」ダイアログボックスの「範囲」に項目名を含んで正しくデータベースの範囲が選択されていることを確認し「次へ」ボタンを押します。（違っている場合は、セル範囲を選択し直します。）
「ピボットテーブル／ピボットグラフウィザード-3/3」ダイアログボックスの「ピボットテーブルの作成先を指定してください。」で「新規ワークシート」あるいは「既存のワークシート」（この場合は集計を表示する範囲の「セルの左上端」を指定します。）を選択し、「完了」ボタンを押します。
ピボットテーブルのフィールドリスト（メニューバーのように表示されている項目名のリスト）から、Ａの項目を「ここに行のフィールドをドラッグします」というエリアにドラッグします。
Ｂの項目を「ここに列のフィールドをドラッグします」というエリアにドラッグします。
集計したいＣの項目を「ここにデータアイテムをドラッグします」というエリアにドラッグします。
ピボットテーブルで集計したデータは、元のデータに変更が加えられた場合でも、ピボットテーブルが自動で更新されることはありません。ピボットテーブルを更新（再計算）するには、ピボットテーブルの任意のセルを1つ選択し、「ピボットテーブルツールバー」の「データの更新」ボタンを押してください。
セルに入力した番号７・９・１１・３７の行を別のワークシートに抽出したい
ご質問の場合は、「データ」「フィルタオプションの設定」を使います。
ただし、抽出されたデータは、このままでは別のセルには抽出できませんので、
「コピー」「貼り付け」をします。
「フィルタオプションの設定」についての説明は、長くなりますので「エクセルの操作説明」の「データベース」「フィルタオプションの設定」を参照してください。
添付ファイルを参照してください。
データは、A列12行目から「番号」、B列に「氏名」、C列に「お金」の項目名を含んで入力してあります。
「抽出条件」は、セルA1:A5です。抽出条件には、項目名を含んでいなければなりません。また、データのとの間に1行以上の空きセルがなければなりません。
抽出条件では、別の行に入力されている条件は、ORとして認識し、同じ行に入力されている条件は、ANDとして認識されます。
この場合は、７・９・１１・３７の行ですからOR条件です。
データ範囲のいずれかのセルを選択した状態で、「データ」「フィルタ」を選択し、「フィルタオプションの設定」で、「抽出先」に「選択範囲内」を選択し、「リスト範囲」にデータ範囲が正しく選択されていることを確認します。
次に、「検索条件範囲」に、$A$1:$A$5が選択されていることを確認し「ＯＫ」ボタンを押します。
次に、抽出データ範囲を選択して「編集」「コピー」して別のブックのシートに「編集」「貼り付け」します。
データをすべて表示するには、「データ」「フィルタ」で「すべて表示」を選択します。
『並べ替え』の優先キーが列番号の場合と項目名の場合がある
項目名になる場合は、選択範囲の項目名がすべての列に対して入力してある場合は、項目名が表示されます。
また、範囲の先頭行を「データ」と選択した場合は、列番号が表示され、「タイトル行」を選択した場合は、「項目名」が表示されます。
文字列中に空白があってもソートする方法
方法は、別のセルに空白を別の文字に置き換え（下の例では漢字のK）、そのデータ列において並べ替えをします。
ご質問の例ではアルファベットですので、空白の文字コードをアルファベットの文字コードより大きい漢字にしています。
具体的には、A列にデータが入力されているとします。
B列にA列のデータの空白を、"∵"という文字に置き換えして返す関数SUBSTITUTEを使います。
書式
SUBSTITUTE(文字列, 検索文字列, 置換文字列, 置換対象)
| |
A |
B |
| 1 |
a |
a |
| 2 |
a b |
a∵b |
| 3 |
aa |
aa |
| 4 |
ab |
ab |
| 5 |
b |
b |
| 6 |
bb |
bb |
| 7 |
c |
c |
セルB1に、
=SUBSTITUTE(A1," ","∵")
と入力し、セルA1の空白を"∵"に置き換えて返します。
個の式をコピーして下のセル範囲に貼り付けます。
「データ」「並べ替え」で、「最優先されるキー」に「B列」を選択し、「昇順」として「ＯＫ」ボタンを押します。
| |
A |
B |
| 1 |
a |
a |
| 2 |
aa |
aa |
| 3 |
ab |
ab |
| 4 |
a b |
a∵b |
| 5 |
b |
b |
| 6 |
bb |
bb |
| 7 |
c |
c |
表の左上端のセルA1を選択します。
「データ」「ピボットテーブルとピボットグラフレポート」(エクセル2007の場合は「挿入」「テーブル」「ピボットテーブル」「ピボットテーブル」)を選択します。
「ピボットテーブル／ピボットグラフ ウィザード1/3」が表示されます。
Excelのリスト/データベース
ピボットテーブル
にチェックが入っていることを確認し、そのまま「次へ」ボタンを押します。
「ピボットテーブル／ピボットグラフ ウィザード2/3」で、使用するデータ範囲が正しく表のデータ範囲を選択していることを確認（または、セル範囲を選択し直します）して、「次へ」ボタンを押します。
「ピボットテーブル／ピボットグラフ ウィザード3/3」で、「新規ワークシート」にチェツクが入っていることを確認し、「完了」ボタンを押します。
新しいシートが追加されて、「ピボットテーブル」メニューバーと、何もデータのない表が作成されます。
「ここに行のフィールドをドラッグします」というセル中に、「ピボットテーブル」メニューバーに表示されている「品名」ボタンをドラッグします。
次に、「ここに列のフィールドをドラッグします」と言うセルの中に、「出荷日」ボタンをドラッグします。
「ここにデータアイテムをドラッグします」というセルの中に、「金額」ボタンをドラッグします。
日付アイテムのグループ化をします。
「出荷日」のアイテムを選択し、「ピボットテーブル」ツールバーの「グループ化」ボタンをクリックして「グループ化」ダイアログボックスを表示します。
「単位」リストボックスで、「月」を選択することでグループ化を行うことができます。
この「単位」リストボックスでは、「秒」「分」「時」「日」「月」「四半期」「年」などを選択できます。
行の「品名」の右の▼、あるいは列の「出荷日」の右の▼ボタンを選択して、表示する項目をチェックすれば、該当の項目のみを表示することができます。
ピボットテーブルのデータは、元のセルのデータが更新されても自動更新されませんのでちゅぅいしてください。
「データ」「データの更新」を選択するか、「ピボットテーブル」メニューバーの「!」(データの更新ボタン)を押すとデータを最新の値に更新します。
ピボットテーブルを自分の決めた独自の条件で並べ替える
参考になる内容が「質問と回答」の「データベース」「2003/3/10」「自分の決めた独自の条件で並べ替える」にあります。
変則的な並び順の利用
昇順や、降順以外の並べ替えを行いたい場合は、「ツール」｜オプション」を選択し、「ユーザー設定リスト」タブをクリックすることで、ユーザー設定リストを作成できます。
そして、既存のフィールドラベルに上書きすることで、簡単にユーザー設定の並び順を作成できます。
Ａ取次店、メールオーダー、Ｂ取次店というにユーザー設定の並び順を作成するには、ピボットテーブルのテーブルに含まれる任意の見出し、"Ｂ取次店"を選択し、メールオーダーと入力します。既存の見出しを他の見出しによって上書きすると、Excelはその動作を二つの見出しを入れ替えるための操作であると判断します。そして、ピボットテーブルが更新されたり、構造が変更されても、変則的な並び順は維持されます。
この場合は、「自動並べ替え」機能をオフにしてください。
自動並べ替え機能をオフにするには、「ピボットテーブルツールバー」の「フィールドの設定」ボタンをクリックして「詳細」ボタンをクリックして、「ピボットテーブルフィールド詳細オプション」ダイアログボックスで「自動並べ替えオプション」の下の「手動」を選びます。
データをあいうえお順に並び替えたい
「データ」「並べ替え」を選択し、「最優先されるキー」に文字の入力されている列を選択し、「昇順」を選択して、「ＯＫ」ボタンを押します。
また、漢字のふりがなを表示するPHONETIC関数でふりがなを表示じしてみると正しいフ「フリガナ」になっていない場合がありますので確認してみてください。
PHONETIC関数 の説明は「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「文字列関数」PHONETICを参照してください。
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
確認として、「データ」「並べ替え」を選択し、「オプション」ボタンを押して、「方法」グループの「ふりがなを使う」にチェックが入っていることを確認してください。
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
また、ふりがなが表示されない場合は、「エクセルで使えるソフト」の「ふりがな表示」http://kiyopon.sakura.ne.jp/soft/furigana.htm
と言う「アドインソフト」を使うことで、ふりがなが表示できなかった漢字もふりがなが表示できるようになります。
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
「ふりがな表示」アドインソフトを使うためには、事前に「マクロを有効にする」必要があります。
複数のワークシート範囲のデータを統合する
ピボットテーブルを使います。
「ピボットテーブルレポート」コマンドを使って、複数の範囲のデータを統合することができます。
複数のワークシート範囲を元にしたピボットテーブルの作成
「ピボットテーブルレポート」コマンドを使って、複数のワークシート範囲のデータを統合することができます。
できあがったピボットテーブルの各ソース範囲は、ページ軸の１アイテムとして表示されます。ページ軸のドロップダウンリストボックスによって、それぞれ
のソース範囲や、それらの範囲のデータが統合されたテーブルを確認できます。
複数のワークシートを元に、統合ピボットテーブルを作成するには、次の手順に従ってください。
１ 「データ」「ピボットテーブルレポート」コマンドを選択し、「ピボットテーブルウィザード１／４」ダイアログボックスの「複数ワークシート範囲」オプションボタンを洗濯して、「次へ」ボタンをクリックします。
２ 「ピボットテーブルウィザード２ａ／４」ダイアログボックスが表示されたら、「自動」オプションボタンが選択されていることを確認し、「次へ」ボタンをクリックする。「ピボットテーブルウィザード２ｂ／４」ダイアログボックスが表示されます。
３ 最初のデータ範囲を選択（この場合得意先名のフィールドと１月の範囲を選択）し、「追加」ボタンをクリックする。
（統合対象となる範囲を指定する場合は、列や行の見出しも含めますが、集計行や集計列は含めないでください。合計や平均はピボットテーブル上で求められます。）
４ 手順３の操作を繰り返し、残りのデータ範囲をすべて指定したら、「次へ」ボタンをクリックする。
５ 統合ピボットテーブルにソース範囲の値の合計を表示する場合は、このまま「次へ」ボタンをクリックする。他の集計方法を使用する場合は、データエリアの「フィールド」ボタン「合計：値」をダブルクリックし、集計方法を選択（この場合は[合計]）して「次へ」ボタンをクリックする。
６ 「ピボットテーブルウィザード４／４」ダイアログボックスが表示されたら、ピボットテーブルの作成先を指定（見本の場合はセルB21）し、「完了」ボタンをクリックする。
ここでは、「ページエリア」のカレントアイテムとして、「すべて」が選択されていることに注意してください。このページには、データの統合された結果が表示されています。その他のアイテムを選択すると、統合の対象となったそれぞれのソース範囲がページごとに表示されます。
見本のファイルを添付しますので参照してください。
sanplep.xls 19.5kB
データベースから深夜に自動で、エクセルにデータを取り込む
２つの自動化作業が必要となります。
１つ目は、データベースからエクセルにデータを取り込む。
２つ目は、時間になると自動的にマクロを起動する。
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
１つ目の、データベースからエクセルにデータを取り込む方法として、ここでは、MicrosoftQueryで、データをワークシートに反映します。
MicrosoftQueryは、SQLのコマンドを組み合わせて作業を行います。SQLの操作に精通した方なら、SQLが生成するコマンドを表示して、編集を加えることも可能です。
しかし、SQLを意識しなくても、MicrosoftQueryを利用できます。
クエリウィザードは、データソースの指定、レコードの抽出条件および並べ替えの方法の指定など、一連の操作をサポートします。
注意:MicrosoftQueryは、オプションのコンポーネントなので、エクセルの標準セットアップでは、自動的にインストールされません。
Queryがインストールされていない場合は、Officeのセットアッププログラムを起動して、Queryの追加インストールを行ってください。
クエリウィザードは、データベースレコードの選択および並べ替えを４段階の手順で行います。
1.テーブルおよび、フィールドを選択する。
2.ファイル他条件を指定する。
3.レコードを並べ替える
4.クエリーに名前を付けて保存する。
クエリーの[データの抽出]ウィザードで、レコードのフィルタ条件を付けて抽出します。
抽出したクエリーウィザードの[完了]ダイアログボックスで[MicrosoftExcelにデータを返す]オプションボタンを選択して[完了]ボタンを押してください。
MicrosoftQueryについての説明は非常に長くなりすので、別の機会に解説をいたします。
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
マクロの自動記録機能を使って一連の操作を、マクロ化してみてください。
マクロの自動記録についての説明は「エクセルＶＢＡマクロの作成」
エクセルＶＢＡマクロの作成
http://kiyopon.sakura.ne.jp/vba/index.htm
を参照してください。
その後、自動起動によって汎用的に使うためには、マクロの修正が必要となると思います。
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
２つ目の、時間になると自動的にマクロを起動するには、次のように記述します。
Sub Auto_Open()
'ファイルを開くと自動的に次のマクロが起動します。
MACRO_TIME
End Sub
Sub MACRO_TIME()
'午前１時００分になるとMACRO_1と言うマクロを実行します。
Application.OnTime TimeValue("01:00:00"),
"MACRO_1"
End Sub
Sub MACRO_1()
AppActivate "Microsoft Excel"
Application.Visible = True
'ここに実際に「データベースからデータを取り込む」マクロを記述します。
End Sub
この自動実行マクロでは、エクセルは午前１時００分までは起動し、さらに該当のマクロを記述したファイルは開いていなければなりません。
複数シートからの重複しないデータ抽出
シート1
| |
A |
| 1 |
商品名 |
| 2 |
パソコン |
| 3 |
プリンタ |
| 4 |
コピー |
| 5 |
電話 |
| 6 |
ファックス |
シート2
| |
A |
B |
| 1 |
商品名 |
重複 |
| 2 |
パソコン |
1 |
| 3 |
カラープリンタ |
0 |
| 4 |
スキャナー |
0 |
| 5 |
ファックス |
1 |
「すべてのデータ」を表示するには、「データ」「フィルタ」「すべて表示」を選択します。
重複するデータを抽出して合計を算出する
|
A |
B |
C |
D |
E |
F |
| 1 |
明細票 |
課金票 |
|
|
|
|
| 2 |
CODE |
金額 |
|
CODE |
金額 |
|
| 3 |
0-1000 |
\48,762 |
|
|
|
|
| 4 |
0-1000 |
\2,469 |
|
0-1000 |
51,231 |
|
| 5 |
0-2538 |
\12,877 |
|
|
|
|
| 6 |
0-2538 |
\655 |
|
0-2538 |
14,036 |
|
| 7 |
0-2538 |
\504 |
|
|
|
|
| 8 |
0-1355 |
\52,340 |
|
|
|
|
| 9 |
0-1355 |
\201 |
|
0-1355 |
52,541 |
|
| 10 |
0-2376 |
\232,596 |
|
|
|
|
| 11 |
0-2376 |
\91,087 |
|
0-2376 |
323,683 |
|
| 12 |
0-2834 |
\15,069 |
|
|
|
|
| 13 |
0-2834 |
1,663 |
|
0-2834 |
16,732 |
|
| 14 |
0-2994 |
\483 |
|
|
|
|
| 15 |
合計 |
458,706 |
|
合計 |
458,223 |
|
重複データの２件目のみを表示するように
セルD3に
=IF(SUM(IF(A3=$A$3:A3,1))=2,A3,"")
と入力し、{Ctrl}+{Shift}+{Enter}で、「配列数式」として確定します。
確定語数式は、{ } でくくられます。
自分で{ }を入力してはいけません。
このセルD3をコピーし、下のセル（この場合D14まで）に貼り付けます。
重複データに対する、条件付き合計式として、セルE3に
=IF(D3="","",SUMIF($A$3:$A$14,A3,$B$3:$B$14))
と入力します。
これらの計算式は、データ入力する可能性のある行のすべてにコピーしておきます。
合計を表示するため、セルE15に
=SUM(E3:E14)
と入力します。
重複データのみの行を表示するには、D列のいずれかのセルを選択し、
「データ」「フィルタ」の「オートフィルタ」を選択します。
タイトル行に表示された▼ボタンを押して、（空白以外のセル）を選択します。
このデータ範囲をコピーし、（いずれかのシートの）任意のセルに貼り付けます。
すべてのデータを表示するには、「データ」「フィルタ」の「すべて表示」を選択します。
8月25日から、9月25日までの誕生日の人を、ぬきだして表示
具体的なデータベースの形式が分かりませんので、下のような例で示します。
日付を検出するには、誕生日の年を無視しなければいけませんので、誕生日から、月日のみを抜き出します。
| |
A |
B |
C |
| 1 |
月日 |
月日 |
|
| 2 |
>=825 |
<=925 |
|
| 3 |
|
|
|
| 4 |
|
|
|
| 5 |
|
|
|
| 6 |
|
|
|
| 7 |
|
|
|
| 8 |
氏名 |
誕生日 |
月日 |
| 9 |
小松義男 |
1965/9/6 |
906 |
| 10 |
小泉淳二 |
1960/12/7 |
1207 |
| 11 |
田中真子 |
1969/6/8 |
608 |
| 12 |
小田祐三 |
1970/8/29 |
829 |
セルA8:C12にデータが入力してあります。
誕生日はセルB9:B12に入力してあります。
セルC9に誕生日の月日のみを抽出する式を次のように入力します。
=VALUE(TEXT(B9,"mmdd"))
セルA1:B2に検索条件が入力してあります。
同じ行に入力してあるのは、AND条件で、825以上でかつ925以下を示します。
「データ」「フィルタオプションの設定」を選択し、「抽出先」グループで「選択範囲内」あるいは「指定した範囲」を選択します。
「リスト範囲」を$A$8:$C$12とし、「検索条件範囲」に$A$1:$B$2とし、抽出先に「指定した範囲」を選択した場合は、「抽出範囲」のセル左上端を指定し、「Ｏ
Ｋ」ボタンを押します。
シート「男」とシート「女」のシート両方に共通している名前を調べて書き出したい
シート「男」のセルA2からA30000までに名前が入力してあるとします。
同様にシート「女」のセルA2からA30000までに名前が入力してあるとします。
シート「男」のセルB1に次の式を入力します。
=IF(A2="","",ISNA(VLOOKUP(A2,女!$A$2:$A$30000,1,FALSE)))
シート「女」のセルA2:A3000にシート「男」のセルA2と同じデータがあるか検索し、無ければTRUEを返し、あればFALSEを返します。
この式をB列の下のセルに連続コピーします。
データの項目名の行であるセルB1を選択し、「データ」「オートフィルタ」を選択します。
表示された、▼ボタンを押してFALSEを選択します。
これで、「男」「女」共通の名前を抽出できます。
抽出されたデータを取り出したい場合は、そのデータ範囲の先頭行のセルを選択し、{Ctrl}キーを押しながら↓（下矢印）を選択すると対象の抽出データがコピーできます。
「編集」「コピー」すれば、他のシートに「貼り付け」できます。
・・・町1-100-1とか・・・町1-1,ハイツ1-101などを正しく並べ替える
「データ」「区切り位置」を使って番地を別々のセルに振り分けて、並べ替えます。
具体的には、下のように住所がA列に入力してある場合
| |
A |
| 1 |
碧南市旭町1-1 |
| 2 |
碧南市旭町1-103 |
| 3 |
碧南市旭町1-106 |
| 4 |
碧南市旭町1-107 |
| 5 |
碧南市旭町1-110 |
| 6 |
碧南市旭町1-18 |
| 7 |
碧南市旭町1-23-3 |
| 8 |
碧南市旭町1-3 |
| 9 |
碧南市旭町1-40 |
住所の入力してあるセル範囲A列を選択し、「編集」「コピー」し、次の列のセルB1を選択して「編集」「貼り付け」します。
貼り付けたB列を選択し、「データ」「区切り位置」を選択し、「区切り位置指定ウィザード」で、「元のデータの形式」の「カンマやタブなどの区切り文字によってフィールドごとに区切られたデータ」のチェツクを押して、「次へ」ボタンを押し、「区切り文字」の「その他」にチェツクを入れ、その後ろのテキスト入力位置に「-」を入力し「完了」ボタンを押します。
| |
A |
B |
C |
D |
| 1 |
碧南市旭町1-1 |
碧南市旭町1 |
1 |
|
| 2 |
碧南市旭町1-103 |
碧南市旭町1 |
103 |
|
| 3 |
碧南市旭町1-106 |
碧南市旭町1 |
106 |
|
| 4 |
碧南市旭町1-107 |
碧南市旭町1 |
107 |
|
| 5 |
碧南市旭町1-110 |
碧南市旭町1 |
110 |
|
| 6 |
碧南市旭町1-18 |
碧南市旭町1 |
18 |
|
| 7 |
碧南市旭町1-23-3 |
碧南市旭町1 |
23 |
3 |
| 8 |
碧南市旭町1-3 |
碧南市旭町1 |
3 |
|
| 9 |
碧南市旭町1-40 |
碧南市旭町1 |
40 |
|
すべてのセル範囲A列からD列のデータを選択し、「データ」「並べ替え」を選択し、「並べ替え」ダイアログボックスで「最優先されるキー」に「B列」、２番目に優先されるキーに「C列」、３番目に優先されるキーに「D列」を選択し、それぞれ「昇順」にチェツクを入れ、「ＯＫ」ボタンを押します。
| |
A |
B |
C |
D |
| 1 |
碧南市旭町1-1 |
碧南市旭町1 |
1 |
|
| 2 |
碧南市旭町1-3 |
碧南市旭町1 |
3 |
|
| 3 |
碧南市旭町1-18 |
碧南市旭町1 |
18 |
|
| 4 |
碧南市旭町1-23-3 |
碧南市旭町1 |
23 |
3 |
| 5 |
碧南市旭町1-40 |
碧南市旭町1 |
40 |
|
| 6 |
碧南市旭町1-103 |
碧南市旭町1 |
103 |
|
| 7 |
碧南市旭町1-106 |
碧南市旭町1 |
106 |
|
| 8 |
碧南市旭町1-107 |
碧南市旭町1 |
107 |
|
| 9 |
碧南市旭町1-110 |
碧南市旭町1 |
110 |
|
必要ない列B,C,D列を削除します。
リストのデータ入力に入力フォームを使う方法
エクセルの機能ですでに「データ」「フォーム」があります。
できれば、見出し行は、第１列目に記入し、データは第２列目から入力するとリストのデータが自動で入力しやすくなります。
データ入力の項目名部分のいずれかのセル（この場合はセルA7:F7のいずれか）を選択し、「データ」「フォーム」を選択します。
すると自動的にリストの入力フォームが表示されます。
フォームのタイトルには、フォームの操作対象となるリストが含まれるシート名が表示されます。
そして、そのタイトルバーのしたに、リストの列見出しが表示されます。
すでにリストへデータが入力されている場合、フォームの各ボックスには、リストの最終行のデータが表示されます。
フォームの右上は市には、現在、表示されている行番号とリストに含まれる全体行数が表示されます。ただし、見出し行はリストの行数には含まれません。
フォームの右端には、、リストを操作するためのコマンドボタンが並んでいます。
フォームの各列見出しの隣には、編集用のテキストボックスが用意されています。
（ただし計算によって値を求める列は除きます。）
リストに新しい行を追加するには「新規」ボタンをクリックします。
空白のフォームが表示されたら、そこの新しいデータを入力してください。
そのまま次の行の入力を続けるには、「新規」ボタンをもういちどクリックし、ワークシートに戻るには、「閉じる」ボタンをクリックしてください。
「フォーム」コマンドを使ってリストに新しい行を追加すると、他のセルに影響を与えることなく、リストはした方向へ拡張されます。
このとき新しいデータを入力しようとしている行に既存のデータが含まれている場合は、リストまたはデータベースを拡張することはできません。というメッセージが表示されます。
リストのデータを変更するには、まずフォームダイアログボックスのスクロールバーを使って目的のデータが含まれる行を表示し、それぞれの編集用テキストボックスで修正を行います。
リストから行を削除するには、「削除」ボタンをクリックします。
「フォーム」ダイアログボックスによる行の削除は取り消せないため、削除の実行前に確認を求めるメッセージが表示されます。
伝票ナンバーごとに金額を集計する
一番簡単なのは、「データ」「集計」で、自動集計をします。
具体的なデータのリストが分かりませんが、
手順は、
１．「伝票ナンバー」列のセルを１つ洗濯し、「データ」「並べ替え」を選択し、「伝票ナンバー」を基準に、リストの並べ替えを行います。
２．「データ」「集計」を選択する。
３．「集計の設定」ダイアログボックスに、「グループの基準」を「伝票ナンバー」に、「集計の方法」を「合計」に、「集計するフィールド」に「金額」を指定します。
４．「現在の集計表と置き換える」「集計行をデータの下に挿入する」にチェックを入れ「ＯＫ」ボタンを押します。
自動集計の結果が表示され、行番号の左にアウトラインが表示されます。
・１番目のレベル記号をクリックすることで、リストの総計行のみを表示できます。
・２番目のレベル記号をクリックすることで、各グループの小計行とリストの総計行のみを表示できます。
・３番目のレベル記号をクリックすることで、リストのすべての行「詳細行と集計行」を表示できます。
アウトラインを利用することで、小計の値を元にリストを並べ替えることも可能です。例えば、合計金額の一番多い項目が最上部にくるように、リストを並べ替えるには、次の手順に従ってください。
１．２番目のレベル記号をクリックし、リストに小計行と総計のみを表示する。
２．"折り畳まれた"状態のリストを"金額"列の値の降順に並べ替える。
並べ替えの結果、すべての詳細行は、その集計行と共に移動されます。
また、集計行のみをコピーしたい場合は、「質問と回答」の「データベース」「2003/2/4」「アウトライン表示の可視セルのみの選択」を参照してください。
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
自動集計とアウトラインを削除するには「集計の設定」ダイアログボックスを表示し、「すべて削除」ボタンをクリックします。
ピボットテーブルのデータ更新
ピボットテーブルのデータ更新による再計算は次のように行います。
「ピボットテーブルの更新」
ピボットテーブルはそのデータソースとリンクされていますが、ソースデータに変更が加えられた場合でも、ピボットテーブルが自動的に更新されることはありません。
ピボットテーブルを更新するには、ピボットテーブルの任意のセルを１つ選択し、「ピボットテーブル」ツールバーの「データ更新」ボタンをクリックするか、「データ」「データ更新」コマンドを選択してください。
「ファイル読み込み時のテーブルの更新」
ピボットテーブルが含まれているファイルを開く時点で、そのピボットテーブルの更新を行うには「ピボットテーブル」ツールバーの「ピボットテーブル」「オプション」コマンドを選択し、「開く時に更新する」チェックボックスをオンにしてください。
「リンクされているピボットテーブルの更新」
あるピボットテーブルが他のピボットテーブルのデータソースとなっている場合、いずれかのピボットテーブルを更新すると、両方のピボットテーブルが更新されます。
「外部データを元にしたピボットテーブルの更新」
ピボットテーブルが外部データのクエリーを元にしている場合、ピボットテーブルの更新中でも作業を続けられるように、バックグラウンドでクエリーを実行するには、テーブルの任意のセルを選択し、「ピボットテーブル」ツーレバーの「ピボットテーブル」「オプション」コマンドを選択して、「バックグラウンドでクエリーを実行する」チェツクをオンにしてください。
男女混合の名簿から男、女のそれぞれの平均年齢を計算
SUMIF関数とCOUNTIF関数を使います。
条件に合致したデータの合計÷条件に合致したデータの数となります。
具体的には
| |
A |
B |
C |
D |
E |
| 1 |
氏名 |
性別 |
年齢 |
|
平均年齢 |
| 2 |
小泉純一 |
男 |
46 |
男 |
39 |
| 3 |
田中和夫 |
男 |
36 |
女 |
26.33333 |
| 4 |
田中真智子 |
女 |
21 |
|
|
| 5 |
鈴木あみ |
女 |
32 |
|
|
| 6 |
寺田良則 |
男 |
53 |
|
|
| 7 |
大日花子 |
女 |
26 |
|
|
| 8 |
西村清 |
男 |
21 |
|
|
| 9 |
|
|
|
|
|
名簿は100行まで入力してあると仮定しました。
セルA2から氏名が入力してあります。
セルB2から性別が入力してあります。
セルC2から年齢が入力してあります。
セルD2 男
セルD3 女
セルE2
=SUMIF($B$2:$B$100,D2,$C$2:$C$100)/COUNTIF($B$2:$B$100,D2)
セルE3
=SUMIF($B$2:$B$100,D3,$C$2:$C$100)/COUNTIF($B$2:$B$100,D3)
SUMIFについての説明は「関数の使い方説明」の「数学+三角」のSUMIF
COUNTIFについての説明は「関数の使い方説明」の「統計関数」のCOUNTIF
を参照してください。
月と取引先と金額のデータを別のシートに月毎、取引先別に合計金額を集計
Sheet1
データ入力シート
| |
A |
B |
C |
| 1 |
月 |
取引先 |
金額 |
| 2 |
4月 |
A |
1000 |
| 3 |
4月 |
B |
2000 |
| 4 |
4月 |
C |
2500 |
| 5 |
4月 |
A |
4200 |
| 6 |
4月 |
A |
6000 |
Sheet2
Sheet1のデータから取引先と月の２つの条件に合致するデータを返します。
Sheet1の取引先が追加・削除されても自動表示します。
| |
A |
B |
C |
D |
E |
F |
G |
H |
| 1 |
|
|
|
取引先/月 |
4月 |
5月 |
6月 |
7月 |
| 2 |
1 |
A |
1 |
A |
11,200 |
21,900 |
11,800 |
0 |
| 3 |
2 |
B |
1 |
B |
2,000 |
21,900 |
18,300 |
0 |
| 4 |
3 |
C |
1 |
C |
7,500 |
16,510 |
12,300 |
0 |
| 5 |
|
|
|
D |
3,000 |
0 |
0 |
0 |
| 6 |
|
|
|
K |
0 |
4,500 |
0 |
0 |
| 7 |
|
|
|
L |
0 |
6,000 |
0 |
0 |
| 8 |
5 |
K |
1 |
G |
0 |
0 |
4,800 |
0 |
| 9 |
|
|
|
|
|
|
|
|
| 10 |
6 |
L |
1 |
|
|
|
|
|
| 11 |
|
|
|
|
|
|
|
|
この例では、取引先は50件、として計算式を示しています。
列A:Cは取引先表示用の計算式ですから、実際のシートでは「表示しない」設定にして、印刷されないようにします。
セルA2 取引先表示用の式
=IF(C2<>"",SUM($C$2:C2),"")
とり引き先の通し番号を返します
下の行にコピーします。
この列は「非表示」にしてください。
セルB2 取引先をSheet1のデータから自動転記します。
=IF(OR(Sheet1!B2="",$B$1:B1=Sheet1!B2),"",Sheet1!B2)
と入力し、{Shift}+{Ctrl}+{Enter}で「配列数式」として確定します。
確定後数式は{ }でくくられます。
自分で{ }を入力してはいけません。
下の行にコピーします。
この列は「非表示」にしてください。
セルC2 取引先表示用の式
=IF(B2<>"",1,"")
下の行にコピーします。
この列は「非表示」にしてください。
セルD2 シート1の取引先データが追加・削除されても正しく、取引先を自動表示します。
=IF(ISERROR(VLOOKUP(ROW()-1,A2:B50,2,FALSE)),"",VLOOKUP(ROW()-1,A2:B50,2,FALSE))
下の行にコピーします。
セルE2 取引先と月の二つの条件に合致するデータの合計を返します
=IF($D2="","",SUM(IF($D2=Sheet1!$B$1:$B$1000,IF(E$1=Sheet1!$A$1:$A$1000,Sheet1!$C$1:$C$1000))))
と入力し、{Ctrl}+{Shift}+{Enter}で「配列数式」として確定します。
配列数式は確定後{ }でくくられます。
{ }を自分で入力してはいけません。
下の行、右の列にコピーします。
この計算式の例では、データ数が1000行までにしています。
セルE1
集計する月を入力します。
右の列にコピーします。
また、罫線も取引先が表示されているセルに自動で表示するように設定します。
セル範囲D1:P50を選択し、「書式」「条件付き書式」を選択し、「条件付き書式の設定」ダイアログボックスの「条件1(1)」で「数式が」=$D1<>""と入力し、「書式」ボタンを押し、「罫線」タグの「外枠」の罫線を指定し、「ＯＫ」「ＯＫ」ボタンを押します。
見本のシートを添付します。
日付入力後に、データを自動的に日付順に並べ替える
マクロを使う必要があります。
見本を以下に示します。
| |
A |
B |
C |
D |
| 1 |
月日 |
預入 |
引出 |
残高 |
| 2 |
1/1 |
|
|
1,214,061 |
| 3 |
2/4 |
10,000 |
|
|
| 4 |
1/30 |
|
10,000 |
|
| 5 |
|
|
|
|
| 6 |
|
|
|
|
| 7 |
合計 |
10,000 |
10,000 |
1,224,061 |
データ範囲の項目名を含めて名前を定義します。
上の例では、セル範囲A1:D6を選択し、「挿入」「名前」「定義」で、「名前」欄にdataと入力し、「ＯＫ」ボタンを押します。
日付はA列に入力します。
日付の列のデータが修正されると自動的に日付を並べ替えます。
日付入力の列やデータの開始行が違う場合はマクロコードを変更します。
説明は、コードに記入してあります。
下のマクロコードをコピーします。
Private Sub Worksheet_Change(ByVal Target As Range)
'データ範囲を項目名を含め"data"として名前定義しておきます。
c = Target.Column
'c 日付の入力列を指定します。1=A,2=B,3=C
If c = 1 Then
'並び替えの列の先頭データを指定し並び替えます。
'必要により下のコードのRange("A2")の"A2"部分を変更します。
Range("data").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes,
_
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, SortMethod _
:=xlPinYin
End If
End Sub
データを入力するシートのシート名タグをマウスの右ボタンでクリックし、「コードの表示」を選択します。
「Microsoft VisualBasic」が開きますので、右の「コード」の空白部分に、コピーしたマクロコードを、貼り付けます。
「×」または「ファイル」「終了してMicrosoftExcelに戻る」を選択します。
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
このファイルを開く場合は、「マクロ」を有効にする必要があります。
マクロを有効にする
【Eｘｃｅｌ９７の場合】
Eｘｃｅｌ９７を起動します。
「ツール」「オプション」の「全般」から「マクロウィルスから保護する」の
チェツクをはずして「ＯＫ」ボタンを押す。
【Excel2000の場合】【Excel 2002の場合】
Excel2000(2002)を起動します。
「ツール」「マクロ」「セキュリティ」からセキュリティレベル「低」
をチェツクして「ＯＫ」ボタンを押す。
「自動集計」で「合計」と「平均」を計算させる
「現在の集計行と置き換える」のチェックを外して再度集計することがポイントです。
「項目」ごとの集計をする場合、「集計の設定」ダイアログボックスから「現在の集計表と置き換える」チェックボックスと「集計行をデータの下に挿入する」のチェツクがオンになっていることを確認して「ＯＫ」ボタンを押します。
さらに同じ「項目」の平均を求めるための集計行を追加するためには、「集計の設定」ダイアログボックスの「集計の方法」ドロップダウンリストボックスから「平均」を選択し、「現在の集計表と置き換える」チェックボックスを「オフ」にします。この結果、リストにすでに挿入されている集計行を解除することなく、新しい集計行を追加できます。
空白セルを自動的に詰める方法
データの左に１列追加してＡ列に１から順番に番号を付けます。
セルA1に1を入力します。
セルA1の右下境界をマウスでドラッグして＋をひょぅじさせます。
その状態で{Ctrl}キーを押しながら、下の行に連続コピー（オートフィル）で数値を2,3,4,と自動入力します。
再度B列選択して、列を挿入します。
セルB1に次の式を入力します。
=IF(C1="","",ROW())
これは、セルC1にデータが入力されていない場合は何も表示せず、データが有れば行番号を表示します。
データのない部分を除いて、自動的に番号が振られます。
「データ」「並べ替え」でB列を対象に「データ」「並べ替え」「昇順」します。
元の状態に並べ替えるには、A列を対象に「データ」「並べ替え」「昇順」します。
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
また、次の方法もあります。
「エクセルで使えるソフト」のなかに「セル値０の行非表示」
選択範囲のセルの値（複数列の場合は同じ行の合計値）が０の場合該当行を非表示にします。
と言うアドインソフトがあります。
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
これを使えば、何も入力されていない行を非表示にできます。
このソフトを使うには、解凍する前にマクロを有効にしておく必要があります。
自分の決めた独自の条件で並べ替える
並べ替えは、通常五十音順（あいうえお順）やアルファベット順で昇順／降順に並べ替えられますが、自分の決めた条件で並べ替えもできます。
「ツール」「オプション」の「ユーザー設定リスト」で、自分が決めて順序を登録しておきます。
例えば、
所長
部長
課長
係長
の順に並べ替える場合は、
手順1
「ツール」「オプション」で「ユーザー設定リスト」タブを開き、「ユーザー設定リスト」の「新しいリスト」を選択し、
「リストの項目」に
所長,部長,課長,係長
と入力し、「追加」ボタンを押します。
手順2
表の項目を並べ替える場合は、その表の任意のセルをクリックし、「データ」「並べ替え」を選択します。
手順3
キーとなる列を選んで、「オプション」ボタンをクリックし、「並べ替え順序の指定」の右の▼で、ユーザー設定で作成したリストを選択して「ＯＫ」ボタンを押します。
複数の表を１つの表にまとめて合計を出す
「データ」「統合」という機能を使います。
この場合、それぞれの表は、同じ形式でデータが入力されていることが必要です。
それぞれの数値データをまとめて、別の表を作ることができます。
同一のシート上の表でも、別々のシート上の表でも「統合」ができます。
例えばSheet2とSheet3の表をSheet1にまとめる場合。
Sheet2
| |
A |
B |
C |
D |
| 1 |
品目 |
単価 |
数量 |
合計額 |
| 2 |
Ａ |
1,000 |
1,077 |
1,077,000 |
| 3 |
Ｂ |
1,200 |
1,220 |
1,464,000 |
| 4 |
Ｃ |
1,400 |
1,078 |
1,509,200 |
| 5 |
Ｄ |
1,600 |
757 |
1,211,200 |
| 6 |
Ｅ |
1,800 |
1,609 |
2,896,200 |
| 7 |
Ｆ |
2,000 |
686 |
1,372,000 |
Sheet3
| |
A |
B |
C |
D |
| 1 |
品目 |
単価 |
数量 |
合計額 |
| 2 |
Ａ |
1,000 |
525 |
525,000 |
| 3 |
Ｂ |
1,200 |
622 |
746,400 |
| 4 |
Ｃ |
1,400 |
533 |
746,200 |
| 5 |
Ｄ |
1,600 |
407 |
651,200 |
| 6 |
Ｅ |
1,800 |
889 |
1,600,200 |
| 7 |
Ｆ |
2,000 |
237 |
474,000 |
Sheet1
| |
A |
B |
C |
D |
| 1 |
品目 |
単価 |
数量 |
合計額 |
| 2 |
Ａ |
1,000 |
|
|
| 3 |
Ｂ |
1,200 |
|
|
| 4 |
Ｃ |
1,400 |
|
|
| 5 |
Ｄ |
1,600 |
|
|
| 6 |
Ｅ |
1,800 |
|
|
| 7 |
Ｆ |
2,000 |
|
|
手順
Sheet1のデータを入れたいセルの左上端セル、C2を選択し、「データ」「統合」を選択します。
「統合の設定」ダイアログボックスで、集計の方法を「合計」とし、「統合元範囲」をクリックし、シートSheet2を選択し、集計したいデータのセル範囲C2:D7を選択します。
「追加」ボタンを選択し、同様に「統合元範囲」をクリックし、シートSheet3を選択し、集計したいデータのセル範囲C2:C7を選択します。
最後に「ＯＫ」ボタンを押します。
シートSheet1には、それぞれのデータ範囲の合計値が入力されます。
Sheet1
| |
A |
B |
C |
D |
| 1 |
品目 |
単価 |
数量 |
合計額 |
| 2 |
Ａ |
1,000 |
1,602 |
1,602,000 |
| 3 |
Ｂ |
1,200 |
1,842 |
2,210,400 |
| 4 |
Ｃ |
1,400 |
1,611 |
2,255,400 |
| 5 |
Ｄ |
1,600 |
1,164 |
1,862,400 |
| 6 |
Ｅ |
1,800 |
2,498 |
4,496,400 |
| 7 |
Ｆ |
2,000 |
923 |
1,846,000 |
1-1,1-2,1-3・・・・・・・・1-100の並べ替えを正しくしたい
数値ではなく、文字列として認識しているのではないでしょうか。
並び替える前に"-"を無くし、-以前の数値に-以降の数値を１００で割って加算します。
| |
A |
B |
C |
| 1 |
1-1 |
=LEFT(A1,FIND("-",A1,1)-1)+(MID(A1,FIND("-",A1,1)+1,LEN(A1))/100) |
1.01 |
| 2 |
1-2 |
1.02 |
1.02 |
| 3 |
1-10 |
1.1 |
1.1 |
| 4 |
1-11 |
1.11 |
1.11 |
．．．
B2セルに
=LEFT(A1,FIND("-",A1,1)-1)+(MID(A1,FIND("-",A1,1)+1,LEN(A1))/100)
と入力します。
B1セルの計算式を「編集」「コピー」し、C1セルに「編集」「形式を選択して貼り付け」で「値」として貼り付けます。
「並べ替え」は、C列をで行います。
アウトライン表示の可視セルのみの選択
可視セルの選択には、カスタムツールバーを作成します。
１「表示」「ツールバー」「ユーザー設定」を選択します。
２「ユーザー設定」ダイアログボックスの「ツールバー」タブの「新規作成」ボタンをクリックし、「アウトライン」と入力し、「ＯＫ」ボタンを押します。
３「コマンド」タブをクリックし、「分類」リストボックスから、「データ」を選択します。
４「コマンド」リストボックスから新しいツールバーへ「アウトライン記号の表示」「グーループ解除」「グループ化」「詳細データの表示」「詳細を表示しな
い」コマンドをドラッグします。
５「分類」リストボックスから「編集」を選択します。
６「コマンド」リストボックスから新しいツールバーへ「可視セルの選択」コマンドをドラッグします。
７「ユーザー設定」ダイアログボックスの「閉じる」ボタンをクリックする。
設定した、ユーザー設定「アウトライン」「可視セルの選択」を選択します。
ピボットテーブルの罫線が表示されない
ピボットテーブルの罫線表示については、いろいろな場合に正しく表示されないことがあります。
その症状と、処置について以下に示します。
１ ピボットテーブル レポートを更新したりレイアウトを変更すると書式が無効になる
書式の維持の設定を確認する レポートのセルをクリックし、[表のオプション]
([ピボットテーブル] ツールバーの [ピボットテーブル] メニュー) をクリックします。[書式オプション]
の [書式の維持] チェック ボックスをオンにします。書式設定をもう一度適用すると、レイアウトを更新または変更しても書式が維持されます。
２ セルの罫線が解除される
レイアウトを変更するかピボットテーブル レポートを更新すると、セルの罫線に加えた変更は解除されます。
３ 条件付き書式設定が動作しない
ピボットテーブル レポートのセルに条件付き書式を適用すると、予測できない結果になります。
株式会社、有限会社等をを無視して５０音に並び替えるには
別のセルにデータを貼付け、そのデータに対して
SUBSTITUTE関数で、株式会社、有限会社、合資会社の文字を削除します。
データリストに１列追加し、株式会社、有限会社、合資会社などの文字を削除した会社名を表示させます。
具体的には
| |
A |
B |
C |
| 1 |
会社名 |
|
|
| 2 |
株式会社ＸＸＸＸ |
ＸＸＸＸ |
ＸＸＸＸ |
| 3 |
有限会社ＹＹＹＹ |
ＹＹＹＹ |
ＹＹＹＹ |
| 4 |
ZZZZ株式会社 |
ZZZZ |
ZZZZ |
| 5 |
|
|
|
B列を挿入し
セルB2に=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"株式会社",,1),"有限会社",,1),"合資会社",,1)
と入力します。
ここでは、株式会社、有限会社、合資会社の文字を削除（文字のない状態に置き換え）しています。
この式を、B列の下のすべての該当セルにコピー貼り付けします。
B列のデータ範囲を選択し、「編集」「こぴー」し、C1セルを選択し、「編集」「形式を選択して貼り付け」で「値」を選択して、「ＯＫ」ボタンを押します。
C列に対して「並び替え」を実施します。
もし、あいうえお順に正しく並べ替えできない場合は、並べ替えのデータに正しく「ふりがなが」されていないためです。
もしふりがなが正しくない場合は、セルを選択し、「書式」「ふりがな」を「編集」すればふりがなが修正できます。
また、
「エクセルで使えるソフト」
http://kiyopon.sakura.ne.jp/soft/index.htm
に「ふりがな表示」アドインソフトがあります。
http://kiyopon.sakura.ne.jp/soft/furigana.htm
これを使えば、ふりがながない漢字文字列もふりがなが表示されます。
注意このソフトを使用する場合は、「アドインソフト使用上の注意」
http://kiyopon.sakura.ne.jp/soft/addin.html
を必ずお読みください。
集計で表示された小計のみをコピー貼り付けしたい
「自動集計」を使ってリストの分析をします。
リストの左側に「レベル記号」が表示されています。
１番目のレベル記号をクリックすることで、リストの総計行のみ表示できます。
（これをアウトラインと言います）
２番目のレベル記号をチェツクすることで、各グループの小計行とリストの総計行のみを表示できます。
３番目のレベル記号をチェツクすることで、リストの全ての行（詳細行と集計行）を表示できます。
２番目のレベル記号をクリックし、リストに小計行と総計行のみを表示します。
小計行のみを表示している場合でも、詳細行は折り畳まれているだけで、行はそのままですので、全体をコピーすると全ての詳細行がコピーされてしまいます。
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
「小計のみ」コピーする手順
アウトライン表示の可視セルのみの選択
可視セルの選択には、カスタムツールバーを作成します。
１「表示」「ツールバー」「ユーザー設定」を選択します。
２「ユーザー設定」ダイアログボックスの「ツールバー」タブの「新規作成」ボタンをクリックし、「アウトライン」と入力し、「ＯＫ」ボタンを押します。
３「コマンド」タブをクリックし、「分類」リストボックスから、「データ」を選択します。
４「コマンド」リストボックスから新しいツールバーへ「アウトライン記号の表示」「グーループ解除」「グループ化」「詳細データの表示」「詳細を表示しない」コマンドをドラッグします。
５「分類」リストボックスから「編集」を選択します。
６「コマンド」リストボックスから新しいツールバーへ「可視セルの選択」コマンドをドラッグします。
７「ユーザー設定」ダイアログボックスの「閉じる」ボタンをクリックする。
レベルで表示されている複数行を選択し、設定したユーザー設定「アウトライン」「可視セルの選択」を選択します。
貼り付け先のセルを選択し、「編集」「貼り付け」します。
重複データの数を多い順に表示
自動的にデータ数の多い順に表示させる数式です。
MODE関数とIF関数の組み合わせで出現回数を調べています。
また、その数式を配列数式で入力しています。
関数で同じ内容のデータのセル数を集計する方法
A1:A10にデータ内容が入力されています。
C列にカウントするデータ内容を出現回数が多い順にデータを表示させる数式を入力します。。
D1列に条件にあったデー多数をカウントする数式を入力します
| |
A |
B |
C |
D |
| 1 |
1 |
|
1 |
4 |
| 2 |
1 |
|
2 |
2 |
| 3 |
1 |
|
3 |
2 |
| 4 |
1 |
|
#N/A |
0 |
| 5 |
2 |
|
#N/A |
0 |
| 6 |
2 |
|
|
|
| 7 |
3 |
|
|
|
| 8 |
3 |
|
|
|
| 9 |
4 |
|
|
|
| 10 |
5 |
|
|
|
D1=COUNTIF($A$1:$A$10,C1)
D2=COUNTIF($A$1:$A$10,C2)
D3=COUNTIF($A$1:$A$10,C3)
D4=COUNTIF($A$1:$A$10,C4)
D5=COUNTIF($A$1:$A$10,C5)
C1=MODE(A1:A10)
出現回数が一番多いデータを返します。
C2=MODE(IF(A1:A10=C1,"",A1:A10))と入力し{Ctrl}+{Shift}+{Enter}で確定する。
出現回数が一番多いデータを除いた残りのデータ範囲の中で一番多いデータを返します。
C3=MODE(IF(A1:A10=C1,"",IF(A1:A10=C2,"",A1:A10)))と入力し{Ctrl}+{Shift}+{Enter}
で確定する。
以下同様に出現回数の多い順にデータを返します。
C4=MODE(IF(A1:A10=C1,"",IF(A1:A10=C2,"",IF(A1:A10=C3,"",A1:A10))))
と入力し{Ctrl}+{Shift}+{Enter}で確定する。
C5=MODE(IF(A1:A10=C1,"",IF(A1:A10=C2,"",IF(A1:A10=C3,"",IF(A1:A10=C4,"",A1:A10)))))
と入力し{Ctrl}+{Shift}+{Enter}で確定する。
{Ctrl}+{Shift}+{Enter}で確定すると「配列数式」となり、数式が{
}でくくられます。
詳しくは、添付ファイルを参照してください。
添付ファイルでは#N/Aを表示しないように数式を加工しています。
oijyun.xls 18.5kB
下１桁が0のセルを抽出したい
手順は次のようになります。
セルのデータをｒRIGHT関数で、下１文字のみ取り出します。
次にそれが０かどうか判定し、表示します。
具体的には、セルA2にデータが入力されているとします。
判定のための列を作り、(例えば列B)そこに次の判定式を入力します。
=IF(RIGHT(A2,1)="0",0,1)
結果が0の行だけを、「フィルター」で検索します。
A
B
1 数値 判定
2 123450 0
3 123456 1
4 123462 1
5 123468 1
6 123474 1
7 123480 0
8 123486 1
9 123492 1
10 123498 1
リストの中のいずれかの説を選択しておき、「データ」「フィルタ」
「オートフィルタ」を選択します。
リストの最上行に矢印が表示されますので、矢印を押して「0」 を選択します。
0の行だけが表示されます。
元に戻してすべての行を表示する場合は、同様にリストの矢印を押して、「すべて」を選択します。
文字と数値の混在データのセルの並び替えを数字のみで行う
下のようにKAT-***のようにセルの並び替えを5文字目以降の数値で行いたい場合
MID関数を使って文字列を５文字目から以降を取りだします。
下の例ではA列の文字列の５文字目以降をB列に取り出します。
取りだした文字はVALUE関数で数値として表示します。
A
B
1 KAT-1
=VALUE(MID(A1,5,LEN(A1)-4))
2 KAT-20
上の式をこれ以降に連続コピー
3 KAT-1000
このＢ列を基準に並び替えをすればいいですね。
オートフィルタの処理結果を自動で他のセルにコピーする
「フィルタオプションの設定」コマンドを使えば、同じシート上の別のセルへ検索結果を表示することはできます。
フィルタ処理結果の他の位置へのコピーの説明
フィルタ条件に一致する行をワークシートの他の範囲にコピー（抽出）したり、他のワークシートにコピーしたりすることが可能です。
条件に一致して、リストに表示されている行のコピーには、通常のコピーと同様、「編集」「コピー」と「貼り付け」コマンドを使用します。
「コピー」と「貼り付け」コマンドを使用せず、フィルタ処理と行の抽出を同時に行う場合は、「フィルタオプションの設定」コマンドを使用してください。
ただし、「フィルタオブションの設定」コマンドを使っても、条件に一致する行を、自動的に他の新規のワークシートや他の新規ブックにコピーすることはできません。
こうした作業を行うためには、やはり、「コピー」「貼り付け」コマンドを使用しなければなりません。
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
「フィルタオプションの設定」の説明については、内容が多くこの紙面では説明がわかりにくいため、「エクセル操作説明」
http://kiyopon.sakura.ne.jp/sousa/index.htm
の「データベース」「フィルタオプションの設定」コマンドを参照してください。
指定用紙の右端にエクセルで作成した氏名を印刷する
ワードの「差し込み印刷ヘルパー」を使います。
準備として、
エクセルで氏名一覧を項目名「氏名」を先頭にして１列で入力しておきます。
つぎに、
ワードの「ツール」から「差し込み印刷ヘルパー」で作成してみてください。
手順としては、ワードの「ツール」「差し込み印刷ヘルパー」を選択します。
「差し込み印刷ヘルパー」ウィザードから「作成」「定型書簡」を選択します。
次に「データファイル」の「データファイル指定」から「データファイルを開く」を選択します。
「ファイルの種類」を「Microsoft Excelワークシート」に変更します。
次に、氏名の入力されたファイルを選択し、「開く」「範囲名または範囲」で、「ワークシート全体」を選択し「OK」ボタンを押します。
メイン文書に差し込みフィールドが見つかりません。メイン文書に差し込みフィールドを追加するには「メイン文書の編集」をクリックしてください。と表示さ
れますので、「メイン文書の編集」を押します。
ワードのメニューバーの下に「差し込みフィールドの挿入」というメニューが表示されていますので、氏名の項目を選択します。
用紙の左上に「氏名」というフィールドが入力されます。
この表示位置は、右寄せや改行などで自由に移動できます。
「ツール」「差し込み印刷ヘルパー」を選択し、「差し込み」ボタンを押します。
「差し込み」ダイアログボックスが表示されますので、「差し込み先」「差し込むレコード」「空フィールドの空白行の処理」を選んで、「差し込み」ボタンを押します。
オートフィルタの「ｎレコード中ｎ個見つかりました」コメントが出ない
ステータスバーについて
ステータスバーの表示は通常、コマンドと表示されていますが、これはワークシートに新しいデータを入力できる状態にあることを意味しています。
新しいデータの入力中は、入力と表示されます。数式バーをアクティブにするか、データの入力されているセルをダブルクリックすると、ステータスバーには編集と表示されます。
ステータスバーの表示をコマンドに戻すには、「入力」ボタンのクリックまたは、[Enter]ボタンを押して数式バーの入力内容を確定するか、「キャンセル」ボタンのクリックまたは[Esc]を押して変更結果を無効にしてください。
Excelのデフォルト設定では、ステータスバーの表示が選択されています。
ステータスバーを非表示にするには、「表示」「ステータスバー」コマンドを選択してください。
ステータスバーを再表示するには、「ステータスバー」コマンドをもう一度選択します。
これでもだめならば、「表示」「ツールバー」「ユーザー設定」コマンドを選択し、「ツールバー」タブの「リセット」ボタンを押してみます。
ツールバーの設定が初期状態に戻ります。
オートフィルタ時のみ、［ツール］−［オプション］の「計算方法」を「手動」にする。
リストの中に、一つでも計算式があると表示されなくなります。
ステータスバーに表示されるレコード件数は他の操作をしてしまうと消えるため、数式が入っていると件数によっては再計算のほうがオートフィルタの抽出より遅くなって、抽出後に他の操作をしたことになり、抽出件数が出ないことがあります。
セルA1〜A100で、B1の数値より小さいものの数を求めたい
配列数式を使います。
セルA1:A100に入力たれたデータに対して、セルB1の数値よりも小さいという条件に合致するデータ数をカウントする場合。
=SUM(IF(A1:A100<B1,1,0))
をセルに入力します。
配列関数は、数式入力後に確定する場合Ctrl+Shift+Enterで確定します。
確定後は、数式が { } で囲まれます。
−−−−−−−−−−−−−−−−−−−−−−−−−−−
{=SUM(IF(A1:A100<B1,1,0))}
配列数式にいて詳しいことは、
エクセルの勉強部屋」の「質問と回答」の2001/9/21を参照してください。
http://kiyopon.sakura.ne.jp/situmon/kaitou/index06.htm#複数の検索条件に一致するセルの値を合計する方法、個数を数える方法
４つ以上の列をキーにして並べ替える方法
4つ以上の列をキーにして並べ替える場合は、重要度の低い列から順に並べ替えます。
たとえば、社員情報が入力されているリストの[部署]、[役職]、[姓]、および[名]という列を並べ替えるには、リストを2回並べ替えます。
１．重要度の高い列から順に[部署]、[役職]、[姓]、[名]であるとします。
２．まず、[最優先されるキー]ボックスの[名]をクリックし、リストを並べ替えます。
３．次に、[最優先されるキー]ボックスの[部署]をクリックし、[2番目に優先されるキー]ボックスの[役職]をクリックし、[3番目に優先されるキー]ボックスの[姓]をクリックしてリストを並べ替えます。
４．他の並べ替えのオプションをクリックし、[OK]をクリックします。
必要に応じて、次に重要度の高い列を使って、手順2?4を繰り返します。
オートフィルタの検索結果のデータ数を表示する
subtotal関数で表示させることができます。
具体的には
セルA11にはデータの総個数 セルB11には抽出結果のデータ個数が帰ります。
| |
A |
B |
| 1 |
日付 |
売上 |
| 2 |
7/1 |
600 |
| 3 |
7/2 |
320 |
| 4 |
7/3 |
630 |
| 5 |
7/3 |
200 |
| 6 |
7/4 |
400 |
| 7 |
7/4 |
700 |
| 8 |
7/5 |
300 |
| 9 |
7/6 |
310 |
| 10 |
7/8 |
520 |
| 11 |
=COUNTA(B2:B10) |
=SUBTOTAL(2,B2:B10) |
「連続する複数シート選択」
１つ目のシートを選択しShiftキーを押しながら最終シートを選択します。
「連続しない複数シートの選択」
１つ目のシートを選択し、Ctrlキーを押しながら選択したいシートをいくつも選択します。
手順２ 複数シートの「検索」
複数シートを選択した状態で「編集」「検索」(置き換え),｛検索文字の入力｝「次を検索」ボタンを押します。
手順３ ほかのシートでの「検索」結果の表示
「検索」ダイアログボックスを閉じた後でも［Ｆ４］キーを押すことで、最後に行った検索を繰り返すことができますので、シートを選択し直して［Ｆ４］キーを押して検索すれば、ほかのシートでの検索結果が確認できます。
複数の検索条件に一致するセルの値を合計する方法、個数を数える方法（配列数式）
構文
=SUM(IF(検索条件,IF(検索条件,合計範囲,0),0))
と入力し{Ctrl}+{Shift}+{Enter}で、「配列数式」として確定します。
確定後数式は、{ }でくくられます。
{=SUM(IF(検索条件,IF(検索条件,合計範囲,0),0))}
自分で{ }を入力してはいけません。
<検索条件>
計算の対象となるセルを定義する条件を指定します。
<合計範囲>
実際に計算の対象となるセル範囲を指定します。省略することはできません。
| |
A |
B |
C |
D |
E |
F |
| 1 |
価格 |
手数料 |
分類 |
検索条件1 |
検索条件2 |
検索条件3 |
| 2 |
\100,000 |
\8,000 |
A |
120,000 |
200,000 |
B |
| 3 |
\150,000 |
\9,000 |
AB |
|
|
|
| 4 |
\200,000 |
\12,000 |
ABC |
|
|
|
| 5 |
\300,000 |
\15,000 |
DA |
|
|
|
1.価格が\120,000以上、かつ\200,000以下の手数料の合計
{=SUM(IF(A2:A5>=120000,IF(A2:A5<=200000,B2:B5,0)))}
計算結果は21,000
検索条件にセル参照を使い汎用性を持たせる場合
{=SUM(IF(A2:A5>=D2,IF(A2:A5<=E2,B2:B5,0)))}
2価格が\120,000以上、かつ\200,000以下の個数
{=SUM(IF(A2:A5>=120000,IF(A2:A5<=200000,1,0)))}
計算結果は2
検索条件にセル参照を使い汎用性を持たせる場合
{=SUM(IF(A2:A5>=D2,IF(A2:A5<=E2,1,0)))}
3価格が価格の平均(\187,500)以上、かつ分類に「B」が含まれている
手数料の合計
{=SUM(IF(A2:A5>=AVERAGE(A2:A5),IF(ISERROR(SEARCH("B",C2:C5)),0,B2:B5)))}
計算結果は12000
検索条件にセル参照を使い汎用性を持たせる場合
{=SUM(IF(A2:A5>=AVERAGE(A2:A5),IF(ISERROR(SEARCH(E2,C2:C5)),0,B2:B5)))}
(SUM関数、IF関数を使った配列数式)
SUMIF関数またはCOUNTIF関数で指定できる検索条件は1つです。
例えば「100未満、または200以上の個数を求める」場合(OR条件)は、
数式「=COUNTIF(範囲,"<100")+COUNTIF(範囲,">=200")」で値を求めることができますが、「100以上で、かつ200未満の個数を求める」場合(AND条件)や、複数の条件を組み合わせて検索する場合は、ワークシート関数であるSUM関数とIF関数をネスト(入れ子)にした配列数式(引数をまとめて処理する数式)を使います。
配列数式として数式を入力する場合は、数式を確定するときに[Ctrl]+[Shift]+[Enter]キーを押します
(または[Ctrl]+[Shift]キーを押しながら<入力ボックス>をクリックします)。
配列数式として入力された数式は、[数式バー]に中カッコ()付きで表示されます。
配列数式についての詳細は、オンラインヘルプ(キーワード「仕様」)を参照してください。
エクセルで住所録を作成したのですが、この住所録から宛名シールを作るにはどうすればいいのでしょうか？
エクセルにはラベル印刷機能がありませんので、ワードとの連系プレーになります。
エクセルで作成した住所録（印刷対象範囲に名前を付けておくとよい）をいったん保存し、ワード2000を立ち上げます。
ワードの「ファイル」「新規作成」で「社外文書」のタグの中の「宛名ラベルウィザード」を押し［ＯＫ］ボタンを押します。
「宛名作成ウィザード」で、「複数の宛先の異なるラベルをリストを使って作成する」にチェツクを入れ［ＯＫ］ボタンを押します。
「データファイル指定」ボタンを押し「データファイルを開く」を押します。
ファイルの種類をMicrosoft Excelワークシートに変更し、すでに保存してあるエクセルの住所録を開きます。
「範囲名またはセル範囲」で、ワークシート全体、あるいは検索された部分の名前があれば、その名前を選択し［ＯＫ］ボタンを押します。
次に「差し込み印刷ヘルパー」の「設定」をクリックします。
ここで宛名ラベルのメーカーや、製品番号を選択します。
「ラベルの作成」で「差込フィールドの挿入」ボタンを押し、印刷する項目を印刷する順番どおり選択していきます。
挿入後にEnterを押せば、印刷時に改行されます。（サンプルを見ればレイアウトはわかります）
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
<<氏名>>の部分の文字を大きく表示したい場合は
「差込印刷ヘルパー」の「ラベルの作成」ダイアログで「差し込みフィールドの挿入」の時点で、サンプルに表示されている<<氏名>>の部分を選択(黒く反転)し、マウスの右ボタンで「フォント」を選択し、「フォント」の「サイズ」を変更し、「ＯＫ」ボタンを押します。
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
全ての項目を挿入すれば［ＯＫ］ボタンを押します。
「差し込み印刷ヘルパー」で「差し込み」ボタンを押し、差し込むレコードの設定や、空白フィールドの処理などを設定し、「差し込み」ボタンを押します。
「差し込み」ボタンを押せばワードの文書にデータが取り込まれます。
以上は、ワードの「宛名作成ウィザード」に手順が順次表示されますので、手順の順番に操作すれば間違いありません。
なお、ワード97では、「ツール」の「差し込み印刷ヘルパー」で、同様な機能があります。
複数条件に合致するセルの合計値(orセルの個数)を求める方法（ピボットテーブル）
エクセルの機能「ピボットテーブル」を使う方法があります。
デフォルトでは、データが数字の場合「合計」、文字列の場合「カウント」を求められます。
１「データ」「ピボットテーブル…」を選択します。
ピボットテーブルウィザードが起動します。
２分析するデータソースの種類を選択してください。
この場合は「EXCELのリスト／データベース」ボタンを押して 「次へ」
３使用するデータの範囲を指定してください。
データリスト上に選択セルがあれば、自動的にリスト範囲が選択されます。
（必ず見出し列も含まれていることを確認してください）
４ピボットテーブルのレイアウトを指定し、ウィザード上の各フィールドを、
行、列、ページエリアへドラッグします。
これで集計ができ、文字列データには該当するデータのCOUNT関数と、数値データに関してはSUM関数がデフォルトで使用されていますので、そのまま使用できます。（集計の関数は、修正も可能です）
重複データを検索する
COUNTIF関数ですでに入力されているデータ範囲と、検索対象のセルを比較し、１よりも大きい場合は■を表示します。
COUNTIF関数
(指定された範囲に含まれるセルのうち、検索条件に一致するセルの個数を返します)
=IF(COUNTIF($A$1:$A1,A1)>=1,"■","")の >=1 は、重複データがある場合１以上となります。
=IF(COUNTIF($A$1:$A1,A1)>1,"■","") とすれば重複データの２番目以降に■が表示されます。
| |
A |
B |
C |
| 1 |
田中和夫 |
=IF(COUNTIF($A$1:$A1,A1)>=1,"■","") |
■ |
| 2 |
小泉純一 |
上のセルの式をＡ９セルまでコピーします |
■ |
| 3 |
田中和夫 |
|
■ |
| 4 |
金正日 |
|
|
| 5 |
田中幹生 |
|
|
| 6 |
鈴木宗男 |
|
■ |
| 7 |
小泉純一 |
|
■ |
| 8 |
田中美智子 |
|
|
| 9 |
鈴木宗男 |
=IF(COUNTIF($A$1:$A9,A9)>=1,"■","") |
■ |
その他の方法として、アドインソフトの「重複データチェック」を参照してください。
２行単位で並べ替えるには？
列を1列追加し（D列）、２行単位に同じ数字などを入力します。
具体的には、
セルD3に
=IF(MOD(ROW(),2),1,2)
と入力します。
これで、奇数行に1、偶数行に2が帰ります。
D列を選択し、「編集」「形式を選択して貼り付け」「値」を選択し「ＯＫ」ボタンを押します。
| |
A |
B |
C |
D |
| 1 |
住所 |
郵便番号 |
|
|
| 2 |
氏名 |
電話番号 |
|
|
| 3 |
東京都･･ |
100-0002 |
|
=IF(MOD(ROW(),2),1,2) |
| 4 |
前田和夫 |
03-1234-4567 |
|
〃 |
| 5 |
名古屋市････ |
456-7891 |
|
〃 |
| 6 |
吉村啓次 |
052-789-1234 |
|
〃 |