FC2ブログ

おジさん学習帳

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

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

DSUM 関数  LibreOffice Calc

データベース関数

DSUM 関数

「広尾店」の「金額」の合計を求める

データベース(必須)
  データのあるセル範囲
  ※ 各列の上端には 列見出しが付いていること
データベースフィールド(必須)
  検索条件をあてはめるデータベースフィールド(列)
  ※ 「列見出し」または「列番号」を指定
  ※ 文字列を「”」(ダブルクォーテーション)で囲んで指定 
    または 文字列が入力されたセルへの参照
  ※ 列番号は データベースの左端の列を 「1」とする整数で指定 
検索条件(必須)
  検索条件を含むセル範囲
  ※ 検索に必要な列見出しの下に1つ以上の条件を指定する

データベース関数は ほとんど同じ 引数のスタイル
求める結果が 違うだけ

ですから データベース関数を 使おうと思ったら
最初に 条件となる列見出しを作成し その下へ 条件を入力する
今回の場合 「広尾店」なので 「店舗名」フィールドを作成し
下のセルへ 「広尾店」と入力


=DSUM(B2:G1002,6,I2:I3)

=DSUM(B2:G1002,G2,I2:I3)

=DSUM(B2:G1002,"金額",I2:I3)


LibreOffice Calc の場合 数式内で セル範囲を選択するのが 面倒なので
出来れば セル範囲には 「名前の定義」を適用した方が 楽

今回の場合は セル範囲 B2:G1002 に 覚えやすい名前を付けると良い
例えば 「売上一覧」 など
「名前の定義」の仕方は 何度も書いたので 大丈夫かと思いますが
念のため もう一度
このデータの場合で
1.セル B2 をクリック
2.[Ctrl]+[Shift]+[→] キーを同時押し
3.[Ctrl]+[Shift]+[↓] キー同時押し
 これで セル範囲 B2:G1002 が選択できる
左上の[名前ボックス]を クリック
「売上一覧」と入力する
[Enter] キーを 2回 押す
1度は 文字変換を確定 2度目が 「名前定義」の確定
  
「名前定義」が出来たかどうかの確認は
[挿入]→[名前]→[指定] クリック
[名前の指定]画面が開く
「名前」が登録できていれば ここに入っている
その名前をクリックすると 下の「位置」欄に セル範囲が表示される
指定した位置とあっているのか 確認する


DCOUNT 関数や DCOUNTA 関数と同じく 条件が複数になった場合
条件となる列見出しを 複数作成し その下へ 条件を入力する場良いだけ

2005年5月分の 「広尾店」の売上合計を求める場合
「日付」フィールドで 2005/5/1 以上
同じく 「日付」フィールドで 2005/5/31 以下
「店舗」フィールドで 「広尾店」の条件を入力する
  
 I10:K11 セル範囲に入力した場合

=DSUM(売上一覧,G2,I10:K11)

=DSUM(売上一覧,6,I10:K11) ・ =DSUM(売上一覧,"金額",I10:K11)


2005年5月 広尾店の輸入菓子の金額合計
  
=DSUM(売上一覧,6,I18:L19)
=DSUM(売上一覧,"金額",I18:L19)
=DSUM(売上一覧,G2,I18:L19)

参考 : 830.ods 


SUMPRODUCT 関数で 計算した場合も載せたファイル
参考 : 830_2.xls 
これも Excel で開いた場合と Calc で開いた場合を見比べてください
スポンサーサイト



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

DCOUNT 関数 DCOUNTA 関数 に ついて

LibreOffice Calc

DCOUNT 関数 DCOUNTA 関数
DCOUNT 関数と DCOUNTA 関数を使えば 条件を満たす行をデータベースから探し出し 
指定した列(フィールド)にあるセルの個数を求められる
DCOUNT 関数は 数値の入ったセルの個数
DCOUNTA 関数は 空白以外のセルの個数


DCOUNT 関数
DCOUNT ( データベース,データベースフィールド,検索条件)
データ範囲内で、検索条件に合ったセルの個数を数えます


データベース(必須)
  データのあるセル範囲
  ※ 各列の上端には 列見出しが付いていること
データベースフィールド(必須)
  検索条件をあてはめるデータベースフィールド(列)
  ※ 「列見出し」または「列番号」を指定
  ※ 文字列を「”」(ダブルクォーテーション)で囲んで指定 
    または 文字列が入力されたセルへの参照
  ※ 列番号は データベースの左端の列を 「1」とする整数で指定 

検索条件(必須)
  検索条件を含むセル範囲
  ※ 検索に必要な列見出しの下に1つ以上の条件を指定する

エラー
[#VALUE!]
 「データベースフィールド」に 存在しない列見出しを指定
 「データベース」の範囲を超える数値を列番号として指定した場合
[#NAME?]
 「”」を付けずに 列見出しを指定した場合


データベース関数を 使用する場合は 

検索条件が必要です

検索に必要な 列見出し

そして その下へ 条件入力




下のような一覧表の場合 L830.ods 

「名前の定義」を設定済み

「法学部」は 何人か?
  データベース(必須)
  データベースフィールド(必須)
  検索条件(必須)
これらを確認する
  データベースは 一覧表(列見出しを含む)
   B2:F47 (「名前」を付けておく 「一覧表」)
  データベースフィールドは 「"学籍番号"」または 「1」(数値) または 「B2」(セル番地)
   理由は この一覧表内で 数値が入っている列は B 列だけ
   列見出しの場合が 「"学籍番号"」
   列番号 の場合が データベースの左端の列なので「1」
   文字列が入力されたセル参照で セル B2
  検索条件は 条件となる列見出しを作成しなけらばならない
   この場合 条件は 「法学部」なので 「法学部」が入力されている フィールド
   「学部名」(D2 セル)をコピー 一覧表から 1列離れた H2 セルへ 貼り付ける
   その下のセル H3 へ 条件である「法学部」を コピー&貼り付ける
   ※ 入力しても良いのですが まったく同じにするには コピー&ペーストの方が確実

1.表示するセル J3 セルをクリック
2. 数式バーの  をクリック
3.[関数ウィザード][関数]タブ 「分類項目」の「▼」をクリック
  「データベース」を選択
4.「関数」欄が データベース関数だけ表示される
  「DCOUNT」を選択 
  [次へ] クリック
5.「データベース」欄に 「一覧表」と 入力
  「データベースフィールド」欄 「1」と 入力
  「検索条件」欄 H2:H3 と 入力
  [OK] クリック
6.求められた
  数式バーでは =DCOUNT(一覧表,1,H2:H3)
  セル表示では 11



これを COUNTIF 関数で 求めた場合は
=COUNTIF(D2:D47,H3)


この場合は 数値の列があったので DCOUNT 関数を使用できたが
数値の列がない場合は DCOUNTA 関数を 利用する
例えば 「学籍番号」が 「A1001」のように 文字列になっている場合
=DCOUNT(一覧表,1,H2:H3)
では 数値に入ったデータは無いのだから 「0」になる
そこで DCOUNT を DCOUNTA に変更すると
DCOUNTA 関数は 空白以外のセルの個数を カウントするので 求められる


以上は 条件が 1つの場合でした 
この場合は COUNTIF 関数でも 求める事が出来る

条件が複数になった場合のセルの個数を数えることも可能です
条件の列見出しを増やし 条件を入力すればよい
 (例) 経済学部で ゴルフ部の女性 が何人か?
  
これが H5:J6 セル範囲に 入力された場合
 =DCOUNT(一覧表,1,H5:J6)
 ついでに SUMPRODUCT 関数の場合も
 =SUMPRODUCT(D3:D47=H6,E3:E47=I6,F3:F47=J6) ← Excel NG
 =SUMPRODUCT((D3:D47=H6)*(E3:E47=I6)*(F3:F47=J6)) → Excel OK

数値列が無い場合
 =DCOUNTA(一覧表2,1,H6:J7)

参考 : L830_2.ods 


条件が複数の セルの個数(データの個数)を求める場合は
SUMPRODUCT 関数 ・ DCOUNT 関数・DCOUNTA 関数を使えばよい
[ 2011年08月30日 12:58 ] カテゴリ:LibreOffice Calc | TB(0) | CM(0)

SUMPRODUCT 関数について その7

今までの復習 3

 条件が3つの場合のデータ件数を求める

 条件が3つの場合の合計を求める

 SUMPRODUCT_7.ods 
今回も 名前は付けておく
 
求めるものは

※ L2 セルには リストを設定してある 「4」「5」「6」  
※ 表示形式で 「月」を 表示させている

「4月」かつ「青山店」かつ「グミ」 の データ件数を 求める L5 セル
複数条件のデータの件数を求める場合
=SUMPRODUCT( (条件範囲1=条件値1)*(条件範囲2=条件値2)*(条件範囲3=条件値3))

つまり
=SUMPRODUCT((MONTH(日付)=4)*(商品名="グミ")*(店舗名="青山店"))
セル参照にすると
=SUMPRODUCT((MONTH(日付)=L2)*(商品名=K5)*(店舗名=L4))

オートフィルを 考えて それぞれの固定を確認する
 L2 このセルは 移動したら困るので 絶対参照 $L$2
 K5 このセルは 列を固定 行は相対参照 $K5
 L4 このセルは 行を固定 列は相対参照 L$4


下方向と 右方向へ オートフィルする



複数条件のデータの合計を求める場合
=SUMPRODUCT( (条件範囲1=条件値1)*(条件範囲2=条件値2)*(条件範囲3=条件値3),条件範囲)

つまり
=SUMPRODUCT((MONTH(日付)=4)*(商品名="グミ")*(店舗名="青山店"),金額)
セル参照にすると
=SUMPRODUCT((MONTH(日付)=L2)*(商品名=K5)*(店舗名=L4),金額)
同様に 参照を 検討し
=SUMPRODUCT((MONTH(日付)=$L$2)*(商品名=$K5)*(店舗名=L$4),金額)
そして 下方向・右方向へ オートフィル


L2 セルに リスト機能を設定してあるので 「」をクリック 「5」を選択すると
5月のデータに変化する

[ 2011年08月29日 16:26 ] カテゴリ:LibreOffice Calc | TB(0) | CM(0)

SUMPRODUCT 関数について その6

今までの復習 2

 条件が2つの場合のデータ件数を求める

 条件が2つの場合の合計を求める

 SUMPRODUCT_6.ods 
今回も 名前は付けておく
 
求めるものは
 
「青山店」かつ「グミ」を 表示する L3 セル
複数条件のデータの件数を求める場合
=SUMPRODUCT( (条件範囲1=条件値1)*(条件範囲2=条件値2))

つまり
L3 の場合
=SUMPRODUCT((店舗名="青山店")*(商品名="グミ"))
セル参照のほうが良いので
=SUMPRODUCT((店舗名=L2)*(商品名=K3))

オートフィルするために セル参照を検討する
店舗名に関しては 列が変更されたら 変動 行は 変動して欲しくない
つまり 店舗名は 行を固定
商品名は 列を固定
結果
=SUMPRODUCT((店舗名=L$2)*(商品名=$K3))
このようにする
L3 セルの右下に マウスポインタを合わせ + になったら ダブルクリック


そうしたら そのまま L14 の右下に マウスポインタを合わせ 右へ オートフィルする



このように 「名前」と 「参照の固定」を利用すると 数式の適用が簡単になる

複数条件のデータの合計を求める場合
=SUMPRODUCT( (条件範囲1=条件値1)*(条件範囲2=条件値2),条件範囲)

つまり
L3 の場合
=SUMPRODUCT((店舗名="青山店")*(商品名="グミ"),金額)
セル参照のほうが良いので
=SUMPRODUCT((店舗名=L2)*(商品名=K3),金額)
後は先ほどと同様
参照をそれぞれ固定して
=SUMPRODUCT((店舗名=L$2)*(商品名=$K3),金額)

そして 下方向・右方向へ オートフィルする

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

SUMPRODUCT 関数について その5

今までの復習

 条件が1つの場合のデータ件数を求める

 条件が1つの場合の合計を求める


SUMPRODUCT_5.ods 
データ量が多いので 「名前」を 付けておく
  

求めた数値を 表示する欄が このように設定してある場合
「名前」を利用していると オートフィルする際に
「名前」の範囲は 絶対参照にしなくても良いので 楽
  
L3 セル 「グミ」・「件数」は データの個数を求めるので
条件が 1つの場合 
=SUMPRODUCT(( 条件範囲=条件値 )*1)
 

この場合は
=SUMPRODUCT((商品名="グミ")*1) 
ですが オートフィルすることを考え
"グミ" を セル参照にする
=SUMPRODUCT((商品名=K3)*1)
こうすることにより 
オートフィルで 下へ持って行くと 参照セルが そのままずれるので 簡単になる
セルL3 の右下へ マウスポインタを合わせ + になったら ダブルクリック

M3 セル 「グミ」・「金額」は 合計を求めるので
条件が 1つの場合 
=SUMPRODUCT(( 条件範囲=条件値)*1 ,集計範囲 )

この場合
=SUMPRODUCT((商品名=K3)*1,金額)
そして 下へ オートフィル


ここで 最初から 
「金額」欄も 「件数」欄と同じような関数なので 利用しようと思った場合
"グミ" の参照 K3 を 右へ オートフィルしても良いように $K3 に しても良い
これは 「K」 (列名)に 「$」が付いている
列固定 にしてある
列固定 なので 右に移動しようが 左へ オートフィルしようが 列参照 が 変わらない
数式バーで 「K3」を選択 [Shift]+[F4] を 3回 押す でも
直接 「$」(半角)を 入力しても大丈夫

=SUMPRODUCT((商品名=$K3)*1)
となった L3 セルを 右へ オートフィルすると
M3 セルは
=SUMPRODUCT((商品名=$K3)*1)
と全く同じになる
  
この状態で 数式バーの  (関数ウィザード)をクリック
 
[関数ウィザード]が 表示される

これで 「行列2」欄に 「金額」と 入力  「数式の結果」「結果」欄に 数値が表示される
[OK] クリック
 
でも 良いし
数式に慣れてきたら 直接 数式バーに 入力しても良い
※ 半角入力に 十分注意する

そして 下へ オートフィルする
$K3 と 列しか固定していないので 下へ オートフィルした場合は
参照セルは 行ごとに変化する K 列の 同じ行を参照する


LibreOffice Calc の場合は 「*1」は無くても 機能する
    =SUMPRODUCT(商品名=K3)
    =SUMPRODUCT(商品名=K3,金額)
 求めることが出来る

しかし LibreOffice Calc で作成し 
Microsoft Excel 97/2000/XP/2003 (.xls)(*.xls) で 保存する方もいますので
[ 2011年08月29日 14:26 ] カテゴリ:LibreOffice | TB(0) | CM(0)

SUMPRODUCT 関数について その4

SUMPRODUCT 関数

複数条件での データの個数や合計を 求めましたが
いままでに書いたのは 複数条件でしたが いわゆる AND 条件 だけでした

「5月」 かつ 「キッチン」 かつ 「成城店」 のような ケース

SUMPRODUCT 関数では OR 条件 についても 集計できます

例えば 
「広尾店」 または 「青山店」 の データ件数や金額の合計

「広尾店」でも「青山店」でも良い → 両方の合計
=SUMPRODUCT((“広尾店”=店舗名)+(“青山店”=店舗名))

以上は 同じフィールド(項目列)における OR 条件


違うフィールド(項目列)の場合は ただ単に 足すわけにはいかない
なぜならば 同じレコード(行)を 選択している場合があるから 
単純に足すと ダブって計算(カウント)する可能性がある
実例では 「店舗名」が 「広尾店」 または 「種別」が 「輸入雑貨」の データの個数といった場合
見えている範囲でも 19行目 No.17 2005/04/02 のレコードが 「広尾店」で「輸入雑貨」なので
「広尾店」で カウントした場合に 1 
「輸入雑貨」で カウントして 1 足すと 2 になってしまう
そこで ダブっている分 = 「広尾店」かつ「輸入雑貨」の件数を 引かなければならない
つまり 数式では
=SUMPRODUCT((店舗名="広尾店")+(種別="輸入菓子"))-SUMPRODUCT(店舗名="広尾店",種別="輸入菓子")

これで 間違ってはいない

でも もっと すっきりしたい場合


SIGN 関数というものを利用する方法もあります
 SIGN 関数は 
  正の値は 「1」 
  ゼロは 「0」 
  負の値は 「-1」にする関数
 これを利用すると
  「1」以上の値になっても すべて 「1」として計算してくれる
数式に入れると
=SUMPRODUCT(SIGN((店舗名="広尾店")+(種別="輸入菓子")))


以上が データの個数の求め方でした
 
合計の場合は この数式に もう1つ 引数として 加えればよい
「数量」の合計が欲しい場合は 「数量」のデータが入力されているセル範囲 H3:H1002
「金額」の合計が欲しい場合は 「合計」のデータが入力されているセル範囲 I3:I1002
まぁ 「名前」を付けたほうがいいので
「数量」合計の場合は
=SUMPRODUCT(SIGN((店舗名="広尾店")+(種別="輸入菓子")),数量)

「金額」合計の場合は
=SUMPRODUCT(SIGN((店舗名="広尾店")+(種別="輸入菓子")),金額)
[ 2011年08月29日 13:35 ] カテゴリ:LibreOffice Calc | TB(0) | CM(0)

SUMPRODUCT 関数について その3

LibreOffice Calc での SUMPRODUCT関数

SUMIF 関数 と SUMIFS 関数 の代用としての利用例

参考 : L828.ods 

このように データの量が多い場合は 「名前」を 使用する
  


「店舗名」が 「広尾店」の金額の合計を出したい場合
SUMIF 関数で =SUMIF(店舗名,"広尾店",金額)

SUMPRODUCT 関数で 計算できます
なぜならば D 列で 合っていれば TRUE =1 違えば FALSE =0
つまり 広尾店の場合は 1 掛ける金額にすれば 良いわけです
=SUMPRODUCT(店舗名="広尾店",金額)




次は 条件が複数になった場合
Excel 2007 以降のバージョンでは SUMIFS 関数が使えます
しかし Excel 2003 以前のバージョン および LibreOffice Calc では 利用できません
そこで こんな場合は 非常に役に立ちます

「店舗名」が「成城店」 「種別」が 「キッチン」の金額を 出したい場合
=SUMPRODUCT(店舗名="成城店",種別="キッチン",金額)

理屈は 今まででわかるとは思いますが もう一度
 D 列(店舗名)が 「成城店」かどうかで 「TRUE =1」か「FALSE =0」
 E 列( 種別 )が 「キッチン」かどうかで 「TRUE =1」か「FALSE =0」
 そして 金額
 それぞれの位置が同じ要素どうしを掛け さらにそれらをすべて合計する
=SUMPRODUCT(店舗名="成城店",種別="キッチン",金額)




おまけ

条件の中に 日付を利用したい場合
例えば 今回の 一覧表は 2005/04/01 から 2005/06/30 までです

条件
  2005年 5月分
  成城店
  キッチン
このような場合
=SUMPRODUCT(日付>=DATEVALUE("2005/5/1"),日付<=DATEVALUE("2005/5/31"),店舗名="成城店",種別="キッチン",金額)
 行列1 日付>=DATEVALUE("2005/5/1")   
2005/5/1 を シリアル値に変換 それ以上 つまり 5月1日以降
 行列2 日付<=DATEVALUE("2005/5/31")
2005/5/31 を シリアル値に変換 それ以下 つまり 5月31日以前

 行列3 店舗名="成城店"
 行列4 種別="キッチン"
 行列5 金額


補足 


5月を 指定するのに MONTH 関数を使う方法もあります

=SUMPRODUCT(MONTH(日付)=5,店舗名="成城店",種別="キッチン",金額)


MONTH 関数を使うと シリアル値から 「月」を表す数値を求めることが出来る
日付 の フィールドは シリアル値になっているので 4・5・6 になる
5 と 等しいとき つまり 5月が TRUE になる




今回は 途中で Excel のことを書きませんでした
というか 実は Excel のことを忘れていました
LibreOffice Calc で作成し Excel とファイル交換する方のために 補足です

前回も 書きましたが 
Calc では 有効でも Excel では無効になる場合があります
それではどんな場合が 駄目なのか?

条件になっているものは Excel では 掛け合わせる
例えば
=SUMPRODUCT(店舗名="広尾店",金額) 
この場合 「店舗名="広尾店"」これが 条件になっている 
ですから
Excelで 使用する場合は 掛け合わせる
ところが 条件が 1つなので 掛ける相手がない そこで 「1」を掛ける
「金額」は セルの内容なので そのまま
=SUMPRODUCT((店舗名="広尾店")*1,金額) となる

=SUMPRODUCT(店舗名="成城店",種別="キッチン",金額) 
この場合
 条件が 2つあります 
ですから 条件は 掛ける
=SUMPRODUCT((店舗名="成城店")*(種別="キッチン"),金額) 

参考ファイル : L828.xls 
このファイルを LibreOffice Calc と Microsoft Excel で 開き
比較・確認してください


次は データベース関数の予定です
COUNTIF ・ COUNTIFS ・ SUMIF ・ SUMIFS と同じ結果を求められる
[ 2011年08月29日 12:13 ] カテゴリ:LibreOffice Calc | TB(0) | CM(0)

SUMPRODUCT 関数について その2

LibreOffice Calc

COUNTIF ・ COUNTIFS の代用としての利用例

参考 : L827.ods 
このような一覧表があった場合
「男が何人か?」を求める場合
COUNTIF 関数で
=COUNTIF(E3:E47,"男") 

これを SUMPRODUCT 関数 でも 求める事が出来る
LibreOffice Calc の場合は =SUMPRODUCT(E3:E47="男") 
Excel でも有効にする場合 =SUMPRODUCT((E3:E47="男")*1)
これは 以前 説明したことがあったと思うが
選択したセル範囲を 1つ1つ 合っているか 違うかを判断し
合っている = TRUE = 1
違っている = FALSE = 0
それを 合計するので 合っているセルの数が 求める事が出来る


次は 条件が複数になった場合
 例 : 「経済学部」で「女」は 何人か?
=SUMPRODUCT((D3:D47="経済学部")*(E3:E47="女"))
または
=SUMPRODUCT(D3:D47="経済学部",E3:E47 ="女")
注意 : 下の式は LibreOffice Calc では 有効ですが Excel では 無効です
   Excel の場合は 上の式でのみ有効


 ※ 「経済学部」「女」以外は 半角で入力すること

 D3:D47="経済学部" で D 列(学部名)で 「経済学部」であるかどうかを調べています
 「経済学部」の場合 そのセルは TRUE=1 です 違う場合は FALSE=0
 E3:E47 ="女" では E 列(性別)で 「女」であるかどうかを調べます
 「女」の場合 そのセルは TRUE =1 違えば FALSE =0
 そして その結果を 掛け合わせるので 
 両方とも 合っていれば 1 違えば 0です
イメージ

これを利用すれば 条件が増えても対応できます
「法学部」で「男」で「ゴルフ部」の 人数を求める のように 条件が増えても
=SUMPRODUCT((D3:D47="法学部")*(E3:E47="男")*(F3:F47="ゴルフ") )
※ Excel でも 使える

=SUMPRODUCT(D3:D47="法学部",E3:E47="男",F3:F47="ゴルフ")
でも 大丈夫 (LibreOffice Calc でのみ有効)
っていうか こちらならば 関数ウィザードを使用して作成できる




注意点
  セル範囲が 同じ幅(高さ)であること
  入力する際には 基本 直接入力で行い 必要な箇所でのみ 日本語入力に変える

LibreOffice Calc では 関数ウィザード内で 範囲選択するのは 面倒なので
可能な限り 「名前の定義」(範囲に名前を付ける)を利用する方が良い


  Excel と ファイル交換する場合は 注意する
   下の参考ファイルを Excel と Calc で 開き比較してみるとわかる
   参考 :  L827.xls 
   Excel で使用できない場合の式では 値が 「0」になる
[ 2011年08月29日 10:21 ] カテゴリ:LibreOffice Calc | TB(0) | CM(0)

SUMPRODUCT 関数について その1

LibreOffice Calc

SUMPRODUCT 関数


 引数として指定した複数の[配列]について 
 各配列内での位置が同じ要素どうしを掛け さらにそれらをすべて合計する

引数

 配列 ・・・ 計算の対象とする値を含んだ配列定数 または セル範囲
 どの配列も 行×列の大きさが 同じである必要があります
 引数はカンマで区切って 30個まで
ポイント
 計算の対処となるのは 数値、論理値、文字列として入力された数字の配列定数か
 または これらの値を含むセル範囲 空白セルは 0 として計算されます

下の画像の場合

=SUMPRODUCT(D3:D26,E3:E26,F3:F26)

 第一引数=「行列1」 は D3:D26
 第二引数=「行列2」 は E3:E26
 第三引数=「行列3」 は F3:F26
各配列内での位置が同じ要素どうしを掛ける そして それを合計する
つまり
 行列1の一番上の D3 セル
 行列2の一番上 E3 セル
 行列3の一番上 F3 セルと掛ける
2番目は 2番目同士 3番目は3番目・・・以下同様 で 掛けた積を合計する

この場合は 行列は同じ表で 同じ行を選択してあるので 同じ行同士でかけ合わせる

セル範囲を選択する場合には 必ず 同じセル幅(高さ)にしなければエラーになる
※ 行範囲が違うと 掛ける対象がないので エラーになる

違う行(D3:D26 と E5:E28 など)でも 同じセル幅(高さ)ならば 使用できる


以上は セル範囲が 1列の場合でしたが
セル範囲が 複数列 表の場合も可能
同じ大きさのセル範囲の場合 同じ位置のセル同士を掛け合わせ それを合計する
この場合
B2 セルと B6 セルを掛け B3 セルは B7 セルと掛け・・・
  

 B2XB6+B3XB7+B4XB8 = 480
 C2XC6+C3XC7+C4XC8 = 2020
 D2XD6+D3XD7+D4XD8 = 5090
 480 + 2020 + 5090 = 7590
これを SUMPRODUCT 関数を使うと

=SUMPRODUCT(B2:D4,B6:D8)



以上は 通常の SUMPODUCT 関数の使い方です
次は COUNTIF COUNTIFS 関数の代わりに使用する場合
[ 2011年08月29日 08:41 ] カテゴリ:LibreOffice Calc | TB(0) | CM(0)

PRODUCT 関数とSUMPRODUCT 関数  Libreoffice Calc

PRODUCT 関数

 すべての数値を 掛け算した結果(積)を 求めることができる
 引数として指定したすべての「値」を 掛け算した結果(積)を 求める
引数について
値 ・・・ 掛け算をしたい値、または値が入力されているセル参照を指定
 「A1:A3」のようにセル範囲を指定することもできる
 引数は カンマで区切って 30個まで指定できる
ポイント
 計算の対象となるのは、数値、文字列として入力された数字、またはこれらの値を含むセル
 空白セルは 0 として計算される
エラー
 引数の個数が 30個を超えた場合 エラーになる


使用例
この場合の原価小計を求める  「価格」×「原価率」×「在庫数」=原価小計
G3 セルをクリック
関数ウィザードを表示し PRODUCT を選択
[次へ]クリック
「数値1」欄に D3:F3 と入力する セル範囲ドラッグでも 直接入力でも可
[OK]クリック
数式バー内が =PRODUCT(D3:F3) となり セル G3 は 5400 となる

このように 選択したセル範囲をすべて掛ける  SUM 関数の 掛け算版


SUMPRODUCT 関数

 複数の数値の組(配列)について 
対応する数値どうしの値の積を求め さらに それらをすべて合計する

 SUM + PRODUCT ※PRODUCT 関数で求めた値(積)を 合計(SUM関数)する
「価格」×「原価率」×「在庫数」を求め さらにそれらすべてを合計する
「原価合計」を表示したいセルをクリック
関数ウィザードを表示
SUMPRODUCT を選択
[次へ]クリック
「行列1」欄 「価格」の入力されているセル範囲を 指定する
「行列2」欄 「原価率」の入力されているセル範囲を 指定する
「行列3」欄 「在庫数」の入力されているセル範囲を 指定する
[OK]クリック
=SUMPRODUCT(D3:D26,E3:E26,F3:F26)

「SUMPRODUCT 関数について」は 続く
[ 2011年08月28日 08:13 ] カテゴリ:LibreOffice Calc | TB(0) | CM(0)

LL3  その8 LibreOffice Calc

 シート「抽出」に抽出されたデータの
キロ数の表示形式を 「987km」と表示されるようにする



簡単な 書式設定なので
1.セル D10 をクリック
  [Ctrl]+[Shift]+[↓] キーを押す  (セル範囲 D10:D24 が選択できる)
2.選択された青い範囲の上で 右クリック[セルの書式設定]選択
3.[セルのフォーマット]画面 [ 数 ]タブ
  「書式コード」欄 「Standard」の後ろをクリックする
  「 "km" 」と入力
  [OK] クリック

結果



その1 ・ その2 ・ その3 ・ その4 ・ その5 ・ その6 ・ その7 ・ その8


[ 2011年08月27日 20:36 ] カテゴリ:LibreOffice Calc | TB(0) | CM(0)

LL3  その7 LibreOffice Calc

  シート「抽出」のセル K9 に 抽出されたデータの中から
日本海に流入する川の数を 関数を用いて求める


問題を 確認する
 「日本海に流入する川の数」を 数える
 つまり シート「抽出」の一覧で 「流入する海」の列に 「日本海」が 何個あるかを数える 使える関数は 3つ 思いつく
  COUNTIF 関数
  SUMPRODUCT 関数
  DCOUNTA 関数

 COUNTIF( 範囲,条件 )
 指定の条件に合う引数の数を数えます
 範囲(必須) 条件を当てはめる範囲
 条件(必須) 条件が指定されているセル範囲

 SUMPRODUCT ((配列1=条件1)*(配列2=条件2))

 ※ 上手い表現ではないm(_ _)m ゴメン
 この使い方は 良く使います
  しかし あまり参考書に 書かれていないかもしれません
 TRUE か FALSE を求めると 論理値で TRUE =1 FALSE =0 です
 今回は 条件がひとつなので 図にすると 下の画像の感じ
    応用すれば 複数条件でも求めることが出来ます

 DCOUNTA( データベース,データベースフィールド,検索条件 )
 検索条件を満たす内容のデータ範囲を対象に 空白でないすべてのセルの数を数えます
 データベース(必須) データのあるセル範囲
 列見出しを含む
 データベースフィールド(必須) 検索条件を当てはめるデータベースフィールド(列)
   列見出しとして入力されている文字列 または リストの左端から数えた列番号
 検索条件(必須) 検索条件を含むセル範囲

   リストの列見出しと全く同じ文字列を入力したセルと
   そのフィールドを対象とした検索条件を入力するセル
   最低2つのセルを使用 必ず上下に並べて配置する

注意:この検索条件のセルは リストのセル範囲と 1行・1列以上の空白行・列を開ける




COUNTIF 関数の場合

1.K9 セルをクリック
2.数式バーの  をクリック
  COUNTIF を選択 [次へ]クリック
3.「範囲」欄 「流入する海」を表示しているセル範囲 F9:F24 を入力
4.「条件」欄 「日本海」と入力してある K8 を入力
  [OK] クリック


SUMPRODUCT 関数の場合

1.K9 セルをクリック
2. 数式バーの  をクリック
  SUMPRODUCT を選択 [次へ]クリック
3.「行列1」欄 F10:F24=K8 と 入力する
  [OK] クリック

何故か LibreOffice calc では このままでも 値が表示される
Excel の場合は このままでは駄目です

数式バーの「=SUMPRODUCT(F10:F24=K8)」で 
「 T 」と「( 」の間へ カーソルが入るように クリックする

  「(」を入力
  カーソルを後ろに持って行き 「 *1)」と入力
  「 =SUMPRODUCT((F10:F24=K8)*1) 」と する

DCOUNTA 関数の場合

1.K8 セルを コピーする
2.F4 セルに 貼り付ける
これが 検索条件範囲になる 
3.K9 セルをクリック
4.数式バーの  をクリック
  DCOUNTA を選択 [次へ]クリック
5.「データベース」欄 B9:G24 と 入力  
  「データベースフィールド」欄 「5」でも 文字列「 "流入する海" 」でも F3 や F9 でも OK
  「検索条件」欄 F3:F4 と 入力
  [OK] クリック

参考 : LL3_6.ods 


その1 ・ その2 ・ その3 ・ その4 ・ その5 ・ その6 ・ その7 ・ その8


[ 2011年08月27日 18:06 ] カテゴリ:LibreOffice Calc | TB(0) | CM(0)

LL3  その6 LibreOffice Calc

 シート「抽出」のセル J9 に 抽出されたデータの中から
長さが最長の川名を関数を用いて求める



問題を 確認する
「長さが最長の川名を関数を用いて」
「長さ」は 「キロ数」列に 数値で入力されている
「最長」ということは 数値が一番大きいということ → MAX 関数
しかし 一番長い長さを求めるのではなく その「川名」を求める
MAX 関数で求めたセルと同じ行の「川名」を表示させなければいけない
表が 一番左に列に 「キロ数」の場合は VLOOKUP 関数で良いのですが
今回の場合は 位置が違うので INDEX 関数を使用します
INDEX 関数
指定範囲内で指定したひとつのセルの値を返します
 範囲(必須)  複数範囲の参照
  行 (任意)  範囲内での 行番号
  列 (任意)  範囲内での 列番号
 範囲番号(任意) 複数の範囲を指定した場合 その中の1つを数値で指定

 注意点 INDEX 関数の場合の範囲は 項目行や項目列を含まないセル範囲

1.J9 セルをクリック
2. 数式バーの  をクリック
  INDEX を選択 [次へ]クリック
3.「範囲」欄に データの範囲(項目行を含まない) B10:G24 を入力
 ※ 必要な範囲だけ選択する人もいる 例: C10:D24
4.「 行 」欄では  を クリック
5.MATCH を 選択 [次へ]クリック
6.「検索値」欄では  を クリック
7.MAX を 選択
  「数値1」欄に 「キロ数」が 入力されている D10:D24 を 入力
  「数式」欄で 「MATCH」の中をクリックする
  数式の結果=MAX 関数の結果=367
  結果 最初の関数(INDEX 関数)つまりすべての最終結果
エラー:511  変数が足りない場合のコード番号
8.MATCH 関数のウィザードになる
  「検索範囲」欄に 「キロ数」が入力されている D10:D24 を入力
  「タイプ」欄は 完全一致なので 「0」を入力
  「数式」欄で 「INDEX」の中をクリックする
  数式の結果=MATCH 関数の結果=8
  結果 最初の関数(INDEX 関数)つまりすべての最終結果 #VALUE!
9.INDEX 関数のウィザードになる
  「 列 」欄に 「2」を入力する
  [OK] クリック
 ※ 何故「2」かというと 「範囲」に指定したのが 
 「地区」「川名」「キロ数」「流域の都市」「流入する海」「順位」なので
 求める「川名」は 2列目にあたる
 ですから 選択した「範囲」が違う人は 違う数値になる


結果




その1 ・ その2 ・ その3 ・ その4 ・ その5 ・ その6 ・ その7 ・ その8


[ 2011年08月26日 23:25 ] カテゴリ:LibreOffice Calc | TB(0) | CM(0)

LL3  その5 LibreOffice Calc

 シート「抽出」のセル I9 に 抽出されたデータの件数を 関数を用いて求める
  なお 「川名」の列を参照すること

参考 : LL3_4.ods 


問題を 確認する 
データの件数を 関数を用いて求める」なので 
件数を数える → カウントする → COUNT 関数?

残念ながら 

COUNT 関数は 数値のデータを数える
今回は 「「川名」の列を参照すること」という指示がある
つまり 数値ではなく 文字列を数えなければいけないので COUNTA 関数
COUNTA 関数は 空白でないセルの数を数える
入力されているものが 数値でも 文字列でも 空白でない限り数えてくれる


1.I9 セルをクリック
2. 数式バーの  をクリック
  COUNTA を選択 [次へ]クリック
3.「値1」欄に 「川名」の列である C10:C24 を 入力する
  [OK] クリック

結果



その1 ・ その2 ・ その3 ・ その4 ・ その5・ その6 ・ その7 ・ その8


[ 2011年08月26日 21:50 ] カテゴリ:LibreOffice | TB(0) | CM(0)

LL3  その4 LibreOffice Calc

  シート「川名一覧」より キロ数が 平均以上の川のデータを
シート「抽出」のセル B9 以降に抽出する
  フィルタのオプションを利用する場合は 「抽出条件」「抽出先」に関して 
シート内の任意の場所に設定 または シートに変更を加えても良い

参考 : LL3_3.ods 


問題を 確認する 
まず 「キロ数が 平均以上の川のデータ」
これを見て キロ数の平均を出さないといけないことがわかる
次は 「フィルタのオプションを利用する場合」
つまり 「フィルタオプション」でもできるという意味になる


1.空いている箇所のセル(どこでもいいのですが とりあえず シート「川名一覧」の I5 セル)
 ※ 「空いているセル」といった場合 選択する上での注意
   周りに使用しているセルがないこと   上下左右斜めすべての方向
   当然ですが 今後使用する可能性のない場所

2. 数式バーの  をクリック
  AVERAGE を選択 [次へ]クリック
3.「キロ数」が 入力されているセル範囲 D5:D39 を 「数値 1」に入力 [OK]クリック
4.求められた「171.4」を覚える


そうしたら 「フィルタオプション」
1.「川名一覧表」の中のセルをクリック どのセルでも構いません
2.[データ]→[フィルタ]→[標準フィルタ]クリック

この場合は C7 セルを 選択している
3.[標準フィルタ]が表示される
4.項目名欄「▼」をクリックし 「キロ数」を選択
  関係欄 「▼」クリック 「>=」を選択
   値 欄 先ほど覚えた「171.4」と入力する
  左下の[オプションを増やす]クリック
5.【フィルタ結果の貼り付け先】にチェックを入れる
6.右端の  をクリック
7.[標準フィルタ]が小さくなったら 抽出するシート「抽出」の B9 セルをクリック
  右端の  をクリック
8.[OK] クリック
9.シート「抽出」の B9 セル以降に抽出できた

参考 : LL3_4.ods 


Excel の場合は シート「抽出」の B3:G3 に用意してある項目の下へ
条件を入力して 『フィルタオプションの設定』で抽出しますが
LibreOffice Calc では この『標準フィルタ』で 条件を入力し
オプションで 抽出場所を指定します
 ちなみに 条件は ちょっと見ただけですが 8個設定できるようです


その1 ・ その2 ・ その3 ・ その4 ・ その5 ・ その6 ・ その7 ・ その8


[ 2011年08月26日 07:37 ] カテゴリ:LibreOffice | TB(0) | CM(0)

LL3  その3 LibreOffice Calc

 シート「川名一覧」のセル範囲 G5:G39 の「順位」を 
キロ数が 長い順(降順)に 関数を用いて求めなさい

参考 : LL3_2.ods 


問題を 確認する 
「順位」を 求める関数 なので 順位→ランク→RANK RANK 関数
RANK( 数値,データ,タイプ )
標本内の数値の順位を返します
 数値(必須) 範囲内での順位を調べる数値
 データ(必須) データが入力されているセル範囲
 タイプ(任意) 0または省略 : 降順 0以外の値 : 昇順



1.G5 セルを クリック
  数式バーの  をクリック
  関数欄内をクリックし [R]キーを押す
  RANK を選択 [次へ]クリック
2.「数値」欄 D5 となるようにする D5 セルをクリックでも 「D5」と入力しても良し
  「データ」欄 D5:D39 セル範囲を 選択
  「タイプ」欄は 降順なので 省略できる
  [OK]クリック
3.数式バーで 第2引数の D5:D39 を選択する
  [Shift]+[F4]キー同時押し
  
4.これで 範囲が絶対参照になったので 下へ オートフィルする
  G5 セルをクリック 右下へマウスポインタを合わせ 
  マウスポインタが  になったら ダブルクリック



参考 : LL3_3.ods 


その1 ・ その2 ・ その3 ・ その4 ・ その5 ・ その6 ・ その7 ・ その8


[ 2011年08月25日 21:54 ] カテゴリ:LibreOffice Calc | TB(0) | CM(0)

LL3  その2 LibreOffice Calc

 シート「川名一覧」 表の一番下へ 以下の2行目を 新規入力しなさい
  文字色などは問わない


参考 : LL3.ods 


単に 入力の問題なので 普通に入力するだけ

参考 : LL3_2.ods 



その1 ・ その2 ・ その3 ・ その4 ・ その5 ・ その6 ・ その7 ・ その8


[ 2011年08月25日 14:39 ] カテゴリ:LibreOffice | TB(0) | CM(0)

LL3  その1 LibreOffice Calc

データ処理 問題 パターン2

 与えられたデータをもとに、抽出、集計を行う
 今回の元データ 「抽出」シートとの2シート

「抽出」シート



「川名一覧」シート



参考 : LL3.ods 


 シート「川名一覧」 表の一番下へ 以下の2行目を 新規入力しなさい
  文字色などは問わない
 シート「川名一覧」のセル範囲 G5:G39 の「順位」を 
キロ数が 長い順(降順)に 関数を用いて求めなさい
 シート「川名一覧」より キロ数が 平均以上の川のデータを
シート「抽出」のセル B9 以降に抽出する
  フィルタのオプションを利用する場合は 「抽出条件」「抽出先」に関して 
シート内の任意の場所に設定 または シートに変更を加えても良い
 シート「抽出」のセル I9 に 抽出されたデータの件数を 関数を用いて求める
  なお 「川名」の列を参照すること
 シート「抽出」のセル J9 に 抽出されたデータの中から
長さが最長の川名を関数を用いて求める
 シート「抽出」のセル K9 に 抽出されたデータの中から
日本海に流入する川の数を関数を用いて求める
 シート「抽出」に抽出されたデータの キロ数の表示形式を 「987km」と表示されるようにする



その1 ・ その2 ・ その3 ・ その4 ・ その5 ・ その6 ・ その7 ・ その8


[ 2011年08月25日 13:10 ] カテゴリ:LibreOffice Calc | TB(0) | CM(0)

LL1  その8 LibreOffice Calc

 シート「測定結果」のセル範囲 G3:G35 の表示形式を 「156.5cm」 
  セル範囲 H3:H35 の表示形式を 「67.5kg」と表示されるようにする

参考 : LL2_5.ods 

最後の問題を 書いていなかった m(_ _)m ゴメン

簡単な 書式設定なので
1.セル G3 をクリック
  [Ctrl]+[Shift]+[↓] キーを押す  (セル範囲 G3:G35 が選択できる)
2.選択された青い範囲の上で 右クリック[セルの書式設定]選択

3.[セルのフォーマット]画面 [ 数 ]タブ
  「書式コード」欄 「Standard」の後ろをクリックする

4.カーソルが入ったら 「 "cm" 」(必ず 半角)と 入力する

「ユーザー定義」に変わる・プレビューが 「159.9cm」になる
  [OK] クリック

H 列( H3:H35 )も 同様に 「 "kg" 」(必ず 半角)を 入力する





その1 ・ その2 ・ その3 ・ その4 ・ その5 ・ その6 ・ その7 ・ その8


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

LL1  その7 LibreOffice Calc

 シート「測定結果」のセル範囲 J3:J35 の「診断」を 
  次の条件に従い 関数を用いて求める
  「体重」が「許容体重」の下限より小さい = 「細い」と表示
  「体重」が「許容体重」の上限より大きい = 「太い」と表示
  それ以外 = 「標準」と表示



問題を 確認する 
図(画像)にすると こうなる
こうなれば 簡単
もし 上限より大きければ 「太い」それ以外
下限より小さければ 「細い」 残りは 「標準」
そのまま IF 関数のネスト
IF(「上限」<H3,"太い",IF(「下限」>H3,"細い","標準"))
1つ目の IF 関数で 太い部分(図形でいうと一番右)は既に設定した
2つ目の IF 関数で 残った部分を 切り分ける
これで 「上限」と「下限」を数式にすればいい

あ~ 前回の最後 削除しないほうが良かった(^_-)-☆ ネッ!
削除してなければ コピーできたのに

でも I3 セルから コピーできるので 
一時的に どこかのセルに貼り付けてもいい

「上限」: ROUNDDOWN((G3/100)^2*24.2,1)
「下限」: ROUNDDOWN((G3/100)^2*19.8,1)


この場合 注意しなければいけないのは
「”」(半角)と「細い」「標準」「太い」の日本語入力の切り替え
「”」が全角にならないように 注意する
「<」「>」の向きは 落ち着いて考える あわてて間違えないようにする


1.I3 セルをクリック
2.数式バーで 「ROUNDDOWN((G3/100)^2*19.8,1)」を選択
3.[Ctrl]+[C] キー同時押し で コピーする
4.空いている L3 セルをクリック [Ctrl]+[V] キー同時押しで 貼り付け
5.I3 セルをクリック
  数式バーで 「ROUNDDOWN((G3/100)^2*24.2,1)」を選択
  [Ctrl]+[C] キー同時押し で コピーする
  [Enter] キーでセルを移動する 
 ※ 数式バーにカーソルがある場合 注意しないと数式内を編集してしまうことがある
6.J3 セルをクリック  で 関数ウィザードを起動
  IF を選択 [次へ]クリック
7.「論理式」欄に 先ほどコピーした 「ROUNDDOWN((G3/100)^2*24.2,1)」を 貼り付ける
8.「論理式」欄の一番後ろにカーソルを置き 「<」と入力
9.H3 セルをクリック または 「H3」と入力する
10.「TRUE の場合」欄に 「"太い"」と入力
  「FALSE の場合」欄に 仮に「0」と入力 [OK]  クリック
11.L3 セルをクリック 「ROUNDDOWN((G3/100)^2*19.8,1)」をコピー
12.J3 セルクリック
  数式バーの  をクリック
すると 既に 数式が入っている場合
  入っている数式の編集が出来る
13.「FALSE の場合」欄の「0」を削除し  を クリックする
14.IF を選択 [次へ]クリック
  「論理式」欄に 「ROUNDDOWN((G3/100)^2*19.8,1)」を 貼り付ける
15.「論理式」欄の一番後ろにカーソルを置き 「>」(必ず 半角)と入力
  H3 セルをクリック または 直接「H3」と入力する
16.「TRUE の場合」欄に 「"細い"」 「False の場合」欄に 「"標準"」と入力
  [OK] クリック
17.後は下へ オートフィルする




その1 ・ その2 ・ その3 ・ その4 ・ その5 ・ その6 ・ その7 ・ その8


[ 2011年08月24日 12:28 ] カテゴリ:LibreOffice Calc | TB(0) | CM(0)

LL1  その6 LibreOffice Calc

シート「測定結果」のセル範囲 I3:I35 の「許容体重」を 
  次の計算式を参考にして 関数を用いて求めなさい
  その際 小数点以下第2位以下を切り捨て
  「下限から上限」(例:52.1から63.7)と表示されるようにする

参考 : LL2_3.ods 


まずは 問題を検討する
「小数点以下第2位以下を切り捨て」は ROUNDDOWN 関数を使う
この辺の関数は 覚えるしかない 完璧に一字一句覚える必要はない
ROUND → 丸い → 端数が無い 通常四捨五入で ROUND 関数
切り上げは 上げる → UP ・ 切り捨て 下げる → DOWN
設定する位置は 第2引数で指定する 

ROUNDDOWN ( 数値,桁数 )
数値を切り捨てて指定の桁数にする

この関数はゼロに向かって切り捨てる
例 ゼロに向かってとは 「1.5」「-1.5」を桁数「0」で切り捨てた場合
ROUNDDOWN(1.5,0) と ROUNDDOWN(-1.5,0) の場合
数値を小さくするという意味ならば 「-1.5」は「-2」になるはずですが 
ゼロ「0」に向かってなので 「-1」になる

ROUND 関数に場合は 四捨五入なので 
=ROUND(1.5,0) は 「2」 =ROUND(-1.5,0) は 「-2」になる

桁数について ( ROUNDDOWN 関数の場合 )

桁数が 負の数の場合、関数は 10、100、1000 などに切り下げられます


次は
「下限から上限」(例:52.1から63.7)と表示されるようにする
下限を 数式で求め ROUNDDOWN 関数で丸める
上限を 数式で求め ROUNDDOWN 関数で丸める
そして 
それを 「&」(AND 演算子)  または CONCATENATE 関数を使用し 文字列を結合する

この様に複雑な場合(自信のある方は見なくて結構)
空いている箇所(セル)を利用して 一つ一つ処理しておく


1.空いているセル L3 セルをクリック
2.「下限」の数式を 入力する
  
  数式バーの  をクリック
  入力が 直接入力であることを確認し (G3/100)^2*19.8 と入力する
  ÷(割る)は 「/」
  べき算は ^(キャレット)そして二乗の「2」で 「^2」  ちなみに 3乗の場合は 「^3」
  ×(掛ける)は 「*」(アスタリスク)
  参考:平方根を求める場合は 「^(1/2)」 と後ろの数値を分数にする
     n乗根を求める場合 「^(1/n)」

3.M3 セルをクリック
  上限の数式を 入力する
  
  =(G3/100)^2*24.2
4.(G3/100)^2*24.2 部分をコピーする
   注意:「=」は 含まないようにする
  この場合の コピーの方法は [Ctrl]+[C] キー同時押しのコピーです
5.N3 セルをクリック 数式バーの  をクリック
  関数ウィザードで ROUNDDOWN を選択 [次へ]クリック
  「数値」欄へ 先ほどコピーした数式を貼り付ける
  「桁数」欄 「小数点以下第2位以下を切り捨て」なので 「1」を入力
  「数式の結果」「結果」欄を確認 [OK] クリック

6.ここまでで 下準備が出来たので L3 セルの「(G3/100)^2*19.8」を コピーしてから
  I3 セルをクリック
  数式バーの  をクリック
   関数ウィザードで ROUNDDOWN を選択 [次へ]クリック
  「数値」欄へ 先ほどコピーした数式を貼り付ける
  「桁数」欄 「1」を入力
  [OK] クリック
7.N3 セルの 「ROUNDDOWN((G3/100)^2*24.2,1)」をコピー
8.I3 セルをクリック
  数式バーの一番後ろにカーソルが入るようにクリックする
9.「&」(必ず 半角)を入力
  続けて 「"」(半角)・日本語入力に切り替えて「から」・「”」(半角)・「&」(半角)を入力する
  数式バーでは 基本 直接入力で 必要な時だけ 日本語入力にする

10.先ほどコピーした 「ROUNDDOWN((G3/100)^2*24.2,1)」を 貼り付ける

状況
11.下へ オートフィルする
  I3 セルをクリック 
  右下にマウスポインタを合わせ  の形になったら ダブルクリック
12.最後に 不要になった セル L3・M3・N3 を 削除する

もちろん 別のセルを使用せず 頭の中で出来る方は 直接 I3 セルに入力すれば良い



数式バー内での コピー&貼り付けは 

ショートカットを使う

[Ctrl]+[C]  [Ctrl]+[V]





その1 ・ その2 ・ その3 ・ その4 ・ その5 ・ その6 ・ その7 ・ その8

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

LL1  その5 LibreOffice Calc

 シート「測定結果」のセル範囲 F3:F35 の「部活動」を 関数を用いて表示
  「出席番号」を検索値として 名前付けされている「所属」を参照すること

参考 : LL2_2.ods 


検索値を参照する一覧表が 縦長ですから VLOOKUP 関数
1.F3 セルをクリック
2.数式バーの   をクリック
3.[関数ウィザード]画面で 「関数」欄をアクティブにし 「V」キーを押す
  「VLOOKUP」を選択 [次へ]クリック
4.[関数ウィザード]画面 「VLOOKUP」
  「検査基準」欄 B3 となるように B3 セルをクリック または 直接入力する
  「行列」欄 「所属」と入力
  「インデックス」欄 2列目なので 「2」(必ず半角)
  「データ順序」欄 完全一致なので 「0」(必ず半角)
  [数式の結果][結果]欄を確認 [OK] クリック

5.下へオートフィルする F3 セルの右下へマウスポインタを合わせ 十 になったら ダブルクリック





その1 ・ その2 ・ その3 ・ その4 ・ その5 ・ その6 ・ その7 ・ その8

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

LL1  その4 LibreOffice Calc

 シート「測定結果」のセル範囲 E3:E35 の「名」を 関数を用いて表示
  セル範囲 C3:C35 の「氏名」の姓と名の間には 全角のスペースが入っている



今回の場合は 2通りの考え方がある
右から選択する(数える)か 真ん中を選択(数える)か
つまり RIGHT 関数を使うか? MID 関数を使うか?
好みの問題だと思います
RIGHT 関数  RIGHT( 文字列,文字数 )
テキストの最後の文字(複数の文字も可)を返します

MID 関数  MID( 文字列,開始位置,文字数 )
文字列の特定部分を返します


どちらの場合も FIND 関数と LEN 関数を使用する
LEN 関数  LEN( 文字列 )
文字列の文字数またはバイト数を返します

RIGHT 関数の場合 右からの文字数を 入力しなければいけない
「名」の文字数は 文字列の全文字数から 全角スペースまでの文字数を引いた数


1.E3 セルをクリック
2.数式バーの   をクリック
3.RIGHT を選択 [次へ]クリック
4.「文字列」欄 C3 と入力する
5.全文字数は LEN 関数を使うので 「文字数」の右の  をクリック
6.LEN 関数では C3 を入力
したら RIGHT 関数へ戻る
7.RIGHT 関数に戻ったら 「 LEN(C3) 」を切り取る
8.そうしたら 再び 「文字数」の右の  をクリック
  FIND を選択する
9.前回と同じように 全角スペースを見つけるように
  「検索文字列」欄 「" "」・「対象」欄 「 C3 」と入力する
  出来たら RIGHT 関数へ戻る ※ RIGHT の文字列内をクリック
10.RIGHT 関数で 「文字数」欄の FIND 関数の前へカーソルが入るようにクリックする
11.カーソルが入ったら 先ほど切り取った LEN(C3) を 貼り付ける

「 LEN(C3)FIND(" ",C3) 」となるので 間に 「-」(半角)を入れる

「-」が入ると 「数式の結果」「結果」欄には 「太助」が 表示される
[OK] クリック

12.オートフィルする




その1 ・ その2 ・ その3 ・ その4 ・ その5 ・ その6 ・ その7 ・ その8

[ 2011年08月22日 03:21 ] カテゴリ:LibreOffice Calc | TB(0) | CM(0)

LL1  その3 LibreOffice Calc

 シート「測定結果」のセル範囲 D3:D35 の「姓」を 関数を用いて表示
  セル範囲 C3:C35 の「氏名」の姓と名の間には 全角のスペースが入っている



問題を読んですぐ何を使うかはわかると思いますが 検討し整理する
「氏名」の「姓」つまり 全角スペースよりも左側を 取り出す
文字列の左側を取り出す → LEFT 関数
LEFT ( 文字列,文字数 )
テキストの先頭の文字(複数の文字も可)を返します


文字数が 先頭から 全角スペースの前まで
全角スペースの位置を 見つける → FIND 関数
FIND ( 検索文字列,対象,開始位置 )
検索対象の文字列にある 特定の文字列の位置を返す
アルファベットの大文字と小文字が区別できます
 検索文字列(必須) : 検索する文字列
 対象    (必須) : 検査の対象となる文字列
 開始位置 (任意) : 文字列で検索する開始位置



1.D3 セルを選択
2.数式バーの   をクリック
  
3.[関数ウィザード]で 「LEFT」を選択 [次へ]クリック
4.「文字列」欄 C3 と なるようにする 直接入力でも セルをクリックしてもOK
  「文字数」欄に カーソルを移動し 「文字数」の右  をクリック
5.「関数」欄で 「FIND」を選択 [次へ]クリック
6.FIND の「検索文字列」欄に 全角スペース を入力する
  「"」(必ず半角)そしてスペースを入れるその後「"」(必ず半角)つまり「" "」と入力する
  「対象」欄には 「氏名」のセル C3 を入力
  [数式の結果]欄が 「3」と表示されている これはFIND 関数の結果
  [結果]欄は 「青木」と表示されている
  ここで あわてて 合っているからいるから [OK]クリックとしてはいけない
  最初に検討した 全角スペースの前まで なので 
  一度 LEFT 関数へ戻り FIND 関数で求めた数値から 「1」引かなければいけない
  [数式]欄で LEFT の文字列の中をクリック
7.LEFT 関数 「文字数」欄の FIND 関数の後ろをクリック
  「-1」と半角で入力する
  「数式の結果」「結果」欄を確認 [OK]クリック
7.下へ オートフィルする



その1 ・ その2 ・ その3 ・ その4 ・ その5 ・ その6 ・ その7 ・ その8


[ 2011年08月22日 02:21 ] カテゴリ:LibreOffice Calc | TB(0) | CM(0)

LL1  その2 LibreOffice Calc

参考 : LL2.ods 
 シート「部活動」のセル範囲 A2:B35 に「所属」という名前を設定する


これは 関数では よく使う 名前の定義
何故?よく使うか? ・・・→ セル範囲を固定しなくて済む
いちいちセル範囲をドラッグしなくても良いなど 利点が多い
自分で何か作成する場合も 利用できる場合は 利用した方が良い

1.シート「部活動」のセル A2 をクリック
2.[Ctrl]+[Shift]+[→] [Ctrl]+[Shift]+[↓] を押し セル範囲 A2:B35 を 選択
3.「名前ボックス」をクリック
4.「所属」と入力 [Enter] を2回押す
  
5.名前が定義できたかの確認は [挿入]→[名前]→[指定]クリック
6.[名前の指定]画面が表示される
  設定出来ている場合は 「所属」という項目がある
  「項目」をクリックすると 「位置」欄に 設定したセル範囲が表示される



その1 ・ その2 ・ その3 ・ その4 ・ その5 ・ その6 ・ その7 ・ その8


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

LL1  その1 LibreOffice Calc

関数問題 パターン2
今回の元データ 「測定結果」シートと「部活動」シートの2シート
測定結果シート
部活動シート
  
参考 : LL2.ods 
 シート「部活動」のセル範囲 A2:B35 に「所属」という名前を設定する
 シート「測定結果」のセル範囲 D3:D35 の「姓」を 関数を用いて表示
  セル範囲 C3:C35 の「氏名」の姓と名の間には 全角のスペースが入っている
  シート「測定結果」のセル範囲 E3:E35 の「名」を 関数を用いて表示
  セル範囲 C3:C35 の「氏名」の姓と名の間には 全角のスペースが入っている
 シート「測定結果」のセル範囲 F3:F35 の「部活動」を 関数を用いて表示
  「出席番号」を検索値として 名前付けされている「所属」を参照すること
 シート「測定結果」のセル範囲 I3:I35 の「許容体重」を 
  次の計算式を参考にして 関数を用いて求めなさい
  その際 小数点以下第2位以下を切り捨て
  「下限から上限」(例:52.1から63.7)と表示されるようにする

 シート「測定結果」のセル範囲 J3:J35 の「診断」を 
次の条件に従い 関数を用いて求める
  「体重」が「許容体重」の下限より小さい = 「細い」と表示
  「体重」が「許容体重」の上限より大きい = 「太い」と表示
  それ以外 = 「標準」と表示
 シート「測定結果」のセル範囲 G3:G35 の表示形式を 「156.5cm」 
  セル範囲 H3:H35 の表示形式を 「67.5kg」と表示されるようにする




その1 ・ その2 ・ その3 ・ その4 ・ その5 ・ その6 ・ その7 ・ その8

[ 2011年08月22日 00:32 ] カテゴリ:LibreOffice Calc | TB(0) | CM(0)

LibreOffice Calc で グラフを Excel 2003 の色と同じにしたい

グラフの色 設定 (Excel 2003の色と同じにしたい場合)

この一覧を参考に [ツール]→[オプション]
[ LibreOffice ]→[ 色 ] 
「名前」を 付ける 
「グラフ1」は既にあるので 別の名前 例:Excel1 や E1 など
「 R 」欄に 赤の数値
「 G 」欄に 緑の数値
「 B 」欄に 青の数値
入力する
[追加]クリック

これを 16回行う
一度 [OK]を クリックする

ここまで 色の登録


登録した色を グラフの際に利用するよう設定

[オプション]を開き [グラフ]→[デフォルトの色]
[グラフの色]欄で 「系列1」を選択
[標準色]欄で 先ほど 登録した「E1」を選択する

すると 系列1の 色が 変更される
この作業で 系列12 まで 変更する
[OK] クリック

普通に グラフを作成すると Excel 2003 の色のようになるはず


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

LK4   その1 LibreOffice Calc

載せるかどうか迷った グラフの問題 
基本的に LibreOffice Calc では 出来ませんが それなりに工夫して...
問題の表
偶数月における「鉱工業」「金属製品工業」を 表す縦棒グラフ
グラフ見本

出来ない点
1.データが 飛び飛びである
2.系列名が 1つのセルではなく 分かれていて その上 1つはセル結合されている
3.プロットエリアのグラデーションのパターンは Calc にはない
4.グラフエリアの角を丸くするのは 出来ない


でも できるだけ 近づけてみた

参考 : LK4.ods 
   シルバー.PNG ・ セーム皮.PNG

参考解答例 : LK4.A.ods 


重要参考データ

グラフの色 設定 (Excel 2003の色と同じにしたい場合)

この一覧を参考に [ツール]→[オプション]
[ LibreOffice ]→[ 色 ] 
「名前」を 付ける 
「グラフ1」は既にあるので 別の名前 例:Excel1 や E1 など
「 R 」欄に 赤の数値
「 G 」欄に 緑の数値
「 B 」欄に 青の数値
入力する
[追加]クリック

これを 16回行う
一度 [OK]を クリックする
そして 再び [オプション]を開き [グラフ]→[デフォルトの色]
[グラフの色]欄で 「系列1」を選択
[標準色]欄で 先ほど 登録した「E1」を選択する

すると 系列1の 色が 変更される
この作業で 系列12 まで 変更する
[OK] クリック

普通に グラフを作成すると Excel 2003 の色のようになるはず
[ 2011年08月21日 13:01 ] カテゴリ:LibreOffice | TB(0) | CM(0)

LK3.ods その7 LibreOffice Calc

 ゴールシーク を用いて
   セル E13 に 「純利益」を 3百万円にするために必要な「総売上額」を求める
   事前に セル範囲 E7:E10 をコピーし セル範囲 E13:E16 に貼り付けること



ゴールシーク
計算結果を先に決めて、その結果を得るために 元の数値をいくつにすればいいのかを求める
 [ツール] → [ゴールシーク] を選択
 数式が目標値になるような変数の値を求めるダイアログを開きます


最初は セル範囲 E7:E10 をコピーし セル範囲 E13:E16 に貼り付けます

目標は「純利益」(E16 セル)を 300万円にするので E16 セルをクリック
[ツール]→[ゴールシーク]クリック
  
[ゴールシーク]画面が表示される

数式が入っている 目標値の入っているセルが 数式セル
目標値欄は そのまま 目標値 「3000000」
変化させるセル 売上総額の E13 セル
これでいいはずですが 何故か? うまくいかない


そこで Excel で確認してみた
すると 「数値が入力されているセルを指定してください」という注意文が表示された
つまり
E16 セルは 数式が入っているセルなので 
変化させるセルは 数式ではなく数値になっていないといけないようです 
そこで 貼り付ける際に E13 セルは 形式を選択して貼り付けで 「値」にしてみた
Excel では 無事 計算できた
では LibreOffice Calc では

「ゴールシークの完了」のダイアログボックスが表示

[はい]クリック

表示されたが 金額なので 表示は整数に直す



 シート「第4四半期予測」とシート「売上合計」の 
金額を表すデータの表示形式を 通貨スタイルの「¥」に設定する


シート「第4四半期予測」の C3:C6 セル範囲を選択 [書式設定]ツールバーの  をクリック

あとは 金額を表すデータを選択し同様の操作

シート「売上合計」でも同様にする
例えば 「定価」の列を選択する場合は E5 セルをクリック [Ctrl]+[Shift]+[↓]キー 同時押しで 一気に選択できる

以上


その1 ・ その2 ・ その3 ・ その4 ・ その5 ・ その6 ・ その7

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

LK3.ods その6 LibreOffice Calc

 シート「第4四半期予測」のセル E7 に 「総売上額」を関数を用いて求める
  セル E9 の「割引額」・セル E10 の「純利益」を次の計算式で求める
値引額 = 総売上額 × 割引率
純利益 = 総売上額 - 値引額

参考 : LK3_5.ods    ・ LK3_5_2.ods  


1.「総売上額」を関数を用いてって 単に SUM 関数なので
  E7 セルを アクティブ(クリック)にして 数式バーの Σ(合計) をクリック
  =SUM(E3:E6) [Enter] 押す
2.値引額は 問題にある 「値引額=総売上額×割引率」 を数式にするだけ
  E9 セルをアクティブにし 数式バーの(関数)をクリック

 数式バーに「=」が入り その後ろのカーソルがある状態で 
 「総売上額」のセル E7 をクリック
 数式バーで E7 の後ろをクリックし カーソルが後ろに入った状態で
 「*」(アスタリスク 半角)を入力
  
 「=E7*」の後ろにカーソルがある状態で 「値引き率」のセル E8 をクリックする

 =E7*E8  [Enter] 押す
3.純利益は 「純利益=総売上額-値引額」を 数式にする
  =E7-E9 になるようにする 直接入力でも構わないが 半角になるように
  直接入力で行う方が良い


参考 : LK3_6.ods 
その1 ・ その2 ・ その3 ・ その4 ・ その5 ・ その6 ・ その7


[ 2011年08月21日 03:04 ] カテゴリ:LibreOffice | TB(0) | CM(0)
カウンター
検索フォーム
ブロとも申請フォーム
QRコード
QR