OpenOfficeで同姓同名を排して成績を検索する
下の画像は、某社の個人売上成績表です。
ご覧のとおり、同姓同名が結構います。同姓同名の人を上手く排して、個人の売上成績を抜き出す方法を紹介します。
 |
| 送信者 OpenOffice |
 |
| 送信者 OpenOffice |
この表のH列からJ列の入力は、リストによる入力になっています。
リストの作り方は、「OpenOfficeで膨大なリストのなかから、ほしい項目を効率的に見つけ出すには」 で紹介した方法を用いています。設定した入力規則は、上のファイルをダウンロードしていただき、H2セル、I2セル、J2セルをそれぞれ個別に選択して、ツールバのデータから入力規則をクリックしてソースを確認してください。
さて、該当する個人の成績を抜き出すための関数はVLOOKUP関数を用いています。
関数名：VLOOKUP関数
書式：=VLOOKUP(検索値;検索範囲;列番号;検索のタイプ)
何ができる：検索範囲に指定された表の1列目から検索値を探し出して、同じ行にある、指定した列番号の値を返す。
平たく書くと：=VLOOKUP("勝手なシンドバッド";C17:F22;2;0) で、上の表の場合、売上件数の値34が返されます。
ここでポイントになるのは、検索範囲になる表の起点と終点です。
起点の設定
上の画像では、中部統括支店の勝手なシンドバッドの成績を検索したいので、中部統括支店が何行目から始まっているかが、起点の行番号になります。=ADDRESS(MATCH(I2;B1:B300;0);3)
MATCH関数を使って求めると、17が返されるので、行番号を17、列番号を名前が入力されている3列目として、ADDRESS関数でC17を返させます。
*MATCH関数では、検索のタイプに0を設定すると、検索範囲内に同じデータが複数存在する場合、最初に現れる行を返します。そのことを、ここでは利用しています。
*また、タイプを1に設定すると、 検索範囲内に同じデータが複数存在する場合、最後に現れる行を返します。
*検索の対象にする表をなぜC列から始めるかと言えば、VLOOKUP関数では、検索値が入力されている列は必ず1行目でなければいけないからです。
終点の設定
=ADDRESS(COUNTIF(B2:B300;I2)+MATCH(I2;B1:B300;0)-1;6)ここでは、中部統括支店が、B列に何個入力されているかを数えます。その値を、中部統括支店が最初に入力されている行番号に加え、1を引くと中部統括支店の最後の行番号が得られます。ここでは22が得られます。
こんな面倒なことをしなくても=ADDRESS(MATCH(I2;B1:B300;1);6)
で済むのではないかと考えても無理はないのですが、こうすると、一番最後に入力されているエリアを検索対象にするときにエラーがでます。これを回避するための措置です。
得られた行番号と、表の最終列である6列目をADDRESS関数に当てはめてF22を得ます。
上のプロセスで得られた起点と終点のアドレスにそれぞれINDIRECT関数をかぶせて再計算できる形にしてVLOOKUP関数に組み込みます。
=VLOOKUP(J2;INDIRECT(ADDRESS(MATCH(I2;B1:B300;0);3)):INDIRECT(ADDRESS(COUNTIF(B2:B300;I2)+MATCH(I2;B1:B300;0)-1;6));2;0)
また、このように数式が長くなるのを好まないときは、起点と終点のアドレスをどこかに出力しておき、そのアドレスにINDIRECT関数をかぶせてVLOOKUP関数に組み込む方法もあります。
ここではN1セルに起点を出力してN2セルに終点を出力してみました。
=VLOOKUP(J2;INDIRECT(N1):INDIRECT(N2);2;0)
これで中部統括支店の営業担当者「勝手なシンドバッド」の売上件数３４が得られました。
さて、売上金額、利益については上の数式と仕組みはまったく同じで、列番号だけを変えています。
売上金額の場合：=VLOOKUP(J2;INDIRECT(N1):INDIRECT(N2);3;0)
利益の場合：=VLOOKUP(J2;INDIRECT(N1):INDIRECT(N2);4;0)
なお、この表と数式を成立させるためには、エリア＞支店名＞社員名のルールに基づいて表が構成されている必要があります。
たとえば、北部の北野支店のデータが16行目でいったん終了して再び22行目から始まったりすると正確な結果を返しません。
この場合は再度並べ替える必要があります。
 |
| 送信者 OpenOffice |
Windows Media Playerの覚え書き
英語の聴き取りのトレーニングのためにいいのではないかと思われるWINDOWS Media Playerの設定
Ctrlキーと3を押すとプレイビューモードになる適当なところで右クリックすると拡張設定ができるメニューが登場する
再生速度を選択
英語のリスニングのトレーニングにWINDOWS Media Playerを使うとき、速聴モードで聴くと、英語に慣れるのが早くなるのではないかと思われます。
グラフィックイコライザ
英語と日本語では聴こえてくる周波数が全然違うそうです。英語は8000ヘルツ以上、日本語は2000ヘルツ前後だそうです。
だから、同じ音量でも英語のほうが聴き取りにくいのは当然なのかもしれません。そんなときは、グラフィックイコライザで高音域を上げてやるといいのではないかと思われます。
Ctrlキーと1を押すとライブラリを表示する。
OpenOfficeで膨大なリストのなかから、ほしい項目を効率的に見つけ出すには
たくさんのアルバムリストや、営業所、部署ごとの社員名などを管理するのは結構大変ですよね。
下の形式でリスト管理すると案外楽にできるかもしれないです。
このリストでは、E2セルでアーチスト名をリスト選択すると
 |
| 送信者 OpenOffice |
選択したアーチストの登録アルバムのタイトルがF列で選択できるようになり
 |
| 送信者 OpenOffice |
さらに選択したアルバムの中の曲名がG列で選択できるようになります。
 |
| 送信者 OpenOffice |
入力規則は、関数を含めた数式を使って設定することができるので、上のようなことが可能になります。
このファイルは、SKYDRIVEからcalc161.odsをダウンロードしてください。
E2セルの入力規則に設定する数式を作成
A2:A300これは数式というようなものではなく、ただのセルの範囲です。
ただし、そのままではおなじアーチスト名が何度もリストの中に登場することになるので、下の2点の設定を行ないます。
- ツールバーのデータ＞入力規則で、入力値の種類をセルの範囲に設定する
- 空白セルの許可のチェックを外す
- エントリを昇順に並べるにチェックを入れる
- ソースの欄に上記のプロセスで作成した数式を入力する
 |
| 送信者 OpenOffice |
 |
| 送信者 OpenOffice |
このことは、以下F2セルの設定、G2セルの設定でも同じです。
F2セルの入力規則に設定する数式を作成
INDIRECT(ADDRESS(MATCH(E2;A1:A300;0);2)):INDIRECT(ADDRESS(COUNTIF(A2:A300;E2)+MATCH(E2;A1:A300;0)-1;2))リストの起点の設定
MATCH関数を使ってE2セルに入力されているアーチストのセルが何行目から始まっているかを探す。MATCH関数で返された値を行番号とするMATCH(E2;A1:A300;0)
アドレスを生成
ADDRESS(MATCH(E2;A1:A300;0);2)
INDIRECT関数をかぶせて数式の中で利用できるようにする
INDIRECT(ADDRESS(MATCH(E2;A1:A300;0);2))
リストの終点の設定
A2:A300の間に、E2の値が何個入力されているかを数えるCOUNTIF(A2:A300;E2)
入力されている個数に、MATCH関数を使って求めた行番号を加えて1を引く。 この値を行番号にする。
COUNTIF(A2:A300;E2)+MATCH(E2;A1:A300;0)-1
曲名はB列に入っているので列番号を2に設定してアドレスを生成
ADDRESS(COUNTIF(A2:A300;E2)+MATCH(E2;A1:A300;0)-1;2)
INDIRECT関数をかぶせて数式の中で利用できるようにする
INDIRECT(ADDRESS(COUNTIF(A2:A300;E2)+MATCH(E2;A1:A300;0)-1;2))
完成したら始点と終点の設定を、始点:終点の形にして、入力規則にする。
G2セルの入力規則に設定する数式を作成
INDIRECT(ADDRESS(MATCH(F2;B1:B300;0);3)):INDIRECT(ADDRESS(COUNTIF(B2:B300;F2)+MATCH(F2;B1:B300;0)-1;3))F2セルの設定と同じ構造です。
ただし今度は、検索する対象をF2にする。
曲名が入っているC列を対象にするので、ADDRESS関数で列番号を3にする。
完成したら始点と終点の設定を、始点:終点の形にして、入力規則にする。
注意すること
元のリストを下の手順で並べ替えておく必要があります。 |
| 送信者 OpenOffice |
 |
| 送信者 OpenOffice |
OpenOfficeで特定の期間内の売上を集計する SUMPRODUCT関数編
前回OFFSET関数を使って特定の期間内の売上を集計する方法を紹介しましたが、でもやっぱりSUMPRODUCT関数でやってしまうのが一番簡単でわかりやすい気がします。
J4セル
集計開始日の設定 (DATE(A2:A300;B2:B300;C2:C300)>=DATE(G4;H4;I4))
集計終了日の設定 (DATE(A2:A300;B2:B300;C2:C300)<=DATE(G5;H5;I5))
集計対象セルの設定 (D2:D300)
DATE関数を使えば上のように簡単に出来ます。
 |
| 送信者 OpenOffice |
左下のタブでSUMPRO1をクリックしてください。
期間内で土日を除く曜日の売上を集計したい場合
曜日を区切る設定(WEEKDAY(DATE(A2:A300;B2:B300;C2:C300);2)<=5)を条件の設定に付け加えます。
WEEKDAY関数で、タイプを2に設定すると、月曜日が1、火曜日が2、以下続いて金曜日が5、土曜日が6、日曜日が7になります。
そこで、DATE関数を使用して=DATE(年;月;日)で処理したい日を設定してその曜日番号が5以下であれば集計の対象にします。
下の画像では、曜日番号を表示していますが、E列を再計算の対象にしていないので、表示する必要は特にありません。
 |
| 送信者 OpenOffice |
左下のタブでSUMPRO2をクリックしてください。
さらに期間内の月水金の曜日だけを集計したい場合
曜日を区切る設定(WEEKDAY(DATE(A2:A300;B2:B300;C2:C300);2)<=5)に加えて、
(MOD(WEEKDAY(DATE(A2:A300;B2:B300;C2:C300);2);2)=1)
を付け足します。
月水金は、曜日番号で言えば１、３、５に当たります。2で割ると余りが1になるので、MOD関数で2で割ったときの余りを返し、1になる日を処理対象にすることにします。
火木のみ対象にしたい場合は、0になる日を対象にしたらいいことになります。
 |
| 送信者 OpenOffice |
左下のタブでSUMPRO3をクリックしてください。
OpenOfficeで特定期間の売上を集計する または、集計の期間を簡単に変更するには
OFFSET関数を使うと参照範囲を簡単に変更出来ます。
たとえば、下の表では11月1日から15日の売上がC列に入力されています。
集計の開始日を1日として、集計の終了日を可変的に累計を求めることができる数式を紹介します。
 |
| 送信者 OpenOffice |
主に使用する関数は、OFFSET関数とMATCH関数です。
関数名：OFFSET関数
書式：=OFFSET(基準セル;行;列)
何ができる：基準セルから指定した行数と列数分を移動したところに位置するセルの値を返す
平たく書くと：=OFFSET(B2;3;0) で、B2セルから、3行0列移動したところにあるセルの値を返します。=B5セルの値が返ります。
関数名：MATCH関数
書式：=MATCH(検索値;検索範囲;検索タイプ)
何ができる：検索範囲のなかで、検索値が何番目のセルにあるかを返す。
平たく書くと：=MATCH(4;B2:B10;0) で4が、B2:B10の範囲の中で何番目に登場するかを返します。
ここでは10日までの累計を求めるので、10日のデータが1日の行から何行離れた位置にあるかを、MATCH関数で求めます。
=MATCH(G3;B2:B16;0) で、上の表の場合、8が返されます。
但し、8は、開始の行（ここでは1日の行)も含んでいるので、1を引きます。
=MATCH(G3;B2:B16;0)-1
これで7が返されます。これで、1日のデータがある行から7行下に10日の業があることが分かります。
この値をOFFSET関数に当てはめます。
=OFFSET(C2;MATCH(G3;B2:B16;0)-1;0) で 11月10日のデータである13が返されます。
この値を、SUM関数に当てはめます。
=SUM(C2:OFFSET(C2;MATCH(G3;B2:B16;0)-1;0)
以上のプロセスで、11月1日から10日までの累計売上が96万円だと分かりました。
終了日を4に設定すると、33万円が返され、ごく簡単に希望道理の期間の累計が得られます。
OpenOfficeの串刺し集計 エクセルと数式の書き方が異なります。
ほかのシートのセルを参照する場合の、エクセルとの表記の仕方の違い
OpenOfficeでは、ほかのシートの値を参照するとき、シートの名前の後に.を付けてからアドレスを入力します。例：Sheet1.A2
エクセルでは、ほかのシートの値を参照するとき、シートの名前の後に！を付けてからアドレスを入力します。
例：Sheet1！A2
串刺し集計を行なう場合の、エクセルとの表記の仕方の違い
OpenOfficeでは、=SUM(値１.A2:値３.A2)最初のシート名とアドレス:最後のシート名とアドレスと書きます。
値１のA2から値３のA2までを合計すると、訳することができるでしょう。
が、
エクセルでは=SUM(値１:値３!A2)
アドレスは、一回しか書きません。
訳すると、値１から値３のシートのA2セルを合計すると、訳することができるでしょう。
串刺し集計で複数のセルを一挙に参照して集計する場合
OpenOffice→=SUM(値１.A2:A11:値３.A2:A11)
エクセル→=SUM(値１:値３!A2:A11)
OpenOfficeの場合↓↓↓↓↓
 |
| 送信者 OpenOffice |
エクセルの場合↓↓↓↓↓
 |
| 送信者 OpenOffice |
OpenOfficeで串刺し集計とシートの並べ替えかた
1行目 値１ 値２ 値３（←シート名)
2行目 1 10 100
3行目 2 20 200
4行目 3 30 300
5行目 4 40 400
6行目 5 50 500
7行目 6 60 600
8行目 7 70 700
9行目 8 80 800
10行目 9 90 900
11行目 10 100 1000
このファイルは、SKYDRIVEからcalc159.odsをダウンロードしてください。
上の値がシートのA列の2行目から11行目に入力されていると考えてください。
集計結果というシートを用意して、そこに各シートの同じ行のデータを足したいとき、いわゆる串刺し集計をしたいときどうするかを紹介します。
集計開始のシート名とアドレスの後ろに、:で、集計終了のシート名とアドレスをつなぎます。
シート名の後ろには、ピリオドを付けてからアドレスを入力します。
=SUM(集計開始のシート名.アドレス:集計終了のシート名.アドレス)
=SUM(値１.A2:値３.A2)
これで、1+10+100の計算結果111が集計結果のシートに返されます。
シートを移動する
OpenOfficeでは、シートを並べ替えることができます。ここでは値２のシートを移動してみます。並べ替え前の状態↓↓↓↓↓
 |
| 送信者 OpenOffice |
 |
| 送信者 OpenOffice |
ここでは集計結果の前に移動します。
 |
| 送信者 OpenOffice |
 |
| 送信者 OpenOffice |
シートを並べ替えると集計結果が変わります。
さて、移動すると何が変わるかといえば集計結果が変わります。シートが値１ 値２ 値３ の順番に並んでいるとき
=SUM(値１.A2:値３.A2)では
2行目 1 10 100 の場合、全てが対象になり111が返されます。
 |
| 送信者 OpenOffice |
=SUM(値１.A2:値３.A2)では
2行目 1 100 10 の場合、値２のシートに入っている10は集計の対象から除かれます。
 |
| 送信者 OpenOffice |
