Access。動画で紹介。複数の条件で抽出したい！クエリで「東京 または 神奈川」を絞り込むOR条件の基本。【Query】

Access。動画で紹介。複数の条件で抽出したい！クエリで「東京 または 神奈川」を絞り込むOR条件の基本。

＜Youtube＞

Accessのクエリで「東京の人と神奈川の人、両方リストアップしたい」と思ったことはありませんか？

クエリデザイン画面の下にある「または」という行。

実はここを使えば、いくらでも条件を追加できるんです。

初心者が迷いやすい「抽出条件」の入力ルールと、保存した後に自動で書き換わってしまう「Accessの不思議な挙動」についても詳しく解説します。

Access。クエリ。最頻値を求めるにはどうしたらいいの【Mode】

Access。クエリ。最頻値を求めるにはどうしたらいいの

＜最頻値＞

ExcelにエクスポートしてMODE.SNGL関数やMODE.MULT関数をつかうことで、最頻値を求めることはできます。

Accessでは、求めることはできるのでしょうか。

次のテーブルを用意して説明していきます。

70が2つありますので、最頻値は70。

ということで、70が出れば正解というわけですね。

Accessのクエリでは、残念ながら1発で求めることはできません。

理由は、ExcelのMODE.SNGL関数やMODE.MULT関数のような関数がないからです。

今回は2段階クエリで求めていきましょう。

まずは、何件あるのかを求めるクエリをつくります。

集計行を表示させます。

一つは、グループ化します。

もう一つは、集計行をカウントにします。

並べ替えは降順

実行します。

これで、最頻値を求めることはできていますが、このクエリをつかって、さらにカウントが1位のものを抽出させます。

まずは、このクエリを保存しておきましょう。

保存したクエリを基に、新たにクエリを作ります。

あとは、クエリデザインのTOPを1に設定すればOKです。

実行して確認してみましょう。

これで、最頻値を求めることができました。

【Access】オートナンバー型の「永久欠番」をきれいな連番に復活・振り直す裏技テクニック！【Youtube】

【Access】オートナンバー型の「永久欠番」をきれいな連番に復活・振り直す裏技テクニック！

＜新作動画＞

Accessでテーブルを操作していると、レコードを削除した際にオートナンバー型が「永久欠番」になって困ったことはありませんか？

本来、データベースの重複を防ぐための重要な仕様ですが、データの整理やテストデータの作成時など、「途中の抜けを埋めてきれいな連番に振り直したい！」という場面はよくあります。

今回は、市販の書籍ではあまり詳しく解説されていない、オートナンバー型の番号を安全にリセット・復活させるリカバリー手順を分かりやすく解説します！

Access。クエリ。得点表から、平均・最大値・最小値・レンジを求めたい【AVERAGEA】

Access。クエリ。得点表から、平均・最大値・最小値・レンジを求めたい

＜クエリ＞

得点を管理しているテーブルがあります。

Excelにエクスポートして、平均・最大値・最小値・レンジを求めてもいいのですが、どうにか、Access上で、平均・最大値・最小値・レンジを求めることはできないのでしょうか。

そこで、クエリで対応してみましょう。

作成タブのクエリデザインをつかいます。

平均・最大値・最小値・レンジ。それぞれの演算フィールドをつくります。

平均: Avg([得点])

最大値: Max([得点])

最小値: Min([得点])

レンジ: Max([得点])-Min([得点])

これらの演算フィールドで求めることができます。

平均はAvg関数。最大値と最小値は、Excelと同じMax関数とMin関数。

範囲のレンジは、最大値から最小値を引くことで、求めることができます。

Accessでも、求めることができるのですが、できないものもあります。

それは、「中央値」。

Excelの中央値を求めることができるMEDIAN関数のような関数がAccessには用意されておりません。

ちょっとした計算ならばExcelにエクスポートしなくても求めることができますが、Accessはデータベースソフト。

表計算ソフトのExcelには及ばないところはありますね。

まぁ、餅は餅屋といったところでしょうか。

Access。Excelのように通貨スタイルの「￥マーク」を表示するには【currency】

Access。Excelのように通貨スタイルの「￥マーク」を表示するには

＜プロパティー＞

Excelでは、合計などで、￥マークをつけることがあります。

Accessでは、どのようにしたらいいのでしょうか。

Accessのテーブルはあくまでも、データなので、見栄えとかは必要ではありませんが、どうしてもという場合には、クエリデザインをつかいます。

通貨スタイルを設定したいフィールドを選択したら、クエリデザインタブにある「プロパティシート」をクリックします。

プロパティシート作業ウィンドウが画面右側に表示されます。

書式から「通貨」を選択します。

では、データシートビューで確認してみましょう。

 ￥マークをつけることができました。

さて、Excelには、会計という表示形式があります。

ExcelでB列に設定したのが、会計です。

会計は通貨と異なって

左側に通貨記号（￥）を固定配置

右側に数値を右揃えで配置

ゼロは “-”（ハイフン）

桁区切りあり

という設定です。

表示形式は、

_ ¥* #,##0_ ;_ ¥* -#,##0_ ;_ ¥* "-"_ ;_ @_

Accessで会計の表示をしたい場合はどうしたらいいのでしょうか。

結論は、「できません」

まず、書式で　会計　は用意されていません。

そもそも、Accessはデータベースアプリなので、見栄えとか、見やすさとは無縁です。

Excelは、セルの中に複数のレイアウト領域を持てますが、Accessは、データを構造化するものなので、フィールドには「値」しか持てません。

なので、できないわけです。

あえて、近づけるとしたら、次のようにしてみました。

会計: "¥ " & Format([販売金額],"#,###")

と演算フィールドをつくってみました。

では、実行してみましょう。

￥マークは左揃えにできましたけど、会計とは全く違いますよね。

どうしてもやりたいようでしたら、Excelにエクスポートして￥マークをつけましょう。

Access。クエリ。桁区切りカンマをつけて表示したい【comma】

Access。クエリ。桁区切りカンマをつけて表示したい

＜プロパティシート＞

区切りカンマを付けて表示したいとき、Excelならば、桁区切りカンマのボタンを押すとか、表示形式でカンマをつけて対応します。

では、Accessではどのようにしたらいいのでしょうか。

基本的に、データベースのデータとしては、表示形式はどうでもよく、あくまでも、見栄え・見やすさだけで、桁区切りカンマのつけるという考えになります。

まずテーブルです。

ちなみに、テーブルには、桁区切りカンマはつけません。

テーブルは「データの倉庫」であって、見た目を整える場所ではないからですね。

そこで、クエリをつかいます。

作成のクエリデザインでつくっていきましょう。

フィールドを設定します。

桁区切りカンマを設定しますので、設定したいフィールドを選択して、クエリデザインタブのプロパティシートをクリックします。

画面右側に、プロパティシート作業ウィンドウが表示されます。

書式から「標準」を選択します。

これで、桁区切りカンマは、つくことはつくのですが…

実行して、データシートビューで確認してみましょう。

標準だけでは、小数点第2位まで表示されてしまいます。

小数点はいらない場合は、次の設定を行う必要があります。

デザインビューに戻りましょう。

プロパティシートの小数点以下表示桁数を0にします。

改めて、実行して確認します。

これで、桁区切りカンマをつけることができました。

もしかしたら、Format関数でも桁区切りカンマをつけれるよと思うかもしれません。

Excelにも表示形式を設定するFORMAT関数をつかうことで、桁区切りカンマを設定することができます。

しかし、ちょっと問題があります。

Format関数を使った演算フィールドをつくりました。

関数: Format([販売金額],"#,##0")　

という演算フィールドです。

実行して、データシートビューで確認します。

確かに、桁区切りカンマはついたのですが、左揃えになってしまいました。

Format関数の結果は、文字列扱いになるので、左揃えになってしまうのが、欠点です。

そのため、あくまでも、見栄え・見やすさということであれば、プロパティシートで設定するほうがいいように思えます。

Access。クエリ。And+Or条件で抽出するなら、Excelだと大変です。【extraction】

Access。クエリ。And+Or条件で抽出するなら、Excelだと大変です。

＜抽出＞

Excelデータがあります。

品川店のA定食と横浜店のB定食だけに抽出するには、どうしたらいいでしょうか。

Excelのオートフィルターでは対応できません。

店舗名は、品川店と横浜店。商品名は、A定食とB定食

結果は、思っているように抽出できません。

品川店はA定食のみにしたいのに、品川店のB定食も抽出されてしまっています。

あくまでも、オートフィルターは、列内のOR条件の優先順が高く、そのあとにAND条件をおこなうからです。

そのため、Excelでは、データタブのフィルターの詳細設定を使う必要があります。

 

ただ、詳細設定をおこなうには、条件行を作る必要があります。

1行目に、見出し行をつくりD2:F3に抽出条件をつくり、フィルターオプションの設定ダイアログボックスを表示して、抽出させる必要があります。

あるいは、店舗名と商品名を結合した、店舗名商品名という列をつくり、つくった列をつかって抽出させる方法もあります。

ただ、列を増設しますので、ファイルサイズは大きくなりますし、結局作業をしなければなりません。

では、Accessのクエリならどうなるのでしょうか。

ExcelデータをAccessにインポートしています。

挿入のクエリデザインをつかって、クエリをつくります。

販売日と店舗名と商品名の列をつかいます。

抽出条件には、品川店でA定食

「または」には、横浜店でB定食

と設定します。

では、実行してみましょう。

問題なく、品川店でA定食　と横浜店でB定食　だけを抽出することができました。

Accessは、Excelと異なった抽出ルールになっております。

場合によっては、Accessのほうが、抽出がスムーズになるかもしれません。

Access。クエリ。品川店と横浜店の売上金額合計を求めるにはExcelよりいいかも。

＜Sum+IIf関数＞

店舗名が品川店と横浜店の合計金額を求めたい場合、SUMIF関数一つだけではもとめることができません。

もし、SUMIF関数をつかう場合で、わかりやすくという前提ならば、次のような数式をつくります。

=SUMIF(D2:D169,"品川店",I2:I169)+SUMIF(D2:D169,"横浜店",I2:I169)

なんで、SUMIF関数が2つあるのかというと、2つめの引数の「検索条件」がOR条件に対応していないからです。

またOR関数をつかった数式に変えてみます。

=SUMIF(D2:D169,OR("品川店","横浜店"),I2:I169)

これだと、0になってしまいました。

原因は、先ほども紹介したように、検索条件にOR関数を使ったからです。

SUMPRODUCT関数をつかうなど、対応方法はありますが、算出するのはちょっと面倒です。

では、Accessのクエリだったらどうなるのでしょうか。

Accessのクエリデザインをつかって、演算フィールドをつくってみます。

テーブルは、Excelのでーたをインポートしています。

クエリデザインで演算フィールドをつくります。

そして、クエリデザインタブの集計ボタンをおして、集計行を表示します。

合計金額: Sum(IIf([店舗名] In ("品川店","横浜店"),[金額],0))

集計行には、演算　と設定します。

では実行してみましょう。

Excelで求めた値と同じ結果であることが確認できました。

では、演算フィールドを確認しておきましょう。

合計金額: Sum(IIf([店舗名] In ("品川店","横浜店"),[金額],0))

Sum関数は合算する関数です。

何を合算するのかというと、IIf関数で合致しているものです。

IIf([店舗名] In ("品川店","横浜店"),[金額],0)　は、

もし、店舗名が、品川店または横浜店だったら金額フィールドをつかって、そうでなければ0をつかうという条件式なので、品川店と横浜店ならば、金額を足すことができるというわけです。

単純ですが、OR条件の合計の場合、Accessのクエリという方法もアリなのかもしれません。

Access。クエリ。ExcelのSUMIFS関数のように複数条件で合計を求めるには【multiple conditions】

Access。クエリ。ExcelのSUMIFS関数のように複数条件で合計を求めるには

＜Access＞

複数条件での合計をExcelで求めるには、SUMIFS関数をつかいます。

 例えば、売上表があって、店舗名が　品川店　で、商品名が　ラーメンセット　の金額合計を求めたい。

そこで、SUMIFIS関数をつかった数式をつくります。

=SUMIFS(I2:I169,D2:D169,"品川店",F2:F169,"ラーメンセット")

最初の引数に合計対象範囲があって、そのあとに、条件範囲と条件を設定します。

SUMIFS関数がないAccessでは、どのようにしたらいいでしょうか。

まず、挿入タブのクエリデザインをつかって、クエリをつくります。

使うフィールドを設定します。

今回は、店舗名・商品名・金額フィールドです。

集計したいので、集計ボタンを押して、集計行を表示します。

店舗名と商品名はグループ化させます。

金額は、複数条件の合計を求めたいので、合計を選択します。

今回は、品川店のラーメンセットの合計を求めたい。

なので、店舗名フィールドの抽出条件には　品川店

商品名フィールドの抽出条件には　ラーメンセット　を設定したら、実行します。

これで、品川店のラーメンセットの金額合計を求めることができました。

結果は、Excelと同じになっていることが確認できました。

Access。動画で紹介。複数条件の抽出をクエリで作成するテクニック【Multiple conditions】

Access。動画で紹介。複数条件の抽出をクエリで作成するテクニック

＜Youtube＞

Accessのクエリで、「AかつB」のように複数の条件を満たすデータだけを抽出したいときはどうすればいいのでしょうか？

今回は「都道県が神奈川県」で、かつ「担当地域が横浜」という人をピックアップする例を使って、AND条件の作り方を実演します。

ポイントは、抽出条件を「同じ行」に書くこと！

この基本を知るだけで、複雑なデータ抽出も自由自在になりますよ。

Access。クエリ。単一条件の合計。ExcelならSUMIF関数。Accessではどうするの？【sumif】

Access。クエリ。単一条件の合計。ExcelならSUMIF関数。Accessではどうするの？

＜SUMIF関数＞

売上表があります。

品川店の金額合計を求めたい場合、Excelならば、SUMIF関数をつかうことで、求めることができます。

SUMIF関数は、単一条件の合計を求めることができる関数です。

L2にSUMIF関数の数式を設定してみます。

=SUMIF(D2:D169,K2,I2:I169)

結果は1526650

では、Accessでは、どのようにしたらいいのでしょうか。

Accessには、SUMIF関数はありませんが、クエリと集計をつかえば、難しくありません。

テーブルから作成タブのクエリにある「クエリデザイン」をつかって、求めてみましょう。

使うフィールドは、店舗名と金額です。

集計行を追加しますので、集計ボタンをクリックします。

店舗名は、そのままでもいいのですが、品川店だけなので、抽出条件に「”品川店”」と設定します。

集計行は「グループ化」のままにします。

金額は、集計行をグループ化から「合計」に変更します。

あとは、実行します。

結果は1526650

ExcelのSUMIF関数と同じ結果になりました。

このように、Accessの場合には、クエリをつかって、抽出条件と集計行の合計で求めることができます。

Access。動画で紹介。クエリデザインで「特定のデータ」を抽出する方法【extraction】

Access。動画で紹介。クエリデザインで「特定のデータ」を抽出する方法

＜Youtube＞

大量のデータが保存されたテーブルから、必要な情報だけをピックアップしたい……。

そんな時に欠かせないのがAccessの「クエリ」機能です。

今回はクエリデザインを使い、「神奈川県」のデータだけを抽出する最もシンプルな方法を解説します。

「クエリって何？」「どうやって条件を設定するの？」という初心者の方でも、動画を見ながら一緒に操作すれば3分で習得できますよ！

Access。クエリ。Excelで平均はAVERAGE関数。Accessでは集計で求めます。【AVERAGE】

Access。クエリ。Excelで平均はAVERAGE関数。Accessでは集計で求めます。

＜クエリ＞

先日。Accessでフィールドの平均値を求めたい場合は、DAvg関数をつかった方法をご紹介しましたが、関数をつかわないで、もっと楽に求めることができます。

次のテーブルです。

先日紹介したのは、このテーブルからDAvg関数をつかったクエリです。

その結果は、

41893.75　と求めることができました。

今回は、関数を使った演算フィールドで求めない方法です。

作成タブのクエリデザインをつかって、クエリをつくります。

金額フィールドだけをつかいます。

集計ボタンを押して、集計行を表示します。

グループ化から平均に変更します。

あとは、実行して結果を確認します。

結果41893.75とDAvg関数をつかったのと同じ結果になりました。

関数をつかうのもいいですが、クエリデザインでも十分対応することができます。

Access。Excelで順位を求めるならRANK.EQ関数。Accessでは？【RANK】

Access。Excelで順位を求めるならRANK.EQ関数。Accessでは？

＜DCount関数＞

店舗別の販売金額の順位を求めたいのですが、Excelでは、RANK.EQ関数をつかうことで、対応することができます。

C2にRANK.EQ関数をつかった数式を設定します。

=RANK.EQ(B2,$B$2:$B$6,0)

RANK.EQ関数のポイントは、2つ目の引数である「参照」に絶対参照を設定しないと範囲がズレるので注意が必要です。

あと、同じ数字がある場合は、同順位にして、次の順位はその件数分を空けた数値になります。

ところでAccessで、順位を求めるにはどのようにしたらいいのでしょうか。

というのも、Accessには、RANK.EQ関数をはじめRANK系の関数が用意されていません。

Excelと同じデータのテーブルを用意しました。

クエリデザインで順位を求める演算フィールドをつくっていきましょう。

順位の演算フィールドは

順位: DCount("販売金額","T店舗別販売金額","販売金額>" & [販売金額])+1

まずは実行してみましょう。

先ほどのExcelと同じように順位を求めることができました。

では、演算フィールドを確認しておきましょう。

順位: DCount("販売金額","T店舗別販売金額","販売金額>" & [販売金額])+1

順位を求める関数はAccessにはないので、そもそも順位とは何なのかを考えてみます。

新宿店の販売金額より大きい販売金額の件数を求めればいいわけです。

全体で大きい値は ない ならば、0　と求められます。

0位というわけにはいきませんから+1する必要があります。

これを演算フィールドで表現すればいいわけです。

件数を求めるにはDCount関数をつかいます。

最初の引数は、フィールド。

今回は販売金額の順位を求めたいので、「”販売金額”」。

2つ目の引数は、テーブルまたはクエリ名なので、「"T店舗別販売金額"」

3つ目の引数は、条件。

「"販売金額>" & [販売金額]」

この条件がポイントでして、"販売金額>販売金額” としてしまうと、"販売金額>販売金額”という文字列という条件になってしまいます。

比較演算子を「&（アンパサンド）」で結合して条件を設定します。

これで、順位を求めることができます。