FC2ブログ

おジさん学習帳

LibreOffice などOffice ソフトなどについてのブログです
おジさん学習帳 TOP  >  2011年09月
月別アーカイブ  [ 2011年09月 ] 

≪ 前月 |  2011年09月  | 翌月 ≫

L5C2.ods  関数問題 LibreOffice Calc

L5C2.ods 関数問題

今回の問題は はまってしまうと 難しい時間関連の関数です

各シートの概要

  [勤務] と [一覧表] の 2種類
  シート[一覧表]には 
「パート社員コード表」「時間給一覧」「祝日一覧」 の 3つのリストがある
  シート 「勤務」 の「★週間パート勤務時間一覧表★」におけるコードについては
   次の内容を意味する



設問1 名前の定義




設問2 関数問題の指示

1.セル範囲 C9:C31 に 「曜日」を 求めること
  なお 祝日にあたる場合は「祝日一覧」リストを参照して 「祝」と表示する
2.セル範囲 E9:E31 および L9:L15 に 「パート社員コード表」リストを参照して「氏名」を求める
3.セル範囲 H9:H31 に 次の条件による計算方法で 「基本勤務時間」を求めること
  なお 計算結果を次の例に示すように 15分単位で切り捨てること
  (例)「基本勤務時間」が 3:22  の場合 : 3:15

4.セル範囲 I9:I31 に 次の条件による表示内容または計算方法で「残業勤務時間」を求める
  なお 計算結果を次の例に示すように 15分単位で切り捨てること
  (例)「残業勤務時間」が 3:22  の場合 : 3:15

5.セル範囲 M9:M15 に 「氏名」別に「基本勤務時間」の合計を求める
  ただし 24時間を超えた場合にも 正しく表示されるように表示形式を設定すること
6.セル範囲 N9:N15 に 「氏名」別に「残業勤務時間」の合計を求める
  ただし 24時間を超えた場合にも 正しく表示されるように表示形式を設定すること
7.セル範囲 P9:P15 に 「時間給一覧」リストを参照して 
  次の計算式に基づき 「氏名」別に「給与」を求める
  なお 「残業勤務時間」の「時間給」は 2割増 とする
  また 計算結果を次の例に示すように 1の位で切り捨てる こと
  <計算式>
  給与 = 時間給 × 基本勤務時間合計 + 時間給の2割増 × 残業勤務時間合計
  (例) 「給与」が ¥12,345 の場合 : ¥12,340

8.セル範囲 Q9:Q15 に 「パート社員コード表」リストを参照して「氏名」別に「交通費」を求める



設問3 表示形式の指示

シート「勤務」のセル範囲 Q5:R5 の 表示形式を 「○月○日作成」となるように設定
(例) 4月1日 の場合 : 04月01日作成




 過去の記事を まとめてあります




スポンサーサイト



[ 2011年09月30日 15:08 ] カテゴリ:LibreOffice Calc | TB(0) | CM(0)

L4C4.ods  グラフ問題について

L4C4.ods  グラフ問題



動画の内容

1.Web サイトから ファイルと画像ファイルを ダウンロード
2.[完成イメージ]の画像を [Snipping Tool] で キャプチャ 保存
  [すべてのファイル]→[アクセサリ]→[Snipping Tool]
  Windows Vista と Windows 7 には入っている
  Windows XP は [PrintSC]や WinShot   など利用してください

 ※ すべて同じフォルダにする方がいいかな? 今回は [ダウンロード]フォルダ
3.LibreOffice Calc を起動
4.オートシェイプ 「四角」を作成
  [表面]で [領域]画面 [ビットマップ]タブ
  [インポート]クリック 今保存した 画像ファイルを指定
  [領域]タブで インポートした画像を選択
  サイズ欄 【オリジナル】のチェックを外す
  【相対】にチェックを入れる
  左下のプレビューを確認 [OK]クリック
  確認出来たら Calc を閉じる 
 ※ 一度再起動しないと グラフでは使えない
5.Calc を起動
  保存した L4C4.ods を 開く
6.グラフでは 必要のない行を 非表示にする
  7行・10行・13行を 「表示しない」 にする
7.グラフで使用したい系列名が 複数セルになっているので
1つのセル(A列)に 文字列を繋げた状態を表示する
  (例)「Aホテル」「食事有」をつなげて 「Aホテル食事有」にする
  A5 セルに =B5&C5 とし A6 セルは =B5&C6 とする
8.そのままだと 目障りなので
  フォントサイズを 小さくし セル幅が狭い表示が出ないようにする
  フォントの色を 「白色」にする 見た目には見えないようにする
9.グラフに必要なデータ範囲を選択する
  A5:A12 ・[Ctrl]キーを押しながら E5:E12 ・[Ctrl]キーを押しながら G5:G12
10.ツールバーの (グラフ)をクリック
11.「グラフの種類」で 「扇形」
  一覧から 「ドーナツ」選択
12.[次へ]と進み 「タイトル」を入力 [完了]クリック
13.指定の位置へ移動
14.保存してある「完成イメージ」の画像を 近くに表示する
  [挿入]→[画像]→[ファイルから]

「完成イメージ」の画像を見ながら グラフの編集をする


15.グラフの表面 [表面]タブ
  塗りつぶし 「ビットマップ」を選択
  一覧から 先ほど登録した「aloha」を選択
  サイズ欄 【オリジナル】のチェックを外す
  【相対】にチェックを入れる
  左下のプレビューを確認 [OK]クリック
16.見本を見ながら グラフの位置を移動する
17.「データラベル」を表示する [データラベルを挿入]
 よく見ると 系列の表示の向きが 逆回転になっている
18.[データ系列の書式]を表示
  [オプション]タブ 【 時計回りの方向 】に チェックを入れる
19.各データ系列の色を 見本と似た色にする
  2回クリックすると そのデータ系列だけを 選択できる
  周りに出来る マークで 区別できる
20.タイトルのフォントサイズ・フォントの色を設定する
21.[凡例]の 外枠の形状を 「実線」にし 「色」を「黒」にする
  位置を 少し上へ移動する
22.グラフ 系列の「外枠」も 同様に 「実線」で「黒」にする
23.大まかな系列名を表示するのですが そのような機能は無いので
  テキストボックスで 入力・表示する
  「Aホテル」と入力し フォントサイズを「10ポイント」にする
  入力したら 一度 別の場所をクリック もう一度選択し
  サイズを調整する
  あと 2か所あるので コピーし 利用する
  「A」を それぞれ「B」「C」に変更するだけ
24.穴の中へ 表示するものも これを利用して 作成する
  文字を削除し 新たに 「外側:申込数」 [Enter]キー 「内側:合計」と入力
  二重円の内側 中央に 移動する



これ以外の細かな設定で 

難しい部分はないと思うので 動画にはしませんでした

「動画キャプチャ」は 10分が 限界なのです
[ 2011年09月29日 10:34 ] カテゴリ:LibreOffice Calc | TB(0) | CM(0)

L4C4.ods  グラフ問題 LibreOffice Calc

L4C4.ods  グラフ問題

L4C4.ods    利用する画像ファイル : aloha.png 



Excel の 表計算の問題です

上の表から ドーナツグラフを作成する


はっきり 言って LibreOffice Calc の場合 そのままでは 不可能です
表に 手を加えれば 作成可能です
  作業列を作成する


完成イメージ





設定内容

 「ハワイツアー申込数」のデータから 各ホテルの申込数を比較する
 ドーナツグラフを セル範囲 B15:J32 に作成 
 内側の円に 合計 外側の円に 申込数
 タイトル 「ハワイツアー申込数」
   MS明朝 18ポイント フォント色:青 外枠:透明 表面:塗りつぶしなし
 凡例 位置:右上 外枠:実線・黒色 表面:塗りつぶしなし
 グラフの位置 「完成イメージ」を 参考
 データラベル:「申込数」および「合計」の値を表示
   フォント : 10ポイント
 グラフ表面:画像ファイル「aloha.png」を挿入
 二重円の外側に ホテル名を 表示
   フォント : 10ポイント
 二重円の内側 中央に 「外側:申込数 内側:合計」と表示する
   フォント : 10ポイント
 「合計」の領域を 「申込数」と区別する出来るように それぞれ任意の色に変更する


参考色

  明るい緑
  E2     R:153/G:51/B102
  E8     R:204/G:204/B255
  E4     R:204/G255/B:255 
  グラフ10  
  E6     R:255/G:128/B:128
  E1     R:153/G:153/B:255
  E3     R:255/G:255/B:204
  ベージュ  R:255/G:204/B:153
  ローズ   R:255/G:153/B:204
※ LK4   その1 LibreOffice Calc 
  グラフの色 設定 (Excel 2003の色と同じにしたい場合)
  使用しそうな色は 登録しておくと便利


ページ設定

 印刷範囲 B2:L38
   A4 縦 1枚に収めて印刷できるようにする
 余白 : 上下左右 2cm
   ページの中央 (水平・垂直とも)に配置
 ヘッダー : 左側 「本日の日付」(作成日)を自動挿入
 フッター : 中央 「ハワイツアー申込状況」と表示


 
個人的な感想

LibreOffice では ドーナツグラフは まだ 不完全

Excel で 作成したことがあると もどかしい

今回も 本来は 内側の円を 外側にしたかった

でも そうすると 凡例の色が違ってしまう

穴の大きさも 指定できないなど

今後に期待する



  

本当は 下図のようでなければいけない


  
[ 2011年09月28日 06:21 ] カテゴリ:LibreOffice | TB(0) | CM(0)

L4C3.ods 抽出・集計問題について

L4C3.ods 抽出・集計問題 


標準フィルタ 並べ替え 特殊フィルタ 小計


特殊フィルタでは 「.*」(正規表現)を 使用
Excel では 「*」(アスタリスク)だけですが
LibreOffice Calc では 「.」(ドット)「*」(アスタリスク)
そして 「正規表現」を利用するにチェックを入れる

Excel の集計は Calc では 小計


Excel のように 「可視セル」を コピーできないので
Calc の場合
アウトラインで 必要な部分をコピー
形式を選択して貼り付け 「テキスト」「数」(場合により「日付と時刻」)
標準フィルタで 必要な項目を 表示
見えている部分が コピー&貼り付けできる



参考解答 : L4C3_A.ods  


注意事項
  Excel と Calc では 漢字での並べ替えは 順番が違います
  同じ結果が欲しい場合は Excel での並べ替えの順番を コピーして
LibreOffice Calc の オプション「順序リスト」に登録して利用する
  または 双方とも フリガナ欄を作成し フリガナ欄を基準に並べ替える



[ 2011年09月27日 04:43 ] カテゴリ:LibreOffice | TB(0) | CM(0)

L4C3.ods 抽出・集計問題 LibreOffice Calc

L4C3.ods 抽出問題

 シートは 「売上一覧」と「分析」の2種類
 シート「売上一覧」は オジサン家具店の売上一覧表を示す
 シート「分析」は 「売上一覧」から データ抽出を行うためのもの


設問1

<抽出1>

 シート「分析」のセル B6 以降に 
シート「売上一覧」から「金額」が 上位5位に該当するデータを抽出する
 抽出したデータを 「金額」の降順に並べ替える
「金額」が同じ場合は 「単価」の昇順になるようにする

<抽出2>

 シート「分析」のセル B20 以降に
次の条件に それぞれ該当するデータを タイトルを含めて抽出する
抽出したデータは 「商品名」の昇順に並べ替えること
抽出条件を作成する場合には セル B14 以降に作成すること


設問2 集計問題


<集計作業用シートの作成>


 新規シートをシートの末尾に挿入し シート名を「集計」に変更する
 シート「売上一覧」のセル範囲 B4:I91 を シート「集計」のセル B2 以降へ貼り付ける
 シート「集計」において
   「担当者」を基準に 「金額」の合計を集計し
   さらに 「店舗名」を基準に「金額」の合計を 追加して集計すること
   なお シート「集計」の作業後の状態については 不問とする

<集計>

 シート「分析」のセル範囲 E41:E67 の該当欄に 集計結果を解答すること



[ 2011年09月26日 05:24 ] カテゴリ:LibreOffice Calc | TB(0) | CM(0)

L4C2.ods 関数問題 その2 LibreOffice Calc


設問2 関数問題(名前を利用する)


1.セル範囲 C5:C20 に 
  「区分表」リスト及び「地域別コード表」の地域別リストを参照して「店舗名」を求める




状況により 選択する範囲が 変わる

INDEX 関数と 気が付けば 簡単


範囲
     複数範囲を指定する
        複数の範囲を指定する場合は 各範囲を 「,」(半角カンマ)で 区切り
        全体を 「()」で囲む必要がある
        ( 東京都,埼玉県,神奈川県,千葉県 )   
      RIGHT(B5,1) ※ 店舗コードの右1桁が 細分コード
      2
範囲番号 店舗コードの左1桁で 区分表を参照 3列目で番号に変換できる
        VLOOKUP 関数で VLOOKUP(LEFT(B5,1),区分,3,0)

(東京都,埼玉県,神奈川県,千葉県) で 入力しても

LibreOffice Calc では 

(東京都~埼玉県~神奈川県~千葉県) になる


=INDEX((東京都~埼玉県~神奈川県~千葉県),RIGHT(B5,1),2,VLOOKUP(LEFT(B5,1),区分,3,0))


関数を覚えたての人は 

何かというと VLOOKUP 関数を使いたがる

では VLOOKUP 関数では出来ないか?



出来なくはないが ややこしくなる


それに この場合 左に列が 数値 問題が INDEX 関数を使うように求めている


どうしても VLOOKUP 関数で求めたい場合は

検索値      区分コードの右 1桁
行列       ここが問題
インデックス  2
データ順序   完全一致なので 0

どうやって 行列 セル範囲を決定するか
ここだけがポイント

状況により 名前を付けた「東京都」「埼玉県」「神奈川県」「千葉県」を 入れたい

これが 非常に重要!

文字列の「東京都」「埼玉県」「神奈川県」「千葉県」では駄目

「名前」の「東京都」「埼玉県」「神奈川県」「千葉県」


さっきも書いたが 何かというと VLOOKUP 関数を使いたがる人がいて
ここでも 「区分」から VLOOKUP 関数で 2列目を 求めて エラーになる
この場合は 求めた値は 文字列だから 「名前」ではない それでエラーになる

ここで 「名前」を取り出すには
CHOOSE 関数が使える 
CHOOSE 関数ならば 「文字列」と「名前」の「東京都」を区別して入力できる
区別して入力できるならば 区別して指定できる
どうするか
CHOOSE 関数は 最初「インデックス」次からが 選択肢
「インデックス」は 結局 区分の3列目
VLOOKUP(LEFT(B5,1),区分,3,0)
そして 以降の値 に 「東京都」などを入力
東京都 と 入力すれば 名前
"東京都" と 入力すれば 文字列
 CHOOSE(VLOOKUP(LEFT(B5,1),区分,3,0),東京都,埼玉県,神奈川県,千葉県)
これが VLOOKUP 関数を利用した場合 第2引数(行列)に入る

結果


=VLOOKUP(RIGHT(B5,1),CHOOSE(VLOOKUP(LEFT(B5,1),区分,3,0),東京都,埼玉県,神奈川県,千葉県),2,0)

最初 いい問題かな とも思ったが 

バカの一つ覚えで

VLOOKUP 関数を使いたがる人は 

元々何も考えない と思い直した


2.セル範囲 F5:F20 に 「区分表」リストを参照して「地域」を求める
  =VLOOKUP(LEFT(B5,1),区分,2,0)
3.セル範囲 G5:G20 に 次の条件による「評価」の結果が表示されるようにする
  条件を落ち着いて 整理する
  見てもわかるが 条件が2つ 2つ目の条件では 「または」=OR 条件が 加わる
  最後の「以外は」 は FALSE なのです
  =IF(E5>=15000000,"A",IF(OR(D5>=3000,E5>=12000000),"B","C"))

4.セル範囲 J5:J8 に 「地域」別に「来店者数」の合計を求めること
  =SUMIF($F$5:$F$20,I5,$D$5:$D$20)

5.セル範囲 J11:J14 に 「地域」別に「売上金額」の合計を求めること
  =SUMIF($F$5:$F$20,I11,$E$5:$E$20)

6.セル範囲 J18:J20 に 「売上金額」の上位3位の店舗名を求めること
  =INDEX($C$5:$C$20,MATCH(LARGE($E$5:$E$20,I18),$E$5:$E$20,0))

7.セル B3 に 本日の日付(作成日)を基準にして 先月の「月」を 求めること
  また 次の例が示すように 関数を用いて表示すること
  (例)本日の日付が4月1日の場合  : 3月度
  最初 簡単に考えて 
  =MONTH(TODAY())-1&"月度"
  ところが この場合 「1月」の時に 「0月」になってしまう
  IF 関数で その状況を設定する
  もし MONTH(TODAY()) が 1 ならば 12 という条件を加える
  =IF(MONTH(TODAY())=1,12,MONTH(TODAY())-1)&"月度"


設問3 表示形式

シート「売上一覧」のセル範囲 D5:D20 及び J5:J8 の表示形式を
次の例が示すように あらかじめ設定されている方区切り形式を
さらに「○人」と表示されるように設定しなさい

(例) 1,000 の場合  : 1,000人


テキストと数字

数値を含むセルに対して
テキストを含む数の書式を指定するには
二重引用符 (") でテキストの前後を囲みますが
単独の文字の場合は 
バックスラッシュ記号 (¥) (半角)を 前に付けるだけで済みます
たとえば、#.# "meters" という指定は 「3.5 meters」のような表示となり

#.# ¥m (¥が半角)という指定は 「3.5 m」のような表示となります
¥ は全角で 入力しています Web 上では 半角¥は  となり 表示できない

  
 

参考解答 : L4C2_A.ods 
[ 2011年09月25日 06:15 ] カテゴリ:LibreOffice Calc | TB(0) | CM(0)

L4C2.ods 関数問題 1 LibreOffice Calc

設問1 名前の定義



[挿入]→[名前]→[指定] から


再生のスピードを 半分に設定しました


[挿入]→[名前]→[指定] クリック

  

[名前の指定]画面で 「名前」を入力




一番下の欄に セル範囲を指定


   右端の縮小ボタンをクリックして セル範囲を選択する


[追加]クリック



    [追加]をクリックしない限り 確定ではないので 必ずクリックする
    下の欄に 入力した「名前」が移動したら 確定



[ 2011年09月25日 04:50 ] カテゴリ:LibreOffice Calc | TB(0) | CM(0)

L4C2.ods 関数問題 LibreOffice Calc

関数問題 その4

L4C2.ods 

各シートの概要


 シートは 「売上一覧」と「コード表」の2種類
 シート「コード表]には 「区分表」「地域別コード表」に含まれる
   「1.東京都」「2.埼玉県」「3.神奈川県」「4.千葉県」の 5つのリスト
 シート「売上一覧」の「店舗別売上一覧表]における店舗コードについては
   次の内容を意味する


設問1 名前の定義



設問2 関数問題(名前を利用する)


1.セル範囲 C5:C20 に 
  「区分表」リスト及び「地域別コード表」の地域別リストを参照して「店舗名」を求める
2.セル範囲 F5:F20 に 「区分表」リストを参照して「地域」を求める
3.セル範囲 G5:G20 に 次の条件による「評価」の結果が表示されるようにする

4.セル範囲 J5:J8 に 「地域」別に「来店者数」の合計を求めること
5.セル範囲 J11:J14 に 「地域」別に「売上金額」の合計を求めること
6.セル範囲 J18:J20 に 「売上金額」の上位3位の店舗名を求めること
7.セル B3 に 本日の日付(作成日)を基準にして 先月の「月」を 求めること
  また 次の例が示すように 関数を用いて表示すること
  (例)本日の日付が4月1日の場合  : 3月度

設問3 表示形式


シート「売上一覧」のセル範囲 D5:D20 及び J5:J8 の表示形式を
次の例が示すように あらかじめ設定されている方区切り形式を
さらに「○人」と表示されるように設定しなさい
(例) 1,000 の場合  : 1,000人


[ 2011年09月25日 03:57 ] カテゴリ:LibreOffice Calc | TB(0) | CM(0)

シートの移動・コピーで クラッシュしたら LibreOffice Calc

LibreOffice の フォーラムに 相談した件



実は あるシートを コピーしようと 思い

  シート見出しを 右クリック 

  [シートを移動またはコピー]を選択

  [シートを移動またはコピー]画面で 

  「移動先ドキュメント名」欄で -新規ドキュメント-

  「次の表の前に挿入」欄で -末尾に挿入-

  [OK]クリック


なんと クラッシュ してしまいました







シートをコピー・移動しようとして クラッシュした方は

[ファイル]→[ドキュメントテンプレート]→[管理] クリック


[ドキュメントテンプレートの管理]画面

右側にある[コマンド |▼]クリック

[標準テンプレートを元に戻す]

「表計算ドキュメント」クリック


これで クラッシュしなくなると思います
 (フォーラムの情報


※ ちなみに クラッシュしたのは 
オートシェイプ や テキストボックス ・ グラフ があるシートの場合です


LibreOffice 3.3.4 の場合








[ファイル]→[ドキュメントテンプレート]→[管理] クリック

[ドキュメントテンプレートの管理]画面
右側にある[コマンド |▼]クリック
[標準テンプレートを元に戻す]-「表計算ドキュメント」クリック

これで 「表計算ドキュメント」の「設定した標準テンプレート」が無くなります

こうなってから  [シートを移動またはコピー]を行なえば クラッシュしません



追加情報

LibreOffice 3.4.3 の場合 

同じことをすると (再現しようとした場合など 2回目には)

[LibreOffice ドキュメントの回復」が 終わると 自動的に

[標準テンプレート]の「表計算ドキュメント」が 削除されている

こともある (必ず そうなるわけでもないみたい)




しかし いろいろと 問題がありそう

グラフ オートシェイプ が あるシートの場合

「シートの移動またはコピー」を 行なわないほうが良い

これは 私個人の感想です




 そのまま コピーしても 問題なく シートのコピーが出来る
 テキストボックスを挿入して コピーすると クラッシュする
 標準テンプレートで 表計算ドキュメントを削除すると シートのコピーを行っても問題ない




クラッシュから 復帰後 そのまま シートのコピーが出来るケース


[ 2011年09月24日 09:31 ] カテゴリ:LibreOffice | TB(0) | CM(0)

LibreOfficeの不具合 や 要望

ちょっと前に 

LibreOffice Calc を利用していて 変なことがあったので

LibreOffice の フォーラム へ 書き込みをしました


結果 自分の操作が悪いのではないことがわかりました


フォーラムを見ていると 

相変わらず 

自分で バグ報告や登録してくださいという記述があります

基本的に バグなのか 操作ミスなのか わからない

だから フォーラムに書いている

これも 結構 勇気のいること

次に 報告・登録するサイトは 英語!

ハードルが高すぎる


でも 彼らの努力は 認める

私のような 人間がいることを 少しは理解しているようで

LibreOfficeのバグ追跡システムである BugZilla に ご登録ください
登録方法は OpenOffice.org日本ユーザー会の「Web出版」ページ  に 書きました
 もし、記述に間違いがあったら、ご指摘ください。
 第1稿なので今後ブラッシュアップしたいと思っています



2.01.03 LibreOfficeの不具合・要望の報告先は? 

メーリングリストフォーラムでは

LibreOffice の初心者的な使い方から

マクロを用いた高度な活用方法まで

さまざまなサポートを受けられます


LibreOffice に対する改善や要望などは 

LibreOffice のバグ追跡システムである

BugZilla に報告することが望まれます


BugZilla は Firefox などオープンソースのソフトウエアでは

一般的に利用されているシステムです

一般ユーザーの方にはハードルが高いかもしれませんが 

BugZilla の使い方について紹介します


BugZilla とは

BugZilla は mozilla.org が開発したバグ追跡システムです
mozilla.org は Web ブラウザ Firefox の開発でも知られています
今では オープンソースのソフトウエアの開発にはなくてはならないツールといえるでしょう
Web ベースのため 開発者だけではなく一般ユーザーもインターネットを通じて
不具合や要望の報告を簡単に行えます
ただし 日本のユーザーにとっては 
英語が公用語という点がネックとなっています
本稿  では 英語が苦手な方にも 挑戦してもらえるようにていねいに説明します


BugZilla についての情報源 
以下のWeb 上でも情報が役立ちます
 はじめての BugZilla(はじめてのバグジラ )
バグ追跡システムの目的、機能と利用方法
 OSS バグ追跡システム「BugZilla」の利用

一応書いたけど 一般人には 意味がない


LibreOffice への バグ報告方法

バグ報告前に目を通す?
 How to Report Bugs in LibreOffice (英語)
 バグ報告
  下が 日本語訳
BugZilla を検索する その1

LibreOffice のバグを発見したら・・・? 

ここがすでに?

バグなのか操作ミスなのかなんてわからないので質問する

次に FreeDesktop サイトの BugZilla にアクセス

検索欄に 「LibreOffice Writer ruler」のように入力し

[Quick Search]クリック

LibreOffice Writer のルーラーに関するバグを検索できる

上記の作業は わかったけど 表記が英語ではわかんない

結局解決しない 既に登録されていても いなくても

それさえわかんない

英語がわかるならば 操作自体は簡単で 検索はできます

以下 かなり飛ばして

バグを報告になった場合

日本語の簡単なサイトでさえ

自分のOS や LibreOffice のバージョンを書かないのに

ここまで来て 書く人間が何人いるか?

まぁ せいぜい フォーラムに書き込むぐらいまでですね

それ以上は....
 
[ 2011年09月24日 00:42 ] カテゴリ:LibreOffice | TB(0) | CM(0)

並べ替え LibreOffice Calc

並べ替え

基本「並べ替え」を 行おうと思ったら 数値が良い

昇順にしても 降順にしても 間違いようがない

一覧表(データベース)などの場合は 

ID・No ・コードなどを利用する方が良い


名前・会社名などの場合は フリガナ欄を作成し 

フリガナ欄を 基準に 並べ替える


漢字のまま 並べ替えを行うと 思うように並べ替えできない

  
現況では 「顧客ID」で 並んでいます
これを 「顧客名」を 基準に 「昇順」に「並べ替え」すると
Excel と LibreOffice Calc では 違いが発生します
  

でも 「フリガナ」を基準に 「昇順」に「並べ替え」すると 当然ですが 同じ
  

わかるとは思いますが 同じExcel でも 
基準が 漢字の「顧客名」の場合と 「フリガナ」では 違いがあります
  

ついでに LibreOffice Calc の場合も
  

ついでに フリガナに関して

何故か 馬鹿みたいに PHONETIC 関数信者がいるが そんなに有効ではない
上の名前を PHONETIC 関数で 変換して 元のフリガナと比較した
  

では LibreOffice Calc の FURIGANA 関数では
  

日本語は 難しい 名前は特に読み方がいろいろある
それを関数で 表示は 無理が生じる
名前に関しては フリガナを入力してもらう方が確実
[ 2011年09月23日 03:14 ] カテゴリ:LibreOffice Calc | TB(0) | CM(0)

L3C4.ods グラフ問題 LibreOffice Calc

L3C4.ods グラフ問題

L3C4.ods         利用画像 ファイル : stripe.bmp 


 上の「地域別会員の年齢分布」のデータから
 地域ごとの会員の年齢を比較する「3D 縦棒グラフ」を B13:H29 セル範囲に作成する

完成イメージ




 グラフタイトル : 地域別会員の年齢分布
   MSゴシック 12ポイント 影付き
 凡例 : なし
 Y軸 「○○人」と表示
 X軸 塾の並び 左から 「60代」「50代」・・・「20代」の順に表示
 Z軸 フォント 9ポイント
 グラフの表面 塗りつぶし ビットマップ「白大理石」 
 グラフの壁面 塗りつぶし 「明るい灰色」
 データ系列 「関東」を一番奥に表示
 データ系列「関東」の表面 画像ファイル「stripe.bmp」を利用
 3D 表示の設定
   X軸の周りに回転 15度
   Y軸の周りに回転 15度



印刷設定

 印刷範囲 B3:H29
   A4縦1枚に納めて印刷できるようにする
 余白 上下左右 : 2.5cm
 ページ中央(水平・垂直とも)に配置
 ヘッダー 右側 本日の日付(作成日)を 自動配置
 フッター 中央 「会員分析」と表示



参考解答 : L3C4_A.ods 
[ 2011年09月22日 15:21 ] カテゴリ:LibreOffice | TB(0) | CM(0)

L3C3.ods 抽出問題について 2 LibreOffice Calc

L3C3.ods 抽出問題 の 集計問題

この問題のパターンでは 最後の問題
「シート「分析」のセル範囲 L5:M13 の該当欄に 集計結果を解答する」


問題が Excel 用だったことから起きる現象として 

並び順が 違ってくる

最初に シート「分析」の解答すべき

「所属」の順番を ユーザー設定に登録しておく



1.シート「分析」 集計結果を解答する ★所属別データ★ の 所属欄 K6:K13 を コピー

2.[ツール]→[オプション]クリック
3.[オプション]で 「LibreOffice Calc」の「順序リスト」選択
4.一番左の[新規作成]クリック

5.「入力項目」欄に 貼り付ける
  右にある[追加]をクリック

6.「リスト」欄に 追加される 
  [OK]クリック

 シート「スコア一覧」を シート末尾に複写し シート名を「集計」にする

今回は LibreOffice 3.4.3 で 行なった場合です
1.シート見出し「スコア一覧」を右クリック
  「シートを移動またはコピー」をクリック

2.[シートの移動またはコピー]画面が表示される
  「アクション」欄 【 コピー 】にチェックを入れる
  「場所」欄 「移動先ドキュメント名」は そのまま
  「次の表の前に挿入」で - 末尾に挿入 - 選択
  「名前」欄 「新しい名前」に 「集計」と入力
  [OK]クリック

 シート「集計」において 「所属」を基準にして 「Total」及び「年齢」の平均を集計すること
なお シート「集計」における作業後の表の状態については不問とする

1.シート「集計」の データベース(一覧)の中の1つのセルをクリック
  [データ]→[小計]クリック

2.[小計]画面 [グループ 1]タブ
  「グループの基準」 「所属」選択
  「小計を計算する列」欄
  【 Total 】 に チェックを入れる
  「計算方法」欄
  【 平均 】 を クリックする

 続けて
  「小計を計算する列」欄
  【 年齢 】 に チェックを入れる
  「計算方法」欄
  【 平均 】 を クリックする

  [オプション]タブ クリック

3.[オプション]タブ 「並べ替え」欄
  【 順序の指定 】 に チェックを入れる
  「▼」をクリック
  最初に登録した順番リストを選択する

4.[OK] クリック



※ 「小計を計算する列」欄で チェックを入れたら 

その都度 計算方法を クリックする


 シート「分析」のセル範囲 L5:M13 の該当欄に 集計結果を解答すること
   ただし 数値の表示形式は 小数点第2位を四捨五入し 
小数点以下の桁数が 1桁の表示にすること


1.小計できているので 左にあるアウトライン レベル2 を クリックする

2.上にあるアウトライン レベル1 を クリックする

3.表示された中で 「所属」「年齢」「 Total 」の 結果 を コピーする


4.1列離して 右側に 形式を選択して貼り付け 「テキスト」「 数 」

5.左のアウトラインを レベル3にする

6.貼り付けたセル範囲が 選択されているので そのまま
  [データ]→[フィルタ]→[標準フィルタ]クリック

7.[標準フィルタ]画面
  項目名 「所属」   「▼」で 選択できる 
  関係 「...で終わる」「▼」で 選択できる
   値  「結果」    「▼」で 「JAVA東ボウル 結果」を選択し 「JAVA東ボウル 」の部分を 削除
  [OK] を クリック
 
8.L17:L83 セル範囲を   コピーする

9.シート「分析」の L6 セルへ 形式を選択して貼り付け 「 数 」

10.P17:P83 セル範囲も コピーし 
  シート「分析」の M6 へ 形式を選択して貼り付け 「 数 」
11.シート「分析」のセル範囲 L6:M13 を選択
  右クリックし 「セルの書式設定」選択

12.[セルのフォーマット]画面 [ 数 ]タブ
  「オプション」欄 【 小数点以下の桁数 】の右の数値を 「1」にする

  [OK] クリック



表示形式で 小数点以下の桁数を指定した時

指定した桁数以下にも数値がある場合は

小数の最後の桁で 四捨五入されます


「0.715..」で 小数点以下2桁にした場合 3桁目の「5」を 四捨五入し 「0.72」になる


[ 2011年09月22日 14:07 ] カテゴリ:LibreOffice Calc | TB(0) | CM(0)

L3C3.ods 抽出問題について LibreOffice Calc

今回の分析する元の一覧表は 周りに余分な行・列が無いので やりやすい

分析する一覧表(データベース)内の1つのセルをクリックして フィルタの設定を行えば
データベースの範囲が自動的に選択される

<抽出 1>  に関してはいつもと同じ 
 [標準フィルタ]「Total」「最大」「 5 」


そして 並べ替え



今回 難問が これ <抽出 2>

「氏名」に「田」という文字が含まれていて
かつ、「年齢」が 50代で 
かつ、「1Game」~「3Game」のいずれかが 200 スコア以上

条件を いつものように 作成すると


かつ = AND 条件 = 同じ行 に設定

いずれかが = OR 条件 = 別の行へ 設定

これで 特殊フィルタを 行うと エラーになる

この範囲は有効なクエリーを含んでいません。




こういった表示が出ると 自分の操作に自信がない人や学習中の人は迷ってしまう

このエラーは 特殊フィルタでの 条件の上限を超えているために 表示される


特殊フィルタでは 条件が 8つまで 

今回のケースでは 条件が 12 なので エラーになる

では 抽出できないか?

こういった場合には 2回に分けて抽出する


 まずは 「氏名」に「田」という文字が含まれていて 
かつ、「年齢」が 50代のデータを抽出する


1.シート「スコア一覧」の データベース内の1つのセルをクリック
2.[データ]→[フィルタ]→[特殊フィルタ] クリック

3.[特殊フィルタ]フィルタ条件にする範囲
  シート「分析」の作成した条件の

「.*田.*」と「>=50」と「<60」を 選択する



4.[詳細]クリック

  【 正規表現 】にチェックを入れる ※ 「.*」を使っている

  【 フィルタ結果の貼り付け先 】にチェックを入れる

  貼り付け先に シート「分析」のセル B23 を指定



5.「氏名」に「田」という文字が含まれている「年齢」が 50代のデータが表示される


 次に 抽出した範囲で 残りの条件を 抽出する

6.抽出された範囲が選択されているので そのまま
  [データ]→[フィルタ]→[特殊フィルタ]クリック
7.残りの条件 「1Game」~「3Game」のいずれかが 200 スコア以上の
  G15:I18 を選択する

8.そのまま [OK]クリック
  すると 同じ位置へ 抽出される

9.「Total」の高い順(降順)に 並べ替え






フィルタの適用

標準フィルタと特殊フィルタを使用すると、データ範囲の特定の行 (レコード) だけを表示できます。
LibreOffice の表計算ドキュメントでは、フィルタを使用すると、さまざまなことを行うことができます。

1. オートフィルタ 機能を使うと、
  あるデータフィールドと内容が一致するレコードのみが表示されます。
2. ダイアログ 標準フィルタ では、
  特定のデータフィールドに値が入っている範囲も定義できます。
  標準フィルタを使用すると、
  最高 3 つまでの条件を論理積または論理和の演算子でつなげることができます。
3.特殊フィルタ の場合、最高 8 つまでの条件を指定できます。
  特殊フィルタでは、条件をシートに直接入力します。

フィルタを削除するには、
 フィルタが適用されている領域内をクリックしてすべてのセルが表示されるようにします。
 それから、データ → フィルタ → フィルタの解除 を選択します。

フィルタが適用された領域から複数の行を選択すると、
 この選択には表示されている行だけでなくフィルタによって隠れている行も含まれます。
 その状態から書式を変更したり選択した行を削除したりした場合、
 その操作は表示されている行だけに適用されます。
 隠れている行は影響を受けません。

これは、書式 → 行 → 非表示 コマンドを使用して手動で非表示にした行とは逆になります。
手動で非表示にした行は、それらを含むセクションが削除されると削除されます。
[ 2011年09月22日 13:55 ] カテゴリ:LibreOffice Calc | TB(0) | CM(0)

L3C3.ods 抽出問題 LibreOffice Calc

L3C3.ods 抽出問題

L3C3.ods

各シートの概要

 シートは 「スコア一覧」および「分析」の 2種類
 シート「スコア一覧」は ボウリング大会のスコア一覧表を 示すもの
 シート「分析」は シート「スコア一覧表」からデータの抽出を行うためのもの


<抽出 1>

 シート「分析」のセルB6 以降に 
シート「スコア一覧」から「Total」が 上位5位に該当するデータを抽出すること

 抽出したデータを 「Total」の高い順に並べ替えること




<抽出 2>

 シート「分析」のセル B23 以降に 
  シート「スコア一覧」から 次の条件にそれぞれ該当するデータを
  タイトル行を含めて抽出し 抽出したデータを「Total」の高い順に並べ替えること
  ただし 抽出条件を入力する場合には セルB15 以降に作成すること



集計問題

<集計作業用シートの作成>


 シート「スコア一覧」を シート末尾に複写し シート名を「集計」にする
 シート「集計」において 「所属」を基準にして 「Total」及び「年齢」の平均を集計すること
  なお シート「集計」における作業後の表の状態については不問とする


<集計作業用シートの作成>

 シート「分析」のセル範囲 L5:M13 の該当欄に 集計結果を解答すること
  ただし 数値の表示形式は 小数点第2位を四捨五入し 
小数点以下の桁数が 1桁の表示にすること

[ 2011年09月22日 13:20 ] カテゴリ:LibreOffice Calc | TB(0) | CM(0)

TEXT 関数 LibreOffice Calc

TEXT 関数

値を書式設定された文字列に変換する


セルに入力されたデータは

[セルの書式設定]「セルのフォーマット」「数」で

さまざまな表示形式を設定できます


例えば 「1234」という 数値データを

「¥1,234」「1234.00」「1234円」などのように表示することが可能

この場合は 表示形式だけのことで 

実際の数値データが変更されるわけではない



TEXT 関数は 数値データを 文字データに変換する


TEXT( 値 ,表示形式 )

値 書式設定するデータを指定する
  数値だけでなく 文字列を指定することも可能
  その場合は 表示形式に 文字列用の書式記号を指定する必要がある

表示形式 値で指定されたデータに設定する 表示形式を示す文字列を指定する


 表示形式を示す文字列とは 
「セルのフォーマット」「数」タブ 「書式コード」欄に 表示される文字列と同じ




[ 2011年09月22日 12:45 ] カテゴリ:LibreOffice Calc | TB(0) | CM(0)

L3C2.ods  関数問題について LibreOffice Calc

L3C2.ods  関数問題 LibreOffice Calc で ポイントになりそうな点は

 設問2 の 2. セル範囲 D7:D21 に 「商品名」を求める
 設問2 の 4. セル範囲 H7:H22 に 「値引き後」の金額が表示されるようにする
 設問2 の 8. セル B4 に 本日の日付(作成日)を求めること
   次の例のように 関数を用いて表示すること
(例) 平成23年4月1日 : 売上日 : 平成23年04月01日(土)


 に関しては VLOOKUP 関数を利用することはわかると思う

  どうやって 第2引数(行列)を 決定するか?
      
  セル範囲には それぞれ「名前」はついている
      
  「名前」は 分類ごとになっている
      
  分類は 商品コードの左1桁 1~4 で 区別されている
      
  1~4 を 使い 分類を特定する
      
  CHOOSE 関数を使う


CHOOSE(LEFT(B7,1),キッチン,バス,テーブル,ユーティリティ)


 に関しては 問題の意味が理解できれば 簡単
   ある意味 国語?
   
   10% 引きの金額 = 金額の 90% = 金額×0.9
   条件の2つ目 20,000以上 30,000未満は 
    既に 1つ目の条件で 30,000以上には設定がされているので
20,000以上とするだけで 20,000以上 30,000未満の条件になる
   5%引きは 95%の金額 = 金額×0.95

=IF(G7>=30000,G7*0.9,IF(G7>=20000,G7*0.95,G7))

 セルの表示形式で 設定することが多いが 今回は 関数を使用し 表示させる

  それだけで 早合点して 日付の部分だけを 関数で 表示しがち
  問題文をよく読み 売上日 : 平成23年04月01日(土)

  「売上日 : 」 も 忘れないようにする

  あと 通常記憶しているのは yyyy/mm/dd (aaa) などだけ

  普通の人は 和暦での 表示形式は 記憶していない

  しかし 心配ない セルの表示形式を見て 確認すればよい

  どこでも良いので 空いているセルの書式設定を表示する
  [セルのフォーマット]画面 [ 数 ]タブ
  分類欄で 「日付」を選択 書式欄で 「平成」から始まるものを選択
  書式コード欄を確認 ※ コピーしても良い
  平成○○年 の部分が GGGE年 になっている


  TEXT 関数

  数値を書式設定した文字列に変換します

  TEST( 数値,書式 )

  数値(必須) : 変換する数値

  書式(必須) : 書式を表す文字列

  
  「本日の日付(作成日)を求める」なので TODAY() を TEXT 関数で 平成 の形で表示する

TEXT(TODAY(),"ggge年mm月dd日(aaa)")


これに 「売上日 : 」 を 同じセル内で表示する
 文字列を 足して表示するので "売上日 : "&
「&」で 文字を繋げる

="売上日 : "&TEXT(TODAY(),"ggge年mm月dd日(aaa)")


または 

すべてを TEXT 関数内に入れて



=TEXT(TODAY(),"売上日 : ggge年mm月dd日(aaa)")


TEXT
指定された書式に従って、数値をテキストに変換します。
構文
TEXT(数値; 書式)
 数値 は、変換される数字です。
 書式 は、書式を定義するテキストです。
 小数位と桁の区切りはセル書式に設定されている言語に従います。
 例
 =TEXT(12.34567;"###.##") は、テキスト 12.35 を返します。
 =TEXT(12.34567;"000.00") は、テキスト 012.35 を返します


[ 2011年09月22日 11:30 ] カテゴリ:LibreOffice Calc | TB(0) | CM(0)

L3C2.ods  関数問題 LibreOffice Calc

L3C2.ods  関数問題

L3C2.ods 

各シートの概要

 [売上表]と[商品一覧]の2種類
 「売上表」の「金下区」には計算式が設定してある
 シート[商品一覧]には 
  「分類表」「キッチンウェア」[バスグッズ]「テーブルウェア」「ユーティリティ」の5つのリストがある
 シート[売上表]の商品CDについては 次の内容を意味する



設問1 名前の定義

    

設問2 関数の設定指示

1. セル範囲 C7:C21 に 「商品分類」を求める
2. セル範囲 D7:D21 に 「商品名」を求める
3. セル範囲 E7:E21 に 「単価」を 求める
4. セル範囲 H7:H22 に 「値引き後」の金額が表示されるようにする

5. セル E24 に 売上の中で「値引き後」の金額が 30,000円以上の商品数を求める
6. セル E25 に 売上の中で「値引き後」の金額が 最も高い商品の「商品名」を求める
7. セル F25 に 売上の中で「値引き後」の金額が 最も高い商品の「商品分類」を求める
8. セル B4 に 本日の日付(作成日)を求めること
   次の例のように 関数を用いて表示すること
(例) 平成23年4月1日 : 売上日 : 平成23年04月01日(土)


表示形式設定

  セル E24 の表示形式を 「○商品」となるように 設定する
[ 2011年09月22日 11:18 ] カテゴリ:LibreOffice Calc | TB(0) | CM(0)

「INDEX 関数 その5」を VLOOKUP 関数で

INDEX 関数 その5 LibreOffice Calc で 複数の範囲から 抽出するを 行いました
まったく同じ 一覧から 抽出するのに VLOOKUP 関数を使っても 抽出できます

INDEX_5.ods 
 元は全く同じファイルです

最初に行うことは

VLOOKUP 関数で 利用するセル範囲に 名前を付ける

セル範囲 B3:E7 を選択 「大人」と名前を付ける

セル範囲 F3:I7 を選択 「子供」と名前を付ける


VLOOKUP 関数で 抽出する際の確認を行う
とりあえず 「大人」の場合で 検討する

VLOOKUP 関数なので 一覧表を 縦型の表と考える
  一番左にある列は 「出発地」が入力されている
  ですから 「検索値」は 「出発地」を 入力する I9 セル

行列は 「大人」の場合には 「大人」と「名前の定義」をしたセル範囲

インデックスは 「到着地」が 
   「東京」の場合には 2
   「名古屋」の場合は 3
   「大阪」の場合には 4
  このようにしたい
  行の中で 何番目かを求めるのは MATCH 関数
  そこで MATCH( 検索値,検査範囲,タイプ )  
  検索値 は 「到着地」が入力される I10
  検査範囲 は INDEX 関数のときとは違い 「東京」が 2 になるように
  B3:E3
  タイプは 完全一致の 0

MATCH(I10,B3:E3,0)

データ順序 完全一致なので 0

VLOOKUP 関数では
=VLOOKUP(I9,大人,MATCH(I10,B3:E3,0),0)



以上で 「大人」では 抽出できる
「子供」の場合は 第2引数(行列)を 「子供」に変えればよい

入力時 「大人」か?「子供」か? の区別は I11 セルに 入力される 「1」か「2」

「1」か「2」で 選択肢を変更する
いろいろな方法があるが 今回は CHOOSE 関数を利用する

CHOOSE 関数

番号で指定して選択肢からデータを選ぶ

インデックスを使い最大 30 個からなる値のリストから 1 つ返す


CHOOSE( インデックス,値1,値2,...)

インデックス(必須) 選択した値(1~30)のインデックス

値1(必須) インデックスで 選択する値


この関数を 使えば 一覧がない場合 関数内に 一覧を作成できる
大きな一覧表の場合は 別の箇所に 一覧表を作成した方が簡単ですが 
今回は 項目が2つの一覧なので 数式内で作成
インデックスは 「大人/子供」が入力される I11
値1 は 大人
値2 は 子供

CHOOSE(I11,大人,子供)

これを VLOOKUP 関数の第2引数(行列)に指定する

=VLOOKUP(I9,CHOOSE(I11,大人,子供),MATCH(I10,B3:E3,0),0)


注意
大人・子供 は 名前の定義なので 「"」で括ってはいけない

単独で CHOOSE 関数で 「=CHOOSE(I11,大人,子供)」 とすると エラーになります
大人・子供 は セル範囲を名前で定義してあるので 単独では表示できないからです
そこで 勘違いして 「=CHOOSE(I11,"大人","子供")」とした場合
そのセルには 文字列として 「大人」・「子供」が表示されます
そのまま VLOOKUP 関数に入れてしまうと
今度は セル範囲を入力する箇所に 文字列が入るので 
VLOOKUP 関数で エラーになります

関数は 単独で考えずに 総合的に考えて設定してください
[ 2011年09月21日 05:17 ] カテゴリ:LibreOffice Calc | TB(0) | CM(0)

参考資料 INDEX 関数

INDEX 関数の書式には、次の引数があります

範囲   必ず指定します
     1 つまたは複数のセルの参照を指定します

 範囲として複数選択された領域を指定する場合は
 範囲をかっこ () で囲み、複数選択を構成するそれぞれの領域を半角のコンマ (,) で区切ります

 範囲で指定した各領域が 1 行または 1 列である場合
 行番号または列番号はそれぞれ省略することができる
 たとえば、範囲が 1 行のみである場合は、INDEX(範囲,列番号) と指定できる


行番号 必ず指定します
     範囲の中にあり、セル参照を返すセルの行位置を数値で返します


列番号 省略可能です
     範囲の中にあり、セル参照を返すセルの列位置を数値で返します


領域番号 省略可能です
     "範囲" に 複数選択された領域を 指定した場合
     その中の 1 つの領域を 数値で指定します
     指定した領域の中から 行番号と列番号が 交差する点にあるセルの参照が返されます
     最初に選択または入力された領域の領域番号が 1 となり
     以下、2 番目の領域は 2、3 番目の領域は 3 と続きます
     領域番号を省略すると、1 であると見なされます

     たとえば、範囲として (A1:B4,D1:E4,G1:H4) のような複数選択領域が指定されている場合
     領域番号の 1 は A1:B4、領域番号の 2 は D1:E4、領域番号の 3 は G1:H4 となる


解説

INDEX 関数では、まず範囲と領域番号を使って特定の範囲が選択され
次に行番号と列番号から特定のセルが選択されます
行番号の 1 は選択された範囲の先頭行 (上端行) を指定し
列番号の 1 は選択された範囲の先頭列 (左端列) を指定します

INDEX 関数が返すセル参照は、行番号と列番号が交差する点にあるセルの参照となります

行番号または列番号に 0 (ゼロ) を指定すると、列または行全体の参照がそれぞれ返されます

行番号、列番号、領域番号には 範囲内にあるセルまたは領域を指定します

範囲外の対象を指定すると、エラー値 #REF! が返されます

行番号と列番号を省略すると、領域番号で指定されるセル範囲内の領域が返されます

INDEX 関数の計算結果はセル参照となり、他の数式はこれを通常のセル参照と同様に扱います

INDEX 関数の計算結果は、セル参照または値として使用されます
[ 2011年09月21日 00:59 ] カテゴリ:エクセル | TB(0) | CM(0)

INDEX 関数 その5 LibreOffice Calc

今回は 今まで関係ないと書いてきた 「範囲番号」を使うケース
INDEX_5.ods 

一番わかりやすい例です
料金表で 「大人」の料金表と 「子供」の料金表
出発地・目的地(到着地)そして 「大人」か「子供」で 決定する

つまり
 「大人」の場合は B3:E7 の料金表から
 「子供」の場合は F3:I7 の料金表から 運賃を求める
いつものごとく
日本語で入力する 出発地と到着地のセル I9 と I10 セルには

入力規則を設定しておく


大人/子供 の 「1」・「2」も 設定しておくほうが良い


あまり複雑に考えずに
最初は 「大人」だけで考えればよい

「大人」の場合
 INDEX 関数で
  範囲 データ部分だけなので C4:E7
  行  MATCH 関数を使用
    「出発地」が 入力される I9 セルを検索値
     検査範囲は 出発地が 入力されている B4:B7
     タイプ は 完全一致 なので 0
  列   MATCH 関数を使用
    「到着地」が 入力される I10 セルを検索値
     検査範囲は 到着地が 入力されている C3:E3
     タイプ は 完全一致 なので 0

ここで 行 と 列 で求めた数値は 
「子供」に関してもそのまま使用できる

表の形式が同じなので セルの位置は違うが
表での 位置に関しては 上から 何行目 左から何列目の数値に関しては同じ

子供の場合は INDEX 関数の 範囲が G4:I7 になる

INDEX 関数に 複数のセル範囲を 入力するには

範囲をかっこ () で囲み

複数選択を構成するそれぞれの領域を

半角のコンマ 「,」 で区切る

今回の場合

(C7:E10,G7:I10)


Excel での場合です LibreOffice Calc でも有効です  確定して 確認すると

(C7:E10~G7:I10) 

半角の「~」に変わっています

どちらの範囲から 検索するかを INDEX 関数の 第4引数(範囲番号)で 指定します

最初に入力された領域の領域番号が 1 となり
以下、2 番目の領域は 2、3 番目の領域は 3 となる
今回 領域は 2ヵ所なので 「1」か「2」が入る


I11 セルに入力されるのが 「大人」=1 「子供」=2
そのまま 利用できる 

結果 数式は
=INDEX((G4:H4,G4:I7),MATCH(I9,B4:B7,0),MATCH(I10,C3:E3,0),I11)


INDEX

範囲 (G4:H4,G4:I7)

行 MATCH(I9,B4:B7,0)

列 MATCH(I10,C3:E3,0)

範囲番号 I11


確定して後から見ると
=INDEX((G4:H4~G4:I7),MATCH(I9,B4:B7,0),MATCH(I10,C3:E3,0),I11)

[ 2011年09月21日 00:51 ] カテゴリ:LibreOffice Calc | TB(0) | CM(0)

INDEX 関数 その4 LibreOffice Calc

前回は MATCH 関数で タイプ 1 のケース

今回は タイプ -1 の ケース


タイプ -1 とは 

検索範囲を 降順(大→小)に 並べてある

検索値が見つからない場合 検索値より大きい最小の値を 返す


INDEX_3.ods 


上の画像の場合 「阿部」の点数「65」を 見た場合
検索値「65」より大きい=「69」「79」「100」 最小=「69」
同じ値は その行になるので 表にすると


D4 セルに 入る INDEX 関数

範囲 「査定」の結果が 入力されている G4:G7

行 MATCH 関数で 調べる

列 選択している範囲が 1列なので 必要ない

MATCH 関数

 検索値 C5

 検査範囲 F4:F7

 タイプ  -1

結果

  =INDEX(G4:G7,MATCH(C4,F4:F7,-1))



今回は 下へ オートフィルするので 参照の固定を行う

=INDEX($G$4:$G$7,MATCH(C4,$F$4:$F$7,-1))


[ 2011年09月20日 11:37 ] カテゴリ:LibreOffice Calc | TB(0) | CM(0)

INDEX 関数 その3 LibreOffice Calc

前回は INDEX + MATCH 関数の 基本パターン

今回は MATCH 関数で あいまい検索のケース

郵便や小包などの配送料金表のケース
INDEX_2.ods 

重さは 一定ではない どんな数値が入るか決まっていない
送り先は 地域ごとに分かれている
郵便などの配送料金表は およそこんな感じだと思います
※ 「重さ」欄は 数値で 表示形式で 「kg」を表示している
※ 0 kg というのは 0 kg 以上 2 kg 未満
   

C3 セルの 送り先の表示には 入力規則を設定して リストで入力するように設定する

今回は 行を求める MATCH 関数が 少し違う
前回は 完全一致でしたが 今回は あいまい検索になります
重さは 料金表に書かれている重量と全く同じということは少ない
1kg だったり 5.3kg だったりするでしょう

たとえば 「関東」で 「5.3kg」の荷物を送るとしたら
関東の列で 5 kg 以上 10 kg 未満の 行の ¥1,000 にならなければいけない

「重さ」の列は 昇順に並んでいます
「5.3kg」の場合 「5kg」の行を選択するようにする
検索する「5.3kg」=5.3 より小さくて 最大の値 5 を選択する

MATCH 関数
 検索値 は 重さを入力する C2
 検査範囲は 料金表の 重さの列 B8:B12
 タイプ(第3引数)を 1 にする
 ※ 検索値が見つからない場合 検索値未満で 最も大きい値を 返す

=MATCH(C2,B8:B12,1)


送り先の地域は 前回と同じ 完全一致 なので タイプは 0


検討が 完了したので 今回は直接作成する

1.C4 セルをクリック
2.数式バーの  をクリック
  関数ウィザードを起動
3.「関数」欄内をクリック(アクティブにする) 「I」「N」キーを続けて押す
  INDEX になる
  [次へ]クリック
4.範囲 の 右端にある  をクリック

5.セル範囲 C8:F12 を選択
  右端にある  をクリック
6.行の右にある  をクリック

7.「M」キーを押す
  MATCH になるので [次へ]クリック

8.検索値 に C2 を 指定
  検査範囲に B8:B12 を 指定
  タイプ に 1 を 指定
  「数式」欄の 「INDEX」内を クリックする

9.関数ウィザードが INDEX に戻る
  列の右にある  をクリック

10.「M」キーを押す
  MATCH になるので [次へ]クリック
  検索値 に C3 を 指定
  検査範囲に C7:F7 を 指定
  タイプ に 0 を 指定
  「数式の結果」「結果」欄を 一応 確認
  [OK]クリック


完了


「送り先」を 変更したり 「重さ」を 変更したりして 確認する
[ 2011年09月20日 10:31 ] カテゴリ:LibreOffice Calc | TB(0) | CM(0)

INDEX 関数 その2 LibreOffice Calc

INDEX + MATCH 関数

INDEX-MATCH関数0.ods 


一番使えそうなケース(運賃表)

どうするか?
出発地を表示するセルを作成
目的地を表示するセルを作成
そして そのセルに 表示された地名が その表の何番目にあたるかを 表示する
この様な表を 作成する


H2 セルには 「鹿児島」「宮崎」「熊本」「福岡」と 入力できるようにする
H3 セルには 「東京」「名古屋」「大阪」と 入力できるようにする

この様な 検索で 日本語を対象とする場合には 入力規則を利用する方が良い
※ 微妙な違いで エラーになってしまうことがある
1.H2 セルをクリック
2.[データ]→[入力規則]クリック

3.[データの入力規則]画面 [条件]タブ
  「入力値の種類」で 「セルの範囲」を選択

4.「ソース」欄の 右にある  (縮小) を クリック

5.セル範囲 B3:B6 を 選択

6.選択したら 右端の  をクリック
7.元の大きさに戻ったら [OK]クリック

8.H3 セルも同様の作業を行い ソース欄には C2:E2 セル範囲を選択する


仮に 「宮崎」・「大阪」にしておく


今回は INDEX + MATCH の 理解の為なので このような表を作成しています
MATCH 関数を使い 
* 「宮崎」が 表のデータ部分に置いて 上から何番目かを調べる
* 「大阪」が 表のデータ部分に置いて 左から何番目かを調べる

J2 セルに MATCH 関数を設定する

MATCH( 検索値,検査範囲,タイプ )

検査値は H2
検査範囲は B3:B6 出発地が入力されているセル範囲
タイプは 完全一致なので 0

=MATCH(H2,B3:B6,0)


J3 セルも 同様に
検査値は H3
検査範囲は C2:E2 目的地が入力されているセル範囲
タイプは 完全一致なので 0
MATCH 関数は 検査範囲が 行でも列でも 何番目かを求めてくれる

=MATCH(H3,C2:E2,0)



これで INDEX 関数で 必要な 行番号と列番号が求められた

INDEX( 範囲,行,列,範囲番号 )

範囲 は 運賃表のデータ(金額)の部分 C3:E6
行  は 今求めた J2
列  は 今求めた J3
範囲番号 は 今回のケースでは 省略
※ 範囲番号が必要なケースは 範囲が複数の場合 今回のように1つの範囲では必要ない

=INDEX(C3:E6,J2,J3)





以上が 考え方です

理解出来たら MATCH 関数を INDEX 関数の中へ 入れてしまう
J2 の代わりに MATCH(H2,B3:B6,0)
J3 の代わりに MATCH(H3,C2:E2,0)
結果

=INDEX(C3:E6,MATCH(H2,B3:B6,0),MATCH(H3,C2:E2,0))
[ 2011年09月20日 08:23 ] カテゴリ:LibreOffice Calc | TB(0) | CM(0)

INDEX 関数 その1 LibreOffice Calc

表があり *行目*列目のデータを取り出したい という時には INDEX 関数が使える

INDEX 関数

=INDEX( 範囲,行(位置),列(位置) )


※ 第4引数の 範囲番号は 別の機会に 通常は 省略

範囲内の指定した行、列のデータを取り出して表示する関数
参考 : INDEX_1.ods 
一番最初に 習う場合に出てくるのが 行位置が そのまま番号のケース
例えば 

この場合は 順位のセルには 数値が入力されていて 表示形式で 「位」が 設定されている
検索したい順位を入力するセルも同様で 「1~5」の数値を入力する
「賞金」が 入力されているのは 1列なので 列位置は 1
その場合 INDEX 関数では 
=INDEX( 賞金が入力されているセル範囲.検索したい順位を入力したセル,1)

=INDEX(C5:C9,B2,1)


賞金の列の 上からの位置がそのまま 順位
4 位 = 4 = 上から 4 番目



最初のケースの一覧表が 横になったケース
今度は 「賞金」が 横のセル範囲(1行)なので 行位置は 1
=INDEX( 賞金が入力されているセル範囲,1,検索したい順位を入力したセル)

=INDEX(C5:G5,1,B2)


賞金の行の 左からの位置がそのまま 順位
3 位 = 3 = 左から 3 番目


縦横ともに 数字を利用した 一覧表のケース

この場合には 
クラス名のセルは 数値 表示形式で「組」を 表示
学年名のセルは 数値 表示形式で 「年」を表示

=INDEX( 範囲,行位置,列位置 )


範囲は 担任の名前が入力されている C7:E10
項目行や項目列を含まない
行位置 は クラスを特定する C3 セル
列位置 は 学年を特定する C2 セル
結果 
=INDEX(C7:E10,C3,C2)


INDEX 関数では *行目 *列目と 縦横の位置が分かれば その値を 抽出できる
*番目という 位置がわからないと 抽出できない
今までのように 行や 列が 順番の数値ではないケースでは
何番目かを 求める MATCH 関数を利用して 位置を特定する


MATCH 関数

指定したデータが 範囲の中で 何番目に位置するかを求める関数

MATCH( 検索値,検査範囲,タイプ )


範囲は 列でも 行でも構わない
ただし 1列 または 1行 のみ
列・行に 関係無く 「検索値」が 何番目にあるかを表示する

タイプ(検索方法)には 「1」「0」「-1」を指定する

「0」 完全一致 タイプ=0の時だけ 正規表現が使えます
「1」 省略可能 範囲に一致するデータがない場合 
    検索条件以下の最後の値が 返される
    最初の検索範囲が 昇順で並べ替えられていると仮定される
「-1」は 検索条件以上の最初の値が 返される
    検索範囲が 降順で並べ替えられていると 仮定される


何行目にあるかを 検索

「野中 敏也」が 一覧の「氏名」列で 何行目かを検索する
行数が そのまま順位


何列目にあるかを 検索

「布施 秋絵」が 一覧の「氏名」行で 何列目かを検索する
列の位置が そのまま順位
[ 2011年09月20日 00:16 ] カテゴリ:LibreOffice Calc | TB(0) | CM(0)

VLOOKUP 関数 その8 LibreOffice Calc

2つの表を比べ追加されているデータを探す

VLOOKUP8.ods 

2つの表を比べ 片方には存在しないデータを探す
「2010年の名簿」「2011年の名簿」を比べ 
「2010年の名簿」にはないデータに 「新規会員」と表示する



こんなケースでも VLOOKUP 関数が使える

2011年の「氏名」( I5 )を 2010年の会員名簿( $C$5:$F$27 )から探す

VLOOKUP(I5,$C$5:$F$27,1,0)


この時に気を付けるのが 選択するセル範囲 B 列からではなく C 列から
検索するのが 「氏名」なので VLOOKUP では 「氏名」が 入力された列を 一番左にする

この数式を M 列に 入力し 下へ オートフィルした場合
2011年にはあり 2010年にはない「氏名」の行では エラーになる
 
ここで もし エラーの場合には 「新規会員」と表示 エラーではない場合 空欄と設定する

=IF(ISERROR(VLOOKUP(I5,$C$5:$C$27,1,0)),"新規会員","")


もう慣れたと思います
IF 関数と ISERROR 関数の組み合わせ
1.M5 セルをクリック
2.関数ウィザードを起動
  IF を選択
3.IF 関数 論理式の右  をクリック

4.関数一覧で「ISERROR」を選択 [次へ] クリック

5、ISERROR 関数 値の右  をクリック

6.関数一覧で 「VLOOKUP」を選択 [次へ]クリック

7.VLOOKUP 関数 検索値 に I5
  行列 C5:F27 セル範囲
  インデックス ここは 「1~4」のいくつでも良い
  ※ 今回の VLOOKUP 関数の目的は エラーになるか 値を求める事が出来るか
  データ順序 完全一致 = 0
 入力が終わったら 「数式」欄の IFの「I」と「F」の間をクリック

8.関数ウィザードが IF に戻る
  TRUE の場合 「"新規会員"」 と入力
  FALSE の場合 「""」と入力
  [OK]クリック

9.数式バーで 絶対参照にしたいセル範囲 C5:F27 2つのセルにかかるようにドラッグ
 
10.[Shift]+[F4] キーを押す

  [Enter] キーを押し 確定する
11.下へ オートフィル


今の逆で 両方に名前がある人へ 「継続」と 表示したい場合は
IF 関数の TRUUE の場合に 「""」 FALSE の場合に 「"継続"」と入力すればよい

=IF(ISERROR(VLOOKUP(I5,$C$5:$C$27,1,0)),"","継続")


参考 : VLOOKUP8A.ods 
[ 2011年09月19日 00:45 ] カテゴリ:LibreOffice | TB(0) | CM(0)

VLOOKUP 関数 その7 LibreOffice Calc

売上一覧表から指定した商品のデータを取り出す

前回は 指定した月のデータ  でしたが 今回は 商品名で 検索抽出する
VLOOKUP7.ods 


やることは同じです 
1.O 列に 作業列を作成します
2.C1 セルに 入力した商品名と K4 セルの商品名が同じ場合に No を表示する

=IF($C$1=K4,I4,"")

  下へ オートフィルします
3.B 列に O 列へ 表示した No を 詰めて表示する
  B4 セルに いつものように SMALL 関数を設定する
  意味は O 列に表示してある値の X 番目に 小さい数字を表示
  X 番目を 一番上の B4 セルは 1番小さい
  次の B5 セルは 2番目に小さい B6 セルは 3番目に小さい
  このように 1行下がると 順位を指定する数値が 増える
  そこで B4 セルに ROW(A1) とする
  ROW(A1)は 1行目なので 1
  オートフィルで 下へ行くと A2 A3 A4 と順に増える
  A2 は 2行目なので 2 A3 は 3行目なので 3 ・・・


理解できたら B4 セルに

=SMALL($O$4:$O$106,ROW(A1))

  
下へ オートフィル すると 表示してある No がなくなると エラーになる
しかし 式は 入力しておかないと 検索値を 変更した際に 表示できない

そのために 数式が エラーの場合には 非表示という設定を行い
多めに設定しておく

=IF(ISERROR(SMALL($O$4:$O$106,ROW(A1))),"",SMALL($O$4:$O$106,ROW(A1)))

式が エラーならば 非表示 エラーでなければ SMALL 関数
IF 関数を 使用
そして
エラーか?エラーでないか? を 判断するのが ISERROR 関数
対象が エラー値なのか? どうかを調べる
ISERROR( テストの対象 )
テストの対象 : テストの対象となるデータを指定
戻り値 : テストの対象が エラー値であった場合 TRUE それ以外は FALSE

SMALL 関数が エラーの場合 TRUE を返す エラーでない場合が FALSE

Excel 2007 以降の場合には IFERROR 関数 という 関数が使える
これは IF 関数と ISERROR 関数が組み合わさったような関数
結果が エラー値の場合は 別の値を表示する
IFERROR( 値,エラーの場合の値 )

※ LibreOffice Calc では まだ使えない Excel 2003以前のバージョンも使えない

B4 セルに

=IF(ISERROR(SMALL($O$4:$O$106,ROW(A1))),"",SMALL($O$4:$O$106,ROW(A1)))
入力したら 下へ オートフィルする 少し 多めの範囲


今度は 日付列・商品名列・単価列・数量列・金額列を VLOOKUP 関数で 表示する

C4 セルに 

=VLOOKUP($B4,$I$4:$N$106,COLUMN(B1),0)

ここでも B4 セルを参照して 表示しているので
B4 セルが 空欄だった場合 空欄という式にする

=IF($B4="","",VLOOKUP($B4,$I$4:$N$106,COLUMN(B1),0))


 
右へ オートフィルする




ここで気が付くか? 「単価」「数量」「金額」の表示が ### になっている
これは Excel と違い オートフィルのパターン選択が出来ない
Excel の場合 「セルのコピー」「書式のみコピー」「書式なしコピー」が選択できる
LibreOffice Calc の場合は 書式も一緒に コピーしてしまう
そこで この段階で 「単価」「数量」「金額」の 下の方の書式をコピーし 貼り付ける
* セル範囲 D5:G5 を選択
* [標準]ツールバーの  (書式のコピーと貼り付け)をクリック
 
* D4:G4 を ドラッグ 書式を貼り付ける

* こうしてから 下へ オートフィルする



今回は 前回のように OFFSET 関数で 一気に 表示はできません
前回の場合は 表示したい範囲が つながっていたので
セル範囲を一つの塊として 縦・横の大きさを指定して 移動コピー出来ました
今回のセル範囲は とびとびなので そのような作業が無理です
もし やるならば 並べ替えを行い まとめてから移動コピーになります
その手間を考えると 今回は こっちの方が簡単です(と思う)

参考 : VLOOKUP7A.ods 
[ 2011年09月18日 22:27 ] カテゴリ:LibreOffice Calc | TB(0) | CM(0)

VLOOKUP 関数 その6 LibreOffice Calc

売上一覧表から指定した月のデータを取り出す

VLOOKUP6.ods 

B1 セルへ 月の数値を入力すると
B4 セル以降へ その月のデータを表示するように設定する



最初にすることは 売上一覧に 作業列を作成し 
B1 セルに入力された月かどうかの判断を 表示する
日付の月なので MONTH(J4)=B1
そして 同じ月の場合 No を 表示する

=IF($B$1=MONTH(J4),I4,"")


B 列に O 列で表示された No だけを 表示する
順番に 表示したいので SMALL 関数を使い
一番上B4 セルが 一番小さい値
SMALL( O列の範囲,1 )下へ オートフィルしたいので
B4 セルで 1 B5セルで 2 にするために ROW()関数を使う
B4 セルに 
=SMALL($O$4:$O$106,ROW(A1))

このまま オートフィルすると エラーが表示されてしまう
そのために この数式がエラーの場合は 非表示 の設定する
=IF(ISERROR(SMALL($O$4:$O$106,ROW(A1))),"",SMALL($O$4:$O$106,ROW(A1)))

これで No が 表示されるので 後は VLOOKUP 関数で
完全一致 検索
検索値 B4
行列  I4:N106
インデックス 2
データ順序 0
これで 日付欄は よいのですが
今回の場合 「商品名」「単価」「数量」「金額」とあるので
出来れば COLUMN 関数を利用した方が オートフィルが楽
ここでは 2 を表示したいので COLUMN(B1)

結果
=VLOOKUP($B4,$I$4:$N$106,COLUMN(B1),0)


おっと B 列が 空欄の場合は 非表示も加えて
=IF($B4="","",VLOOKUP($B4,$I$4:$N$106,COLUMN(B1),0))

下・右へ オートフィル

これで いいでしょう


ちょっと VLOOKUP 関数から離れますが

このような つながっているセル範囲を

別の箇所へ 表示するには


OFFSET 関数というものが 使えます


OFFSET 関数
基準から指定の行列数を移動した位置のセルの値を返します
OFFSET( 基準,行数,列数,縦,横 )
 基準(必須) このセルまたはセル範囲から 行列数を数える
 行数(必須) 基準のセルから上下方向に数える行数
 列数(必須) 基準のセルから左右方向に数える列数
  縦 (任意) 求める範囲の行数
  横 (任意) 求めるセル範囲の行数


この関数を 利用するためには 
基準を I3 セルとした場合に 第2引数となる 
基準から 何行分下のセルから始めるか

第4引数となる 何行分 表示するか データの幅の数
第3引数は 同じ位置で選択するので 「0」
第5引数は すべての列を表示するので 表の列数 「6」

作業列で 表示する No が 今回の場合 基準となる I3 セルからの 数にあたる
邪魔にならない Q1 セルに =SMALL(O4:O106,1)

データの幅は データの数なので COUNT 関数で 作業列をカウントする
Q2 セルに =COUNT(O4:O106)

1.B4 セルをクリック
2.関数ウィザードを起動 OFFSET を選択
3.基準欄 I3 を 指定
4.行数欄 先ほどで作成した Q1 セルを指定
5.列数欄 列は移動しないので 「0」(ゼロ)指定
6. 縦 欄 先ほど作成した Q2 セルを指定
7. 横 欄 表の列数 「6」(半角)
8.関数ウィザードの左下にある 【 行列 】にチェックを入れる

このような 行列の関数では 「関数の結果」「結果」欄は #VALUE! ですが気にしない
[OK]クリック

一気に表示される 
数式内は 
{=OFFSET(I3,Q1,0,Q2,6)}



関数で 作成したので B2 の値を 「4」や「6」に変更すると
4月分 6月分に 自動的に変更される

参考 
  Q1 セルや Q2 セルを使いたくない場合は
  OFFSET 関数内の それぞれの引数欄に 直接 入力しても良い
  行数欄に SMALL(O4:O106,1)
   縦 欄に COUNT(O4:O106)
=OFFSET(I3,SMALL(O4:O106,1),0,COUNT(O4:O106),6)



参考 VLOOKUP6A.ods 
[ 2011年09月18日 06:07 ] カテゴリ:LibreOffice Calc | TB(0) | CM(0)

VLOOKUP 関数 その5 LibreOffice Calc

料金表のパターン

基準値が 昇順に並んでいるデータで 近似値を検索 表示する
VLOOKUP5.ods 

右側の「商品金額表」の意味ですが
購入金額が  0 以上 100,000 未満 の場合が 商品券 0
購入金額が 100,000 以上 200,000 未満 が 商品券 5,000
購入金額が 200,000 以上 300,000 未満 が 商品券 10,000
購入金額が 300,000 以上 500,000 未満 が 商品券 15,000
購入金額が 500,000 以上   商品券 25,000

これがわかっていない人も多い VLOOKUP 関数では **以上の部分が 検索参照列
その列を 昇順に並べる

一度 自分が 作成しようとしている表を検討して 作成すること

一番わかっていないのが 以上・以下・未満・より上・より下
言葉でわかりにくい場合は >=・<=・>・<・>・< 記号で 理解するようにする
その値を含むか? 含まないのか? 
VLOOKUP 関数を設定するのは あいまい検索の基本パターン

1.検索基準 は 検索値「購入金額」が入力されているセル C 列 C5
2.行列は 「商品金額表」のセル範囲 F5:G9  絶対参照にする癖を付ける
3.インデックスは 「商品券」が入力されている 2列目なので 2
4.データ順序 省略 でも良いのですが 
  入力することで 完全一致か あいまい検索かを 意識するように 「1」を入力する

=VLOOKUP(C5,$F$5:$G$9,2,1)



あいまい検索 料金表のパターンでは

検索する 料金表をどのように作るか

最重要ポイント


[ 2011年09月17日 21:04 ] カテゴリ:LibreOffice | TB(0) | CM(0)

VLOOKUP 関数 その4 LibreOffice Calc

前回のファイル と 似ている状況


VLOOKUP4.ods
 
コード表が 1つになっているケース

もう1つ 前回と違うのは 
J11:K12 の 「輸入菓子」は「1」 「キッチン」は「2」という表がある

最初にやることは 今回も同じ 入力規制

1.セル範囲 J11:J12 を コピー  ※「輸入菓子」「キッチン」をコピー
  セル範囲 B4:B10 を 選択
  [データ]→[入力規則]クリック
  [データの入力規則]画面が表示される
  [条件]タブ 「入力値の種類」で 「リスト」を選択
  「入力項目」欄に 貼り付け
  [OK]クリック
2.セル範囲 I4:I9 を コピー  ※コード番号「C001~C006」をコピー
  セル範囲 C4:C10 を選択
  [データ]→[入力規則]クリック
  [データの入力規則]画面が表示される
  [条件]タブ 「入力値の種類」で 「リスト」を選択
  「入力項目」欄に 貼り付け
  [OK]クリック


3.D4 セルに VLOOKUP 関数を設定するために検討する
  検索基準 は コード番号が入力されている C4 セル
  行列 は セル範囲 I4:M9
  インデックス は 今回のポイント
  商品名を 選択したい場合 「輸入菓子」ならば 2 「キッチン」ならば 4 を指定したい
  下に 「輸入菓子」=1 「キッチン」=2 という表がある
  ここでも VLOOKUP 関数を利用し B4 セルが 「輸入菓子」ならば 「1」が求められる
  求められた「1」を 2倍すれば 「2」 
  「キッチン」だった場合 VLOOKUP 関数で 「2」が求められ 2倍して「4」
  数式にすると VLOOKUP(B4,J11:K12,2,0)*2 セル参照を固定して
  VLOOKUP($B4,$J$11:$K$12,2,0)*2
  データ順序は コード検索 完全一致なので 0

結果 
=VLOOKUP($C4,$I$4:$M$9,VLOOKUP($B4,$J$11:$K$12,2,0)*2,0)

4.B 列・C 列共に入力していない場合は 表示しないという設定を 加える
  これも前回と一緒なので省略 結果だけ

=IF(OR($B4="",$C4=""),"",
VLOOKUP($C4,$I$4:$M$9,VLOOKUP($B4,$J$11:$K$12,2,0)*2,0))


5.「単価」は コード表で それぞれ「商品名」の隣なので 
VLOOKUP 関数の インデックスでも 「商品名」の場合に「1」を足せばよい

  D5 セルを 右へ オートフィルし インデックスの位置に「+1」を加える



=IF(OR($B4="",$C4=""),"",
VLOOKUP($C4,$I$4:$M$9,VLOOKUP($B4,$J$11:$K$12,2,0)*2+1,0))


確認する


参考 : VLOOKUP4_A.ods 
[ 2011年09月17日 18:28 ] カテゴリ:LibreOffice Calc | TB(0) | CM(0)
カウンター
検索フォーム
ブロとも申請フォーム
QRコード
QR