仕事メモとか

仕事中に調べた情報とか知ったことをメモしています。
unixコマンド, vim, oracle, putty, postgresql, bash, EXCEL, python, SQL全般 など。
最近は tableau, movabletype とかも触ったりしています。
雑な読書感想とかはこちら

カテゴリ: excel


EXCELで列名のアルファベットをオートフィルでつけたい

はい、EXCELで列名を見ているとアルファベットなのですが、
システムで取り込むときに、そのアルファベット自体が欲しい、っていうことがあります。
ただ、オートフィルはアルファベット自体には対応していないので面倒だし、
AA,ABみたいなものに対応させるのはさらに面倒。

ということでaddress関数から列情報自体を持ってきて加工、という手法をとります。

=SUBSTITUTE(ADDRESS(1,ROW(),4),1,"")

これをA1セルに入れて、オートフィル(またはコピペ)で下にコピーしていくと、
アルファベットが連番で出来上がります。

内部的に見てみると、

ADDRESS(1,ROW(),4)

今のセルの行番号を列に変換し「A1」「B1」のような文字に変換

SUBSTITUTE(xxx,1,"")

後ろの「1」を置換して削除

これでアルファベットの連番を作ることが可能です。



参考:
https://kokodane.com/2013_kan_046.htm
https://www.relief.jp/docs/003721.html


EXCELで文字列を連結

EXCELで文字列を連結する方法はいくつかあるのですが、
関数で連結する方法を紹介します。
EXCELのバージョンによってちょいちょい呼び方が違うみたいです。

Excel2010 &演算子で結合、またはCONCATENATE関数
Excel2013 フラッシュフィル機能
Excel2016 CONCAT関数、TEXTJOIN関数

文字列どうしは&(正確には半角)で繋げれば文字列連結します。
または、CONCATENATE(A1,B1,C1)のようにつなげることも可能です。
どちらでも好きな方、というか見やすい方を適用すればOK。

TEXTJOINはもう少し高機能で、セルが空だったときの制御や
区切り文字の制御もできるうえ、セルの範囲指定もできるっぽいです。


参考:
http://www.shegolab.jp/entry/excel-macro-concat-cells-text


かなり便利なcountifs、EXCELで複数条件で出来るよ

はい、今回はcountifsです。
countifは良く使うのですが、多段階的にやれないものか、と思っていたら、普通にありました。
何だこの便利さん。


参考:
https://dekiru.net/article/4514/

countifsは範囲と条件を複数書けます。但し、条件は全てANDで繋がります。
よって、同範囲で2つの条件を書くことも可能なんですが、

例)
データが20~30の間に含まれる
=countifs(A:A,">=20",A:A,"<=30")

ただ、これ範囲を複数書けるので、複数のフィールドを見る条件が簡単に作れます。

=countifs(A:A,">=20",B:B,"=1")

ORの場合は、dcountを使うそうですが、そちらはそのうち。


EXCELで「開こうとしているファイル XXXX.csv は、実際にはその拡張子が示すファイル形式ではありません。」が出た。

正確にはそのあとに、


このファイルを開く前に、ファイルが破損していないこと、信頼できる発行元からのファイルであることを確認してください。
ファイルを今すぐ開きますか?


と出てきます。
これ、実はcsvファイルを作るときに1列目のヘッダーが「ID」となっている場合に発生します。
なので、その文字を他の文字(例えばXXX_IDみたいに何のIDかを表す)に変更すれば、問題なく開けます。

テキストエディタなどで開いてみても文字化けする場合は、この原因じゃない可能性がありますので、
やっぱりファイルの発行元に確認してください。


EXCELでcsvを開いたら、#NAME? と出る


EXCELの基本機能として、csvファイルを開くと勝手にデータを解釈してくれるのですが、
これがちょいちょい誤作動します。

特にデータの頭がハイフンで始まっていると、これが元で式の不整合「#NAME?」がでます。

●test.csv
aaa,bbb,ccc
123,-123,-ああああ

●EXCELで開くと
aaa bbb ccc
123 -123 #NAME?

ちなみに、#NAME?は、中身を見ると、

=-ああああ

となっており、式(=)化したあとで、式が壊れてる判断になるようです。

取り込み時にウィザードをつかって指定取り込みをすれば回避できるみたいです。

●参考
http://global-wing.com/activity/csv_point_convert.html

↑このページのトップヘ