MW211 EXIT

devlog
Excel関数/掛け算の合計
2023年11月15日
┌──┬──┬──────┐
│単価│数量│    金額    │
├──┼──┼──────┤
│A1  │B1  │=A1*B1      │
├──┼──┼──────┤
│A2  │B2  │=A2*B2      │
├──┼──┼──────┤
│A3  │B3  │=A3*B3      │
└──┼──┼──────┤
      │合計│=SUM(C1:C3) │
      └──┴──────┘
上記のようなことを一発で行う方法。
┌──┬────────────┐
│合計│=SUMPRODUCT(A1:A3,B1:B3)│
└──┴────────────┘
分類:Excel
Excel/図形のサイズ変更
2021年11月09日
図形の境界線をつかんでサイズを変更する際に、
以下のキーを複合するといい感じにサイズが調整される。
┌──────┬──────────────────┐
│Alt         │セルの境界線に合わせてサイズ変更    │
├──────┼──────────────────┤
│Ctrl        │中心固定でサイズ変更                │
├──────┼──────────────────┤
│Shift       │縦横比率固定でサイズ変更            │
├──────┼──────────────────┤
│Ctrl+Shift │中心固定かつ縦横比率固定でサイズ変更│
└──────┴──────────────────┘
分類:Excel
Excel/数式で「0」が表示される問題対策
2021年11月08日
空欄セルを数式で指定すると「0」が表示される問題。
┌─┬──┬──┐  ┌─┬──┬──┐
│  │ A  │ B  │  │  │ A  │ B  │
├─┼──┼──┤→├─┼──┼──┤
│ 1│    │=A1 │  │ 1│    │   0│
└─┴──┴──┘  └─┴──┴──┘
解決方法は以下の通り。

(1) B1の書式が文字列の場合  →「0」と表示されてしまう
  ┌──┬───┐
  │数式│=A1&""│
  └──┴───┘
(2) B1の書式が数値の場合  →「0」と表示されてしまう
  ┌────────┬─┐
  │書式のユーザ定義│# │
  └────────┴─┘
  これだと数値「0」が表示できなくなるが、それが嫌なら(1)+右寄せか。

(3) B1の書式が日付の場合  →「1900/1/0」と表示されてしまう
  ┌────────┬──────┐
  │書式のユーザ定義│yyyy/m/d;;; │
  └────────┴──────┘
  日付の場合、(1)だと「2021/11/8」→「44508」のように
  シリアル値表示になってしまう。
  よって、日付については書式で回避するのがよいだろう。
  ちなみに「;」が3つ並んでいて、4つのブロックに分かれているが
  それぞれ以下の条件の場合という意味になる
    (左から1番目)正数の場合  →唯一表示する
    (左から2番目)負数の場合
    (左から3番目)ゼロの場合  →空欄なので「0」を抑止している
    (左から4番目)文字列の場合
分類:Excel
Excel関数/マトリックスLOOKUP
2021年06月08日
┌────────────────┐
│    A   B   C   D   E   F   G   │
│  ┌─┬─┬─┬─┬─┬─┬─┐│
│ 1│B │ 3│B3│  │  │  │  ││
│  ├─┼─┼─┼─┼─┼─┼─┤│
│ 2│  │  │  │  │A │B │C ││
│  ├─┼─┼─┼─┼─┼─┼─┤│
│ 3│  │  │  │ 1│A1│B1│C1││
│  ├─┼─┼─┼─┼─┼─┼─┤│
│ 4│  │  │  │ 2│A2│B2│C2││
│  ├─┼─┼─┼─┼─┼─┼─┤│
│ 5│  │  │  │ 3│A3│B3│C3││
│  └─┴─┴─┴─┴─┴─┴─┘│
└────────────────┘
「A1」に横軸、「B1」に縦軸の座標をぞれぞれ指定した場合に
「C1」に結果を出力する場合、「C1」以下の数式を設定する。
┌───────────────────────────┐
│=VLOOKUP(B1,D:G,MATCH(A1,D2:G2,0),FALSE)              │
└───────────────────────────┘
分類:Excel
Excel/検索機能(Ctrl+F)
2019年03月25日
Excelの検索機能(Ctrl+F)について、仕様をまとめてみた。
・初期セル(ActiveCell)の次から検索を始める
・途中該当があればそこで止まる
・一周し初期セルに戻ってきて、初期セルが該当すればそこで止まる
  初期セルが該当しなければ、該当なしメッセージを出力する

なお、該当するのが一つだけの場合は、再度検索してもうんともすんとも言わない
→一周して初期セル(該当セル)に戻るだけ
分類:Excel
Excel/条件付き書式の条件を満たす場合は停止
2019年02月18日
過去Excelとの互換性を維持するという観点から
条件付き書式の「条件を満たす場合は停止」を(どちらでもよい場合には)
チェックを入れておいた方がよい。

なお、「条件を満たす場合は停止」これは
IFでTHENの場合にRETURNで抜けるかという話らしい。
分類:Excel
Excel/条件付きセルの書式(2)
2019年02月12日
数式にてある列が特定の値の場合に、セルを着色したりする場合の条件式の記載の方法。

左端が「×」の場合という条件
┌──────────────────────────────────────┐
│=$A1=×"                                                                    │
└──────────────────────────────────────┘

左端が「×」か「△」の場合という条件
┌──────────────────────────────────────┐
│=OR($A1="×",$A1="△")                                                      │
└──────────────────────────────────────┘

一番左の「=」は気にせず、IF()の条件文(第一引数)を記載すればよいようだ。

ちなみに一番左の「=」を入力しないで入力すると、
以下みたいに勝手に解釈(変換)されて、意図した結果とならないので注意。
┌──────────────────────────────────────┐
│="$A1=""×"""                                                               │
└──────────────────────────────────────┘
分類:Excel
Excel/参照先シートの並び替えに連動させたい
2018年11月09日
Sheet2が以下の場合…
┌─┬─┐
│  │A │
├─┼─┤
│ 1│ a│
├─┼─┤
│ 2│ b│
└─┴─┘
Sheet1なんかに以下のような数式を設定する。
┌─┬─────┐  ┌─┬─┐
│  │    A     │  │  │A │
├─┼─────┤  ├─┼─┤
│ 1│=Sheet2!A1│→│ 1│ a│
├─┼─────┤  ├─┼─┤
│ 2│=Sheet2!A2│  │ 2│ b│
└─┴─────┘  └─┴─┘
そこで、Sheet2を並び替える。
┌─┬─┐
│  │A │
├─┼─┤
│ 1│ b│
├─┼─┤
│ 2│ a│
└─┴─┘
すると、Sheet1も連動して並び替えて欲しいところが
律儀に参照先を維持してくれるので数式が書き替わって結果的に並び替えが効かない。
┌─┬─────┐  ┌─┬─┐
│  │    A     │  │  │A │
├─┼─────┤  ├─┼─┤
│ 1│=Sheet2!A2│→│ 1│ a│
├─┼─────┤  ├─┼─┤
│ 2│=Sheet2!A1│  │ 2│ b│
└─┴─────┘  └─┴─┘
ではどうやって並び替えを反映させるか?
絶対参照の「$」は効かない(同じ結果になる)。

INDIRECT()関数を使えばよい。
┌─┬───────────┐  ┌─┬─┐
│  │          A           │  │  │A │
├─┼───────────┤  ├─┼─┤
│ 1│=INDIRECT("Sheet2!A1")│→│ 1│ a│
├─┼───────────┤  ├─┼─┤
│ 2│=INDIRECT("Sheet2!A2")│  │ 2│ b│
└─┴───────────┘  └─┴─┘
これならSheet2がどうなろうが(並び替えしようが)、参照先は固定される。

なお、「=INDIRECT(Sheet2!A1)」みたいに引数に参照先を直接指定してはダメ。
「"」で囲んで文字列扱いとして指定する必要がある。
分類:Excel
Excel/1900年02月29日
2018年10月27日
Excelの世界では、存在しないはずの「1900年02月29日」が存在する。

「4年に一度の閏年は、100年に一度閏年ではなくなる(但し400年に一度は閏年になる)」
という法則からすると、「2000年02月29日」は存在しても
「1900年02月29日」や「2100年02月29日」は存在しないことになる

ただ、競合するLotus1-2-3の仕様が「1900年02月29日」が存在するものだった(*1)ので
Excelもそれに合わせて今日に至っているという事情のようだ
*1:メモリ節約のため「100年に一度閏年ではなくなる」を端折ったとの話も

従って、シリアル値を日付に変換する場合、
基本的に「シリアル値1=1900年01月01日」から日数を加算していく形となるが
「1~59」の間はこれが遵守されるが、「シリアル値60=1900年02月29日」
「シリアル値61=1900年03月01日」なので、「61~」は前述の法則性から
敢えて一日引いてあげなければなない
┌───┬──────────────────────────────────┐
│ 1~59│1900年01月01日の(シリアル値 - 1)日後                                │
├───┼──────────────────────────────────┤
│60    │1900年02月29日(本来はありえない)                                    │
├───┼──────────────────────────────────┤
│61~  │1900年01月01日の(シリアル値 - 2)日後                                │
└───┴──────────────────────────────────┘

ちなみに、「2100年02月29日」は存在しない
┌──────────────────────────────────────┐
│・シリアル値73109=2100年02月28日                                           │
│・シリアル値73110=2100年03月01日                                           │
└──────────────────────────────────────┘
→あくまで「100年に一度閏年ではなくなる」を端折ったのではなく
  「1900年02月29日」だけ(前述の事情による)特例であるという扱いである
分類:Excel
Excel/セルの表示形式が勝手に変わる
2018年09月08日
セルの表示形式が「標準」のセルに、日付の文字列(例えば「2018/9/8」など)を
入力(コピペも)すると、表示形式が「日付」になってしまう。

つまり、例えば、実は数値「1」を入力すべき項目だった場合、
間違って日付を入力してしまうと、再度「1」を入力した瞬間
「1900/1/1」と表示されてしまう。

これを解決するには、表示形式を「標準」としないで
「数値」とかにちゃんと設定する方法がよいようだ。

特にセルの保護をして書式設定を直せないものについては予防しておくとよい。
分類:Excel
前へ 1 2 3 4 5 次へ