Excelで列番号を英文字に変換する方法
「Excelで列番号を英文字に変換する方法 - チキン煮込みチーズミックス4辛」は間違っていた。Excelの列を表すアルファベットは、A〜Zを使った26進数表現ではない。具体的には、次のようになっている。
桁数 | 列番号(0ベース*1) | アルファベット*2 | 場合の数 |
---|---|---|---|
1 | 0〜25 | A〜Z | 通り |
2 | 26〜701 | AA〜ZZ | 通り |
3 | 702〜18278 | AAA〜ZZZ | 通り |
また、上記のURLで紹介したアルゴリズムは、アルファベットが2桁までならば正しく計算できるようだ。アルファベットが3桁になるような列番号を入力すると、間違った値が得られる。ConvertToLetter(702)は"ZZ"で、ConvertToLetter(703)は"[A"となってしまう。
ということで、列番号を英文字に変換するVBA関数ConvertToLetter2を書いてみた。
Option Explicit Function ConvertToLetter2(iCol As Integer) As String 'Excelの列番号は1始まりなので、-1して0始まりにする ConvertToLetter2 = ConvertToLetter2_detail(iCol - 1) End Function Function ConvertToLetter2_detail(iCol As Integer) As String Dim nCases As Long Dim nCasesPrev As Long Dim i As Integer '何桁になるか調べる i = 1 nCases = 0 nCasesPrev = 0 Do While True nCasesPrev = nCases nCases = SumOfPower(1, i, 26) If iCol < nCases Then Exit Do End If i = i + 1 Loop 'i桁のアルファベットであることが確定 'i-1桁の最大値以下ではnCasesPrev通りの場合の数を表現できる Dim rest As Long rest = iCol - nCasesPrev Dim result As String result = ConvertRadix(rest) Dim act_length As Integer act_length = Len(result) 'i桁にする Dim shortage As Integer shortage = i - act_length For i = 0 To shortage - 1 result = "A" & result Next ConvertToLetter2_detail = result End Function Function SumOfPower(lowerLim As Integer, upperLim As Integer, base As Integer) As Long Dim result As Long result = 0 Dim i As Integer For i = lowerLim To upperLim Dim tmp As Long tmp = base ^ i result = result + tmp Next SumOfPower = result End Function Function ConvertRadix(n10 As Long) As String Dim result As String Dim codeA As Integer Dim radix As Integer Dim quotient As Long codeA = Asc("A") radix = 26 Dim r, q As Long quotient = n10 Do q = quotient \ radix r = quotient - (q * radix) quotient = q If q > 0 Then result = Chr(codeA + r) & result End If Loop While q > 0 result = Chr(codeA + r) & result ConvertRadix = result End Function
数式の結果が更新されない
次のような引数で指定された範囲をカンマ区切りで連結して返す関数concatを準備しといて、この関数を複数シートで使って、ブックをxlsm形式で保存した。
concatを使うセルはA1でconcatの引数はA2:A21。各シートで位置は揃えている。
Option Explicit Function concat(rng As Range) As String Dim startX, startY, endX, endY As Long, strResult As String startX = rng.Columns.Column startY = rng.Rows.Row endX = startX + rng.Columns.Count - 1 endY = startY + rng.Rows.Count - 1 Dim currX As Integer For currX = startX To endX Dim currY As Integer For currY = startY To endY If Len(Cells(currY, currX)) > 0 Then If Len(strResult) < 1 Then strResult = Cells(currY, currX) Else strResult = strResult & "," & Cells(currY, currX) End If End If Next currY Next currX concat = strResult End Function
つまり、保存する内容はこんな感じ。左からSheet1、Sheet2、Sheet3。
で、次にこのファイル開いたとき、
が出るけど、こいつでマクロを許可してやると、マクロを許可したときに表示していたページのconcatの結果が、その他の全ページconcatしているセルの値に表示されている。さっきのSheet2を選んでマクロ許可してやると、こんな感じになる。
この状態から各シートのA1セルを再計算(いったんセルを編集状態にして確定するなど)すると、ちゃんと正しい値になる。
なんなのこの挙動。最初から保存した内容で表示するにはどうしたら良いのだろう。ちなみに、計算方法は「自動」にしてる。
誰かおせーて。
追記2016/05/28
2重ループは重い処理だと勝手にExcelが判定するから再計算しないのかと考え、どうせ1列しか見ないので、ループをネストさせないようにしてみた。結果は同じだった。
64ビットのマシンだからかと考え、32ビットの別PCでも試してみたが、やはりこれもダメだった。
現象だけ見ると「エクセルで数式の結果が反映されない(自動計算になっているのに) -す- Excel(エクセル) | 教えて!goo」と似ているように思われるけど、この人も解決できていないっぽい。
かなり困るんだけどな...
デシリアライズではまった
次のサンプルコードのMyMapクラスのようなクラスをファイルにシリアライズしてデシリアライズすると、デシリアライズ用のコンストラクタの中で_entriesの要素が全部(null,null)になってしまう。entry.Item1とentry.Item2を使って処理をしていたので、実行時エラーになっていた。そしてその理由が分からず数週間悩んだ。
Testメソッドで、デシリアライズ済みのオブジェクトを操作するときは、_entriesには正しい値が入っているので、ちゃんとTryGetValueを実行できている。entry.Item1やentry.Item2に依存した何らかの初期化処理をしたい場合、どうしたら良いのかな?
using System; using System.Collections.Generic; using System.IO; using System.Runtime.Serialization; using System.Runtime.Serialization.Formatters.Binary; namespace ConsoleApplication1 { [Serializable] class MyString : ISerializable { string _value; public MyString(string str) { _value = str; } protected MyString(SerializationInfo info, StreamingContext context) { _value = info.GetString("Value"); } public void GetObjectData(SerializationInfo info, StreamingContext context) { info.AddValue("Value", _value); } public string Value { get { return _value; } set { _value = value; } } public override string ToString() { return Value; } } [Serializable] class MyMap : ISerializable { int _n; IList<Tuple<MyString, MyString>> _entries; public MyMap() { _n = 0; _entries = new List<Tuple<MyString, MyString>>(); } protected MyMap(SerializationInfo info, StreamingContext context) { _n = info.GetInt32("N"); Console.WriteLine(_n); _entries = (IList<Tuple<MyString, MyString>>)info.GetValue("Entries", typeof(IList<Tuple<MyString, MyString>>)); foreach (var entry in _entries) { // なぜかすべてのTupleが(null, null)になっている。 Console.WriteLine("{0} -> {1}", entry.Item1, entry.Item2); } } public void GetObjectData(SerializationInfo info, StreamingContext context) { info.AddValue("N", _n); info.AddValue("Entries", _entries); } public void Add(MyString val1, MyString val2) { Tuple<MyString, MyString> entry = Tuple.Create(val1, val2); _entries.Add(entry); } public bool TryGetValue(MyString key, out MyString result) { foreach (Tuple<MyString, MyString> entry in _entries) { if (entry.Item1.Value == key.Value) { result = entry.Item2; return true; } } result = null; return false; } public int N { get { return _n; } set { _n = value; } } } class Program { static MyString Key = new MyString("bar"); // 【要修正】ファイル出力先 const string FilePath = @"C:\Users\foobar\Desktop\a.out"; static void Main(string[] args) { MyMap map = new MyMap(); map.Add(new MyString("foo"), new MyString("hoge")); map.Add(new MyString("bar"), new MyString("fuga")); map.Add(new MyString("baz"), new MyString("piyo")); map.N = 42; // シリアライズ → デシリアライズ Test(map); } static void Test(MyMap obj) { MyString result1 = null; obj.TryGetValue(Key, out result1); System.Console.WriteLine(result1); Stream s = Serialize(obj); MyMap tmp = Deserialize(); MyString result2 = null; tmp.TryGetValue(Key, out result2); System.Console.WriteLine(result2); } static Stream Serialize(MyMap obj) { Stream s = null; try { s = new FileStream(FilePath, FileMode.Create); BinaryFormatter bf = new BinaryFormatter(); bf.Serialize(s, obj); } catch (Exception) { throw; } finally { if (s != null) { s.Close(); } } return s; } static MyMap Deserialize() { MyMap ret = null; Stream s = null; try { s = new FileStream(FilePath, FileMode.Open); s.Position = 0; BinaryFormatter bf = new BinaryFormatter(); ret = (MyMap)bf.Deserialize(s); } catch (Exception) { throw; } finally { if (s != null) { s.Close(); } } return ret; } } }
Excelで列番号を英文字に変換する方法
http://support.microsoft.com/kb/833402/ja にあるけど、これってA〜Zを使った26進数表現だよね、たぶん。A〜Zを使った26進数表現だと思ってたけど違った*1。
(念のため魚拓: http://megalodon.jp/2014-0628-2058-19/support.microsoft.com/kb/833402/ja )
Function ConvertToLetter(iCol As Integer) As String Dim iAlpha As Integer Dim iRemainder As Integer iAlpha = Int((iCol - 1) / 26) iRemainder = iCol - (iAlpha * 26) If iAlpha > 0 Then ConvertToLetter = Chr(iAlpha + 64) End If If iRemainder > 0 Then ConvertToLetter = ConvertToLetter & Chr(iRemainder + 64) End If End Function
*1:2016/5/26追記
Function内部でHiddenプロパティが効かない件
次のようなコードを書いていた
Function hidenUnusedColumn() As Integer Dim rangeStr As String rangeStr = 【非表示にする列の範囲を求める関数を実行】 Columns(rangeStr).Hidden = True hidenUnusedColumn = 0 End Function
SubじゃなくてFunctionにしているのは、特に意図はない。
rangeStrは正しく求められている。けどHiddenプロパティが動いてくれない。
数時間悩んだ。
FunctionじゃなくてSubで定義したらちゃんと動いた。
FunctionとSubって、戻り値を返せるかどうかの違いだけだと思ってたけど違うのかな?
Sub内でしか実行できない手続きって何だろう?
逆にFunctionでしか実行できないものってあるのかな。
カレントスキーマの全テーブルの全カラムを一覧するSQL
次の情報も一緒に取得。
- カラムの型
- 主キーかどうか
- NOT NULL制約列かどうか
SELECT A.TABLE_NAME ,A.COLUMN_NAME ,A.COLUMN_ID ,A.DATA_TYPE ,A.DATA_LENGTH ,A.DATA_PRECISION ,A.DATA_SCALE ,SQ1.CONSTRAINT_TYPE /* ,CASE NVL(SQ1.CONSTRAINT_TYPE,'') WHEN 'P' THEN 1 ELSE 0 END PKEY */ ,A.NULLABLE /* ,CASE NVL(A.NULLABLE,'') WHEN 'N' THEN 1 ELSE 0 END NOTNULL */ FROM USER_TAB_COLUMNS A ,(SELECT B.TABLE_NAME ,B.COLUMN_NAME ,C.CONSTRAINT_TYPE FROM USER_CONS_COLUMNS B ,USER_CONSTRAINTS C WHERE B.OWNER = C.OWNER AND B.TABLE_NAME = C.TABLE_NAME AND B.CONSTRAINT_NAME = C.CONSTRAINT_NAME AND C.CONSTRAINT_TYPE = 'P' ) SQ1 WHERE A.TABLE_NAME = SQ1.TABLE_NAME(+) AND A.COLUMN_NAME = SQ1.COLUMN_NAME(+) ORDER BY A.TABLE_NAME ,A.COLUMN_ID