いろいろな関数 8−2

主な内容
1.[TEXT]関数
2.[WEEKDAY]関数
3.[IF]関数で空白セルを作り[TEXT]関数、[WEEKDAY]関数をネストさせます。
4.フィルタを使ってリストの値を抽出する方法
5.オートフィルタ
6.フィルタ オプションの設定

曜日を表示:[TEXT]関数に[WEEKDAY]関数をネストさせます
基礎知識:オートフィルタ、フィルタオプションの設定

[TEXT]関数

(「いろいろな関数5-3」と重複しますが再度。)

書式:「 TEXT(値,表示形式) 」

数値を指定した形式の文字列で表示する関数です。

数式パレットの引数1が「値」、引数2が「表示形式」です。
注釈では:「数値に指定した書式を設定し、文字列に変換した結果を返します。」とあり、
「値には数値、結果が数値となる数式、または数値が入力されているセルへの参照を指定します。」 となっています。
表示形式の注釈では:「表示形式には[表示形式]ダイアログボックスに表示されている数値形式を、 文字列として指定します」となっています。

[表示形式]ダイアログボックスとは、セル書式設定ー表示形式ー分類(C)ー種類(T)、 若しくはユーザー定義ー種類(T)から書式記号 (こちらを参照)、( こちらも参照)を使いますが、必ずダブル クォーティション (") で囲んで指定します。

[WEEKDAY]関数

(「いろいろな関数5-3」と重複しますが再度)

書式:「 WEEKDAY(シリアル値,種類) 」

指定した日付の曜日を求めます。

数式パレット注釈:「シリアル値を曜日に変換した結果を返します。
種類には戻り値の種類を表す
1(日曜=1〜土曜=7)、2(月曜=1〜)、3(月曜=0〜)のいずれかの数字を指定します。」となっています。

[TEXT]関数に[WEEKDAY]関数をネストし、好みの表示形式を選ぶ

TEXT(WEEKDAY(曜日の対象となる日付(シリアル値)セル番地),"aaa")

下図のようになります。カラー表示は「条件付書式設定」による
>
["aaaa"]にすると「土曜日」と表示されます。

[IF]関数で空白セルを作り[TEXT]関数、[WEEKDAY]関数をネストさせます。

IF(日付セル番地="","",TEXT(WEEKDAY(日付セル番地)"aaa"))

日付セルを[A4]とし、曜日セルを[B4]としたときに、[B4]セルに上の式を埋め込みます。

取り敢えず、1月分の曜日の習得法を下記に表示しました。「95年間年表」は、「iroiro-kansu8-3」にあります。

下表をコピーしてEXCEL[A1}セルに貼り付けてください。[A1]セルには「=YEAR("2004/5/1")」と入力し、 [YEAR]関数で2004のみを表示させます。、[C1]セルには「=MONTH("2004/5/1")」と入力し、 [MONTH]関数で5のみを表示させています。ただし、この2つの表示は、[A4]セル以下の「日にち」に連動していませんので、 この場合はただの「2004」、「5」の入力でも同じことなのですが。「連動させた場合の例は 30nen-reki」をEXCELにコピーし、B6セル以降をみていただけると分かります。(A列には1〜31の連番を振り隠してあります。)

今回は(現在シリアル値表示を Delキーで消して)に適当な日付(****/*/*形式)で記入してみてください。10行までコピーしてあります。(フィルハンドルで、月を越えて伸ばすこともできます。また、曜日も同様に伸ばします。)

お手持ちのコンピュータExcelのメニューバーの「書式⇒セル⇒表示形式タブをクリック⇒日付をクリック⇒種類を3月4日 (WinMeの場合)」に設定して置くと「月日」表示になると思います。

また、土日の青赤色表示は「セルの条件付き書式設定」で行います。

2004   5   (平成16年)
スケジュウル表
曜日 午前 午後 適用
1日      
2日      
3日      
4日      
5日      
6日      
7日      
8日      
9日      
10日      

上記の「A4」セル以下の関数入力は下記のとおりです。
=IF(A4="","",TEXT(WEEKDAY(A4),"aaa"))
=IF(A5="","",TEXT(WEEKDAY(A5),"aaa"))
=IF(A6="","",TEXT(WEEKDAY(A6),"aaa"))
=IF(A7="","",TEXT(WEEKDAY(A7),"aaa"))
=IF(A8="","",TEXT(WEEKDAY(A8),"aaa"))
=IF(A9="","",TEXT(WEEKDAY(A9),"aaa"))
=IF(A10="","",TEXT(WEEKDAY(A10),"aaa"))

MS技術情報によりますと、「 =CHOOSE(WEEKDAY(A1,1),"日","月","火","水","木","金","土") 」でも「年月日から曜日を取得」できます。「曜日を表示したいセル」に赤書き部分を埋め込んでください。上の表を使いますと「=CHOOSE(WEEKDAY(A2,1)」のように[A2]に訂正する必要があります。
今度は[IF]関数を使わないので「曜日欄」は常時に「土」の表示になります。オートフィルでコピーしても 全部「土」の表示です。それでも、例えば月日欄に「2004/6/1」などと入力すれば、「火」と返ってきます。

また、その外にも[WEEKDAU]関数を使わず[TEXT][DATE]関数でも「曜日の取得」ができます。
下の点線間をExcel[A1]セルに貼り付け
----------------
2004
6
1
----------------
- 下式を[B3]セルに貼り付けてください
=TEXT(DATE($A$1,$A$2,A3),"aaa")
後はオートフィルでコピーします。

さーて、上の表を何かに活用できないものだろうか?

基礎知識:[オートフィルタ]と[フィルタオプションの設定]

フィルタを使ってリストの値を抽出する方法

リストのフィルタ処理は、リストから特定のデータのセットをすばやく「抽出」します。
抽出されたリストには、特定の列で指定した検索条件に一致する行だけが表示されます。
リストのフィルタ処理には次の 2 つのコマンドがあります。

[オートフィルタ] コマンド

基本的な検索条件でデータを抽出する場合に使用します。また、選択した項目に基づいてデータを抽出することもできます。
オートフィルタでは、列に条件を 2 つまで適用できます。(3っつ以上条件が重なったときに[フィルタ オプションの設定]を使用することになります。)

[フィルタ オプションの設定] コマンド

[フィルタ オプションの設定] は、詳細な検索条件を指定してデータを抽出する場合に使用します。 並べ替えとは異なり、フィルタ処理ではリストの構成は変更されません。フィルタ処理では、必要のない 行が一時的に非表示になります。

用語:「抽出」と「検索」との違い

「抽出」は条件に一致したデータのみを表示し、一致しないデータは非表示になります。 (特定の列で指定した抽出条件に一致する行(レコード)のみを表示。)
「検索」はデータを表示したまま、条件に合ったセルを探します。

用語:リストとは

●ワークシートの行の系列で、例えば、請求書のデータベース、顧客の氏名と電話番号などといった関連するデータが入力された一覧。
●リストはデータベースとして使用できます。その場合は、行がレコード、列がフィールドになります。
●リストの先頭の行には列ラベルがあります。

オートフィルタ

リストでフィルタを使って条件に一致する行を表示する

通常、ワークシートの各列の一番上には見出し (項目名) があり、データの内容を説明しています。以下は設定から抽出するまでの手順です。

  1. データの内容を抽出したい表内の、任意のセルをアクティブにします。
  2. メニューバー[データ(D)]⇒[フィルタ(F)]ポイント⇒[オートフィルタ(F)]をクリックします。(チエックマークが付き、同時に抽出するリストの列見出し (列ラベル) の右側にオートフィルタ▼矢印が表示されます。)
    (解除は同様な手順で⇒[オートフィルタ(F)]をクリックし、チエックマークを外します。)
  3. 特定の値が含まれる行だけを表示するには、表示するデータが含まれる列のオートフィルタ▼矢印をクリックします。
  4. 値をクリックします。(各項目毎のリスト:重複しないで表示されています。)
  5. 他の列の値に基づいて条件を追加するには、他の列で手順 3 〜 4 を繰り返します。

1.「1 つの列に対して 2 つの条件を使ってリストを抽出する場合」、または
2.「 "=" 以外の比較演算子を使用する場合」(不等号[<] [>]使用の場合)
は列のオートフィルタ▼矢印をクリックして「 (オプション)」 をクリックします。

注):1 つの列にフィルタを使用した場合、他の列で使用できるフィルタは、最初に抽出されたリストの表示されたセル(可視セル)の値だけです。

オートフィルタの抽出オプション

各列のオートフィルタ▼矢印をクリックすると、「リストボックス」が表示され、空白セル (スペースだけのセル) および空白以外のセルも含め、その列の一意な表示セルがすべて一覧で表示されます。この一覧から項目を選択すると、選択した値が入力されていないセルの行が、すべて非表示になります。

「(すべて)」:すべての行を表示します。
オートフィルタ抽出を実行した項目は、▼矢印が青色になります。その項目を元どおり表示したいときに(すべて)をクリックします。
「(トップテン)」:項目または%で指定した範囲に含まれるすべての行を表示します。
[数値]と[日付データ]の場合に使うことができます。
ダイアログボックスの左窓では、上位からか、または下位からかを選択します。中央窓では、[順位]または[%値]の数値を入力します。右窓で、それが項目か%かを選択します。
「(オプション)」:
1 つの列に対して 1 つまたは 2 つの比較検索条件を演算子 AND (既定の演算子) で指定してリストを抽出します。

1.検索条件を「 1 つ」指定するには、
[抽出条件の指定] の 上段の記入欄、「検索値入力ボックス」へ「検索値」を入力します。入力は▼矢印をクリックして「表示されるデータ」から選択するかまたは、直接入力します。上段右側では「比較方法」を、▼矢印をクリックして表示される「比較語句」から選択します。 大小を比較できるのは、数値データの場合です。

なお、オートフィルタ オプション ダイアログボックス下段に、[?を使って、任意の1文字を表すことができます。]と[*を使って、任意の文字列を表すことができます。]となっております。 [?]と[*]はワイルドカードといいます。「検索値」が文字列の場合で「1字忘れ」、「1文字以外殆ど忘れ」の場合に利用できます。

ワイルドカードの使い方
ワイルドカードとは、[?]や[*]などを使って文字列を検索する方法です。 [?]には1文字だけ入り、[*]には何文字でも入ります。例えば下図氏名欄の▼をクリックした場合の「検索値」です。
2.「2 つの条件」を使ってリストを抽出して表示するには、
上段左右の記入欄の入力した後で、 下段に示す条件との関係が「上段且つ下段の条件を満たす」には [AND(A)]接続を選択し、「上段または下段いずれかの条件を使ってリストを抽出して表示するには」[OR(O)]を選択し、下段の「検索値入力ボックス」と「比較方法」を入力します。
3.抽出されたリストの表示:項目を抽出した列や行は、青で示されます。また、 抽出した列のオートフィルタ矢印が青で表示され、抽出された行番号も青で表示されます。
「(空白セル)」:
空白セルの行だけを表示します。
「(空白以外のセル)」:
値が入力されている行だけを表示します。
注): [(空白セル)] および [(空白以外のセル)] は、抽出する列に空白セルがある場合に限り有効です。

下図は[オートフィルタ・オプション]です。(適用例としては、あまりしっくりしないけれど)


フィルタ オプションの設定

列に 3 つ以上の条件を適用したり、ほかの場所にデータを抽出したりする場合は、フィルタ オプションを設定して、より詳細な検索条件を使用します。

操作手順
  1. データ範囲の外側に抽出条件を入力します。
    抽出したい項目だけで結構ですが、元表からのコピーが望ましいです。
    例えば、国語   数学   英語
        =>70   =>70    =>70  のように項目の下に条件を入力します。
    下表をコピーし、[A1]セルに貼付けてご使用下さい。
    氏名 性別 国語 数学 英語 合計点
    山田太郎 男性 80 90 80 250
    川田花子 女性 75 70 70 215
    草田一郎 男性 60 85 65 210
    木田一美 女性 50 80 80 210
    上田春子 女性 65 75 80 220
    中田夏男 男性 60 55 95 210
    下田秋子 女性 65 70 65 200
    天野和雄 男性 65 70 65 200
    地脇智子 女性 60 60 60 180
    人見賢治 男性 65 65 70 200
    氏名 性別 国語 数学 英語 合計点
      >=70 >=70 >=70
    以上出題
    以下回答
    氏名 性別 国語 数学 英語 合計点
    山田太郎 男性 80 90 80 250
    川田花子 女性 75 70 70 215
    氏名 性別 国語 数学 英語 合計点
      >=70 >=70 >=70
  2. データを抽出したい表の中の適当なセルをクリックします。 (この行為で、これからこの表について〜することを意味しているのです。)
  3. [データ]⇒[フィルタ]⇒[フィルタオプションの設定]をクリックします。
    フィルタオプションの設定ダイアログボックスが現れます。
    (この時点で、自動的に表示された範囲が正しければ、そのままにします。)
  4. 抽出先の選択ですが、
    1.[選択範囲内] ;は元表内で結果を表示することです。
      上の表下段のようにリストが折りたたまれ表示され、対象外のデータは非表示(9.で再表示)になります。
    2.[指定した範囲] ;元表以外の任意の場所に処理結果を表示します。
      (1.の[選択範囲内]の記入欄に[指定する番地]が1つ増えただけです。)
       別Sheetへでも可能です。(一寸したコツを必要としますが)(ここでは、選択範囲内を選んだとします。)
  5. [リスト範囲] :データを抽出したい表の範囲を選択(2の行為で自動的に入力されています。)
    既入力を取り消して、自分でセル参照で入力しても結構です。(Sheet番号付き、絶対参照で入力されます。)
  6. [検索条件範囲] :1で入力した条件を項目を含めて、セル参照で範囲指定します。
  7. [重複するレコードは無視する(R)]へチェックを入れておけば、重複レコードは抽出されません。
  8. 入力完了したら [OK] をクリックで完成です。
  9. なお、抽出の解除は、[データ]⇒[フィルタ]⇒[すべて表示]を選択します。
[検索条件範囲]の検索条件指定の仕方で[OR]検索にする

上の表では一行で「条件設定」をしましたが、その場合の条件設定は[AND]検索になります。
そこで、各項目毎に行を替えて設定しました。すると[OR]検索になります。下図と上の表を比較してくだされば、その違いがお分かりかと思います。(なお、今回は[重複するレコードは無視する(R)]へのチェックは入れてありません。)



トップへ戻る     いろいろな関数目次へ戻る
いろいろな関数8へ戻る     いろいろな関数8-2-2へ進む

inserted by FC2 system