FC2ブログ

データベース 1 Spreadsheets2013

Spreadsheets では、
 データをデータベースとして使うことができます
 データベースとは、特定のルールで整理されたデータの集まりです
 データの中から特定のデータを抽出したり
 条件を指定して並べ替えたりするなど
 様々なデータベース機能があります


 データを管理するときに、「リスト」を使います
 「リスト」とは、各列に、「見出し」を付け
 「列見出し」の下に、同じ種類のデータを入力したもの


 リストの列を、フィールド
 リストの行を、レコード
 リストの列見出しを、フィールド名


201507170001.png

 使用ファイル: データベースNo1.et 

 重 要

 リスト範囲の自動認識
 リスの内の1つのセルをクリックするだけで
 リストの範囲が自動的に認識され、選択される


 リスト作成時
 * 1つのシートに複数のリストを作成しない
 * リストと他のデータの間に、空白列や空白行を少なくとも1つ挿入する
    隣接したセルに入力しない
 * フィールド名は、先頭行に作成する
 * セルの先頭に余分なスペースを挿入しない
 * 同じ列のセルには、同じ書式を設定する
 * どの行でも、同じ列に同じ種類の項目を入力する



※ Spreadsheetsは、
同じ表計算ソフトですが Excelではありません

  Excel には、出来ることが、Spreadsheets には、出来ないことがあります
  ホームページ に、「互換性満足度No.1」と書いてありますが、
  複数の互換オフィスソフトの互換性を比較する企画で、互換性満足度No.1を獲得した実績があるというだけです

  「グラフィカルな表現を保証するものではありません」とも 
   小さな文字で書いてあります
   グラフが その代表的な例です
   Excel で作成したちょっと複雑なものは表示できません
  完全な互換性が必要な方は、
Microsoft の同じバージョンの Excel を使うしかありません
  同じ Excel でも、バージョンによって違いがあります

Kingsoft Office で、
 このようなことが出来ないかとか
 こんな機能が欲しいとか
 ここが変だなど
 疑問・希望・質問がある方は、
 例:Spreadsheets
 各ソフトの右上の 201507170003.png (改善要望)をクリック


   201507170002.png

 すると、パソコンにメールソフトを設定してある方は、そのソフトが起動します
 例 WindowsLivemail2012
 201507170005.png

 「宛名」欄に、kso_jp_error_02@kingsoft.jp;
 「件名」欄に、Kingsoft Spreadsheets 2013改善要望(9.1.0.4917)
 
※ (9.1.0.4917)は、そのKingsoftOffice のバージョンです
   
 Webメールを使い、メールソフトを設定していない方は
 自分の使っているバージョンを確認し

 201507170006.png

 201507170007.png

 先ほどの「宛先」・「件名」を設定して
 内容を記入の上送信してください

 キングソフトオフィスサポートセンターから 回答が来ます
 

 
スポンサーサイト



複数の関数を組み合わせて使う 2 IF+VLOOKUP

今回は、
 ネストの関数を最初から作るケース


セルD8に IF関数とVLOOKUP関数を使った数式を作成する

まずは、確認
 D列で求めるのは、「単価」
 「単価」は、一覧表の3列目にある


1.セルD8をクリック
2. fx (関数の挿入)をクリック
3.IFを選択 [OK]クリック
4.「論理式」欄に、「B8=””」と入力する
5.「真の場合」欄に、「””」と入力する

  201507160011.png

6.「偽の場合」欄をクリック
  
「数式バー」の左側にある「関数ボックス」右端にある▼をクリックする

  201507160012.png

7.一覧に「VLOOKUP」があれば、クリックする

  201507160013.png

 ※ 無かった場合は、一番下の「その他の関数」をクリック
   [関数の挿入]画面が表示されるので
   「関数の分類」:「検索と参照」で選択する

8.[関数の引数]画面が、「VLOOKUP」に変化する
  「検査値」欄に、カーソルがあることを確認
  セルB8をクリックする


  201507160014.png

9.「データ範囲」欄 右端にある 201507160016.png をクリックする

  201507160015.png

10.セル範囲H8:J19をドラッグ選択する

  201507160017.png

11.[関数の引数]右端の 201507160018.png をクリックする

  201507160019.png

12.「データ範囲」にカーソルがあることを確認し、[F4]キーを押す
  H8:J19 が、$H$8:$J$19 に 変化します


  201507160020.png

13.「列番号」 欄に、「3」と入力
  「検索方法」欄に、「0」を入力
  [OK]クリック

  

  201507160021.png
  ※ 関数の場合は、TRUE→1 FALSE→0 で、代用できる
    正式ではない ですが、一般的に使われる裏ワザです

14.D15までオートフィルする

  201507160022.png

製品IDを選択してみる
 セルB9 は、H99A10 ・ セルB10に、H99A21

  201507160023.png

「数量」を入力する
 セルE8 に、30 ・セルE9 に、25 ・ セルE10に、75

  201507160024.png

とりあえず 今回の「関数」は、ここまで
次回からは、「データベースについて」の予定です


 

複数の関数を組み合わせて使う  If+VLOOKUP

関数の引数の中で、さらに関数を指定されていることを
「関数のネスト」といいます
 ※ Kingsoft Spreadsheets 2013では、ネスト出来るレベルは7までです
 ※ Microsoft Excel 2013 では、64レベル


数式が空白セルを参照する可能性がある
など
計算結果にエラー値が表示されることがある場合
IF関数とVLOOKUP関数を組み合わせて
エラー表示を回避します


セルC8に入力されている関数を
エラー値#N/Aが表示されないように
数式を変更する


1.セルC8をクリック
2.数式バーで、以下の部分を選択する
  VLOOKUP(B8,$H$8:$J$19,2,FALSE)

  ※ 「=」は、含めない

  201507160001.png

3.右クリック 「切り取り」をクリック

  201507160002.png

4. fx (関数の挿入)をクリック

  201507160003.png

5.IFを選択 [OK]クリック

  201507160004.png

6.「論理式」欄に、「B8=””」と入力する

  201507160005.png

7.「真の場合」欄に、「””」と入力する

  201507160006.png

8.「偽の場合」欄で、「貼り付け」を行う

  201507160007.png

9.[OK]をクリック

  201507160008.png

10.セルC15までオートフィルする

  201507160009.png

11.セルC9~C15に、#N/Aが表示されないことを確認する

  201507160010.png


 

VLOOKUP関数を使う 2-2

前回の結果

 201507120009.png

 製品ID(セルB8)が未入力なので、エラー値「#N/A」が表示されている

セルB8 に、「H99A16」と入力する
設定されている関数の結果が表示される


   201507120010.png

 注 意

「検査値」の「製品ID」に入力する内容が少しでも違うと
VLOOKUP関数では、エラー値(#N/A)が表示される
例えば
先ほどの「H99A16」のケースで
 「H19A16」のように全角・半角混合で入力した時
 「H99A16 」のようにスペースを入れてしまった時


  201507120011.png

  201507120012.png

下の例の場合 一見入力が間違っていないように思える

このような入力ミスによるエラーが起きないように
「製品ID」の入力欄(セル範囲 B8:B15)に、入力規則を設定する


1.セル範囲B8:B15を選択する

  201507120013.png

2.[データ]タブ 「入力規則」をクリック

  201507120014.png

3.[入力規則]画面
  [設定]タブ 「条件の設定」 「許可」で
  「すべての値」を 「リスト」に変更


   201507120015.png

4.ソースで、右端のアイコン 201507120016.png をクリック

  201507120017.png

5.一覧表で、「製品ID」のセル範囲(H8:H19)を選択

  201507120018.png

6.入力規則の右端のアイコンをクリック

  201507120019.png

7.【 □ セル内のドロップダウン】に、チェックを入れる

  201507120020.png

8.[OK]クリック

  201507120021.png

9.セルB8の横に ▼ が表示されている

  201507120022.png

10.[▼]をクリックすると、「製品ID」のリストが表示される

  201507120023.png

11.この一覧の「H99A16」をクリックする

  201507120024.png

12.C8に「製品名」が表示される

  201507120025.png

今回は、ここまで
「関数04.et」として保存する   



 

VLOOKUP関数を使う 2-1

完全一致検索のケース

使用ファイル: 関数03.et 

 201507120001.png


シート「請求書2」の表に
製品ID番号を入力し、製品名が表示されるように設定する


1.セルC8をクリック
  fx (関数の挿入)クリック

   201507120002.png

2.[関数の挿入]画面
  「関数の分類」「最近使用した関数」
  「関数の選択」欄に、VLOOKUP があれば選択

  無かった場合 「検索と参照」に変えて選択
  [OK]クリック

   201507120003.png

3.[関数の引数]画面
  「検査値」欄に、カーソルがあることを確認
  セルB8をクリックする


   201507120004.png

4.「データ範囲」欄をクリック

   201507120005.png

5.一覧表のデータ部分(セル範囲 H8:J19)をドラッグ選択する

   201507120006.png

6.[F4]キーを押す
  $H48:$J$16 に変化する ※ 絶対参照にする


   201507120007.png

7.「列番号」欄に、「2」と入力
  「検索方法」欄に、「FALSE」と入力 ※ 「0」(ゼロ)でも


注意
 結果が、#N/A ですが、これは「検索値」の B8セルが空欄であるため


  [OK]クリック

   201507120008.png

つづく


 

VLOOKUP関数を使う 1 近似値検索

VLOOKUP関数

引数の「検索方法」が、TRUE または省略するケース

どのようなケースで使うか
 * 荷物の重さに応じて 料金を決める
 * 距離に応じて 料金をきめるなど
 検索値で検索するものが、数値などで
 すべてを一覧表にすると表が大きくなりすぎる



 荷物の重さに応じた料金を求める

 重さが 「0kg以上0.5kg未満」は、Aランクで、300円
 重さが 「0.5kg以上1kg未満」は、Bランクで、500円
 重さが 「1kg以上3kg未満」は、Cランクで、800円
 重さが、「3kg以上5kg未満」は、Dランクで、1,000円
 重さが 「5kg以上10kg未満」は、Eランクで、1,500円
 重さが、「10kg以上」は、Fランクで、2,000円
 以上のような条件の場合
 次のような一覧表になります


   201507110000.png

調べたい重さを セルF4に入力すると
料金を セルG4に自動的に表示する

 
  本来は セルF4が空欄の場合 空欄というIF関数も組み合わせるのですが...
 
1.セルF4に「2.5」と入力する
2.セルG4をクリック
fx  「関数の挿入」をクリック

   201507110001.png

3.[関数の挿入]画面 「関数の分類」で▼をクリック
  「検索と参照」を選択する


   201507110002.png

4.一番下にある「VLOOKUP」を選択
  [OK]クリックする


   201507110003.png

5.[関数の引数]画面で、「検索値」欄にカーソルがあることを確認
  検索したいセルF4をクリックする


   201507110004.png

6.「データ範囲」欄をクリック
  カーソルが移動したことを確認


   201507110005.png

7.一覧表のデータの部分で、最初のセルB4をクリック
  データ部分最後(右下)のセルD9まで、ドラッグする
  選択されたセル範囲が点線で表示され
  6行*3列の意味の[ 6R × 3C ]も表示される


   201507110006.png

8.「データ範囲」欄に、B4:D9 と表示される
  今回はこのままでもよいのですが セル範囲を固定する場合
  カーソルが後ろにあることを確認 [F4]キーを押す


   201507110007.png

9.B4:D9 が $B$4:$D$9 になるセル範囲の絶対参照
  数式の入ったセルをオートフィルしてもデータ範囲が変動しない

   201507110008.png

10.「列番号」欄には、
   表示したい「料金」の列が3列目なので、「3」と入力する
  「検索方法」欄には、近似値検索の「TRUE」を入力する


   201507110009.png


検索方法を変えた場合

1.近似値検索なので、「検索方法」欄を省略しても OK

  201507110010.png

2.よく使う方法ですが 
  Excel で数式の場合 TRUE は、1 でも同じ意味になる

  ※ FALSEは、 0 です

  201507110011.png

3.間違えて FALSE(=完全一致検索) と入力した場合は
  完全一致する検索値(2.5)がないので #N/A というエラーになる


  201507110012.png



 

該当するデータを検索する

一覧表を参照してデータを取り出す場合
よく VLOOKUP関数が使われます

 
ID番号を入力して商品名や単価を表示したり
社員番号から入社年度や所属部署などを表示したりすることができます

VLOOKUP関数
 
「検索と参照」関数の1つです
指定した範囲の中から必要な情報を検索して取り出します

 
書式
 VLOOKUP(検索地,範囲,列番号,検索の型)
引数
 検索値には、表の左端の列から 検索する値を指定します
  検索値の含まれる行からデータが取り出されます
  検索値には、値、セル参照、文字列を指定します

 範囲には、目的のデータが含まれる表の範囲を絶対指定で指定します
  
 列番号には、引数 範囲に指定されている範囲内での列番号を指定します
  表の左端の列を列番号 1 として数えます

 検索の型には、TRUE または FALSE を指定します
  ※ 省略可能
 FALSE を指定すると 検索値と完全に一致する値だけが検索されます
 完全に一致する値が、複数ある場合
 最初に見つかった値が使用されます
 完全に一致する値が見つからない場合
 エラー値#N/Aが返されます
 ※ FALSE の代わりに 0 (ゼロ)でも可

 TRUE を指定するか 省略した場合
 検索値と完全に一致する値 または 近似値が返される
 完全に一致する値が見つからなかった場合
 検索値未満の最大値が使用されます
 重要
 検索の型に、TRUE または 省略の場合
 範囲の左端の列にある値を
 昇順に並べ替えておく必要があります


説明

範囲の左端の列にある文字列を検索するときは
範囲の左端の列にあるデータの先頭または末尾にスペースがなく
印刷されない文字が含まれていないことを確認してください
これらの状況に当てはまる場合
誤った値や予測しないデータが返されることがあります

数値や日付型の値を検索する場合は
範囲の左端の列にあるデータが、文字列として保存されていないことを確認してください

検索の型が、FALSEまたは 0 で検索値が文字列の場合
検索値で疑問符(?)またはアスタリスク(*)を
ワイルドカード文字として使用することができます
(?)は、任意の1文字を表し
(*)は、任意の文字列を表します
通常の文字として(?)や(*)を検索する場合は
前に(~)「チルダ」を付けます


次回は、使用例


 

IF関数を使う Kingsoft Spreadsheets 2013

IF関数を使う1つの例題

使用ファイル :  関数02.et 

数量が20個以上の場合は、金額から10%割引きします
IF関数を使って
数量が20個以上の場合は
割引率のセルに「10%」と表示する数式を設定する


201507100002.png

1.セルH8をクリック(選択)
2.fx
(関数の挿入)をクリック

   201507100003.png

3.[関数の挿入]画面が表示される
  「関数の分類」の▼をクリック
  一覧から「論理」を選択する


   201507100004.png

4.「関数の選択」欄の「 IF 」を選択
  [OK]をクリックする


   201507100005.png

5.[関数の引数]画面 
  「論理式」欄に、カーソルがあることを確認
   「 F8>=20 」と入力
  「真の場合」欄に、10% と 入力
  「偽の場合」欄に、"" と 入力
  [OK]クリック

  ※ 関数の引数に文字列を指定するには、
    文字列を半角ダブルクォーテーション(")で囲みます
    空白を返すには、半角ダブルクォーテーション2つ("")です

   201507100006.png

6.数式バーの数式を確認
  セルH8には、「10%」と表示される
  セルI8の表示が、「125,400」から「112,860」に変化する


201507100007.png


7.セルH8の数式をセルH15までコピーする
  セルH8 の右下にマウスを合わせ、形が 201507100009.png になったら
  セルH15までドラッグする


201507100008.png

8.「割引率」と「割引後の金額」が表示される

201507100010.png


※ 「関数03.et」という名前で保存する 次回使います


 

条件を設定して処理を分岐する IF関数

設定した条件を満たしているかどうかで、処理を分けたい場合

IF関数を使います

IF関数は、論理関数の1つです
設定する条件を 比較演算子を使った「論理式」で表します
条件を満たす場合を   「真の場合」
条件を満たさない場合を 「偽の場合」
それぞれの場合の処理を指定する


IF関数

書式
   IF(論理式,真の場合,偽の場合)
引数
   論理式 には、真または偽のどちらかに判定できる値または式を指定
   真の場合には、論理式を満たす場合に返す値を指定する
   偽の場合には、論理式を満たさない場合に返す値を指定する

使用例
  =IF(F8>=20,”達成”,”未達成”)
  (説明)
   F8の値が20以上の場合は、「達成」と表示
   そうでない場合は、「未達成」と表示する


  =IF(F8>=20,G8*0.9,G8)
  (説明)
   F8の値が20以上の場合は、G8の値に0.9を掛けた値を表示
   そうでない場合は、G8の値を表示する



比較演算子と使用例

201507100001.png


論理関数
 1.AND
  
すべての引数がTRUEのとき、TRUEを返す
  1つ以上の引数がFALSEのときFALSEを返す

 2.FALSE
  
論理値FALSEを返します ※引数はありません FALSE()

 3.IF
  
論理式の結果に応じて、指定された値を返します

 4.IFERROR
  
数式の結果がエラーの場合は指定した値を返し
   それ以外の場合は数式の結果を返します
  IFERROR(値,エラーの場合の値)

 5.NOT
  
引数の逆の値を返します
   ある値が特定の値と等しくないことを確認するときに使う
  ※ NOT(論理式)
   例:=NOT(1+1=2)→結果:FALSE =NOT(FALSE)→結果:TRUE

 6.OR
  
いずれかの引数がTRUEのときTRUEを返します
   引数がすべてFALSEである場合はFALSEを返す
  引数は、1~255個まで指定できる 

 7.TRUE
  
論理値 TRUE を返します ※引数はありません TRUE()


 ※ Kingsoft Spreadsheets では、以上の7つです
    ちなみに、Microsoft Excel では、現在9つ(プラス IFNA と XOR )



 

端数を処理する 3

ROUNDDOWN関数とROUNDUP関数

端数の切り捨て、切り上げを行うには
ROUNDDOWN関数、ROUNDUP関数を使います


 201507070017.png



関数以外の方法での端数処理

標示形式で
 小数点以下の桁数を指定して
 端数処理を行うことができます


  201507070022.png 小数点表示桁上げ

  201507070023.png 小数点表示桁下げ

  201507070020.png

※ この場合 端数は表示桁数の一桁下で四捨五入されます
  が、数値データそのものは変更されません
  見た目だけが変化しているので
  計算では、表示されている数値ではなく
  端数を含む数値が使用されます



ROUNDDOWN関数

機能概要
 数値を指定された桁数に切り捨てます。
書式
 ROUNDDOWN(数値,桁数)
 数値:切り捨ての対象となる実数値を指定します。
 桁数:切り捨てした後の桁数を数値で指定します。  

解説
 「数値」に 文字列を指定した場合、エラー値「#VALUE!」が返されます。
 「桁数」に 正数を指定すると小数点以下で切り捨てされます。
 「桁数」に 「0」を指定すると一の位で切り捨てされます。
 「桁数」に 負数を指定すると整数部分の指定した桁で切り捨てされます。


ROPUNDUP関数

機能概要
 数値を指定された桁数に切り上げます。

書式
 ROUNDUP(数値,桁数)
 数値:切り上げの対象となる実数値を指定します。
 桁数:切り上げした後の桁数を数値で指定します。

解説
 「数値」に 文字列を指定した場合、エラー値「#VALUE!」が返されます。
 「桁数」に 正の数を指定すると小数点以下の指定した桁数に切り上げられます。
 「桁数」に 「0」を指定すると「数値」に最も近い整数に切り上げられます。
 「桁数」に 負数を指定すると整数部分の指定した桁に切り上げられます。