カテゴリー「Calc・エクセル・表計算」の4件の記事

2020年1月27日 (月)

Calc・エクセル・表計算:ふりがな順位をつける

【Calc・エクセル・表計算テクニック】

 ★ ふりがなに順位をつける

名前などのふりがなで、ふりがな順の順位の値が欲しいときがある。

その場合、以下のようにcountif関数が使える。

Ca_001

昇順の場合は、

COUNTIF(カウント対象の範囲,"<="&基準対象のセル)

降順の場合は、

COUNTIF(カウント対象の範囲,">="&基準対象のセル)

とする。

なお、同姓同名等、同一名が出る可能性がある場合は、重複対処のための処理を忘れずに♪

 

 

〔関連ページ〕
 ・Calc・エクセル・表計算:複数のフラグの結合・抽出 
 ・Calc・エクセル・表計算:OFFSET関数 

 

 

 

 

 

 

2018年10月 9日 (火)

Calc・エクセル・表計算:OFFSET関数

【Calc・エクセル・表計算テクニック】
 
 ★ OFFSET関数の書式
 
 
ちょっと変わった関数、「OFFSET」。
戻り値は「範囲」を返します。
 
使い方は以下な感じ。
 
OFFSET(基準位置となるセル , 範囲先頭セル指定【行:基準から下へ何行か】, 範囲先頭セル指定【列:基準から右へ何行か】, 範囲の縦幅 , 範囲の横幅
  
011_03

で、これが何に約に立つかというと、SUM関数などを使用する際、範囲は通常、「D6:F10」のように固定して利用することが多いかと思いますが、OFFSET関数を利用すると、「範囲」を引数で「動的に」指定することが可能となります。
 
 
 
 
 
【外部サイト】
 ・OFFSET関数の便利な使い方 
 
 
 
 
〔表計算関連ページ〕
 ・Calc・エクセル・表計算:複数のフラグの結合・抽出 
 ・Calc・エクセル・表計算:10進数 ⇔ N進数 変換  
 
 
 
 
 
 
 
 
 

2018年9月23日 (日)

Calc・エクセル・表計算:10進数 ⇔ N進数 変換

【Calc・エクセル・表計算テクニック】
 
 ★ 10進数 ⇔ N進数 変換

000_

Calcやエクセルなどの表計算ソフトにおいて、10進数・2進数・8進数・16進数の相互変換は楽です。
 
というのもそれ専用の関数があるので。
 
002_1

10進数➔  2進数・・・DEC2BIN関数
10進数➔16進数・・・DEC2HEX関数
10進数➔  8進数・・・DEC2OCT関数

2進数➔10進数・・・BIN2DEC関数
2進数➔16進数・・・BIN2HEX関数
2進数➔  8進数・・・BIN2OCT関数

8進数➔10進数・・・OCT2DEC関数
8進数➔  2進数・・・OCT2BIN関数
8進数➔16進数・・・OCT2HEX関数

16進数➔10進数・・・HEX2DEC関数
16進数➔  2進数・・・HEX2BIN関数
16進数➔  8進数・・・HEX2OCT関数

しかし、それ以外のものに関しては専用の関数が無い(※※)ので、自分で変換処理を行う必要があります。

ということで、とりあえず計算原理表をば。

まず、理解しやすいシンプルな2進数から。

 
 

〔2進数 ➔ 10進数〕 
 
2進数の「10011」を、10進数の「19」に直す場合。
 
011_r
 
 
 
〔10進数 ➔ 2進数〕 
 
10進数の「19」を、2進数の「10011」に直す場合。
 
012_r_2
022_6  023_3  
 
 
2進数がわかれば3進数以降も原理は同じです。
 

(※)10進数のケースで考えれば、理解しやすいかもしやすいかもしれません。
061_2

 

 
〔3進数 ➔ 10進数〕 
 
3進数の「12021」を、10進数の「142」に直す場合。
 
031
 
 
 
〔10進数 ➔ 3進数〕 
 
10進数の「142」を、3進数の「12021」に直す場合。
 
032
 
さて、基本的に上記のよう計算を行えば変換はできますが、特定の桁の分だけ計算したい場合は以下のようにすると良いです。
 
 
〔10進数 ➔ N進数の特定の桁〕 
 
例えば、10進数の「628」は、3進数で「212021」であるが、4桁目の2を求めたい場合、次のような計算を行うと可能となる。
 
628÷(3^4)=628÷81=7・・・61 ←5桁目以上の排除〔MOD(628,POWER(3,4))=61
61÷{3^(4-1)}=61÷27=2・・・7  ←4桁目の抽出〔QUOTIENT(61 ,POWER(3,(4-1)))=2
 
042_1

035_
 
さらに配列を利用すれば、以下のような表が作成可能となる。
 
051_
 

【セルJ21の配列数式】
{=SUM(QUOTIENT(MOD($B21,J$3^{1,2,3,4}),J$3^({1,2,3,4}-1))*(10^({1,2,3,4}-1)))}

(終)
 
 
 
 
 
 

【おまけ】
10進数の「628」において、3進数「212021」の4桁目の2を求める際、次の計算でも求められる。
 628÷27=23・・・7
 23÷3=7・・・2 
こちらで実装した方が式は短くなる。

 

※※
実は『10進数 > N進数』に関しては『BASE関数』、『N進数 > 10進数』に関しては『DECIMAL関数』なるものが存在する。
しかし、表計算ソフトのバージョンが古い場合は存在しないケースがあるので注意が必要です。

 

 
 
〔関連ページ〕
 ・Calc・エクセル・表計算:複数のフラグの結合・抽出 
 ・Calc・エクセル・表計算:OFFSET関数 
 
 
 
【外部サイト・行列定数/配列定数】
 ・数式でインライン行列定数を使用する 
 ・配列数式のガイドラインと例 
 
 
 
 
 
 

 

Calc・エクセル・表計算:複数のフラグの結合・抽出

 
【Calc・エクセル・表計算テクニック】
 
 ★ 複数のフラグの結合・抽出


〔フラグの結合〕
 
002_

表計算にて、複数のフラグを1つにまとめて活用する方法をば。
考え方としてn進数の考え方を使用します。
上図の例はシンプルに2進数で扱っています。
 
例えば上図の場合、4つのフラグを扱ってますが、

2進数4桁目【十進数では8 (2の3乗)】を「性別」フラグ、
2進数3桁目【十進数では4 (2の2乗)】を「成人/未成年」フラグ、
2進数2桁目【十進数では2 (2の1乗)】を「ポイントカードの有無」フラグ、
2進数1桁目【十進数では1 (2の0乗)】を「会員」フラグ

としています。

 
「高野八郎」の場合、十進数においては

男なので「8
未成年なので「0
ポイントカード無しなので「0
会員なので「1

で、これをすべて足せば「9」となり、これが結合されたフラグとなります。

ちなみに十進数表記の「9」は、二進数表記では「1001」となります。
 
結合フラグ処理には、配列か、SUMPRODUCT関数を利用すれば楽に処理できるかと。SUMPRODUCT関数の使用例は上図を参考にしてみて下さい。
1,2,4,8・・・に関してはPOWER関数を利用すると良いかと。【aのb乗 ➔ POWER(a,b)】
 
SUMPRODUCT関数ってそもそも何?〜な方はマニュアル(F1キーでたぶん表示される)とかネットで調べて下さい。
 
011_1
 
 
 
 
 
〔フラグの抽出〕
 
021_2
 
結合されたフラグから、あるフラグの有無を確認したい場合、フラグの抽出をする必要があります。
 
理論的には、対象のフラグの桁がどうなっているかがわかれば、判定できます。
022_2
問題はどう実装するかですが、何パターンか考えられます。
 
【① 文字列処理による抽出】

(1)DEC2BIN関数で10進数を2進数表示化
(2)MID関数やRIGHT関数などで対象の桁を抽出

考え方としては易しいけれども、関数の組み方が意外に面倒。

特に(2)のところが無意味に複雑になったりする。
 
 
【② 10^n で割る】

(1)DEC2BIN関数で10進数を2進数表示化
(2)MOD(QUOTIENT(結合フラグ(2進数表示),10^(桁数-1) ),10) 

とすれば、目的のフラグが抽出できます。

 
DEC2BIN関数を使用して表示される数は、2進数表示ではあれど、10進数扱いとなるため、QUOTIENT関数で100で割れば、右2桁分を排除が可。後はMOD関数で1桁目をゲットすればOK。
 
こちらの方が①よりもだいぶスマートに組めるかと。
 
031_

が、しかし、実はDEC2BIN関数には弱点が存在します。
 
というのも、DEC2BIN関数512以上の数は2進数化できなかったりするんですよね。
(2進数で言うと9桁までが限界 ➔ つまり、9つのフラグしか結合できない。)
 
032_
 
 
ということで、DEC2BIN関数を使用しない方法をば。
 
【③ BITRSHIFTの利用】

(1)BITRSHIFT関数で、不要な桁分だけ、右シフトさせ、対象の桁を1桁目へ
(2)MOD関数で、1桁目の値を判別 

041

045_
 
ちなみに、BITRSHIFT関数で指定する結合フラグは2進数表示の方ではなく、10進数の方で。LibreOffice5 Calc ならば、2進数においての48桁分くらいまで多分対応可。(つまり48フラグの結合が可能。)
 

※1
2進数以外での結合フラグの場合、BITRSHIFT関数を利用したフラグの抽出は基本的にできないです。工夫次第で、4進数,8進数,16進数,32進数・・・2^n進数はできますが、ちょいと面倒になります。
また、下記の『発展編』でも状況は同様になります。
 
※2
フラグの抽出は他にも方法がありますが、「Calc・エクセル・表計算:10進数 ⇔ N進数 変換」に計算原理を含めた解説が既出なので割愛。こちらの方は、N進数対応です。

 
 

〔発展編:複数のフラグを同時に確認〕 
 
さて、先の手法であれば、1つのフラグ状態を抽出し、確認できますが、複数のフラグに該当するかを同時に確認することはできません。
(例えば「男性」で「ポイントカードを持っていない」人を選ぶなど、2つ以上のフラグの有無を同時確認する場合。)
 
ということで、おまけの「複数のフラグに合致するか」について。
 
 
【マスクの利用】
マスク処理というものを利用します。

(1)マスクするための値を準備
(2)BITAND関数で、ビットごとにAND演算を行いマスク処理
  ( BITAND(結合フラグ値,マスク値) とする。結合フラグとマスク値はどちらが先でも良い。)

051_r

052_
 
マスク処理後は、対象の桁のフラグの合成値のみを調べることが可能となります。
 
テクニカルな要素がありますが、これはこれで便利です。
参考までに。
 
 
060__2
 
 
 
〔関連ページ〕
 ・Calc・エクセル・表計算:10進数 ⇔ N進数 変換 
 ・Calc・エクセル・表計算:OFFSET関数 
 
 
 
 
【外部サイト】
 ・【5分で覚えるIT基礎の基礎】論理演算の種類と真理値表 
 ・【5分で覚えるIT基礎の基礎】AND,OR,XOR,NOTの意味と使い方の総整理 
 ・ビットとビット演算 
 ・整数型とビット操作 
 ・ビット演算 
 ・ビット演算 (bit 演算) の使い方を総特集! 〜 マスクビットから bit DP まで 〜 
 ・ビットを数える・探すアルゴリズム 
 
 
 
 
 
 

2021年5月
            1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30 31          
無料ブログはココログ