Excel VBA で同じ項目の行を揃える
こんにちは。
仕事の自動化にやりがいと達成感を感じるガッくんです。
この記事の目次
背景・目的
いきなりですが、データ活用は重要です。
作業にかけた工数の集計、可視化や売上の集計可視化が即座にできるようなレイアウトでまとめる必要があります。
そこで、同じ項目について行の位置を揃えて、データの差異や特徴を捉えやすくする VBA をまとめます。
プログラム
ソースコード
Sub 行揃え()
' 値を調べるセルの設定
r = 4 ' 行
c = 4 ' 列
With ThisWorkbook.ActiveSheet
' 左の値と右の値の取得
左 = .Cells(r, c - 2).Value
右 = .Cells(r, c).Value
' ループ
Do While 左 <> "" And 右 <> ""
' 文字列の比較
比較 = StrComp(左, 右)
' 比較結果によって行を上下させる
Select Case 比較
Case -1
' 左が上
.Range(.Cells(r, c), .Cells(r, c + 1)).Insert Shift:=xlShiftDown
Case 0
' イコール(何もしない)
Case 1
' 右が上
.Range(.Cells(r, c - 2), .Cells(r, c - 1)).Insert Shift:=xlShiftDown
Case Null
' 引数が Null だから何もできない
End Select
' 左の値と右の値の更新
r = r + 1
左 = .Cells(r, c - 2).Value
右 = .Cells(r, c).Value
Loop
End With
End Sub
結果
図1 の表は品名をキーにして昇順に並んでいます。
昇順のソートはリンクの記事でまとめています。
『 Excel VBA で昇順に並べ替える - 解析エンジニアの自動化 blog 』
コメント
図2 の様にまとめる事で、顧客 A と顧客 B の差異がはっきりしました。
顧客 A によく売れるもの、顧客 B によく売れるものの傾向が分かります。
その傾向から顧客に買ってもらえそうなものを顧客単位で分析する事も出来そうですね。
以上
Excel VBA で昇順に並べ替える
こんにちは。
仕事の自動化にやりがいと達成感を感じるガッくんです。
この記事の目次
背景・目的
色々な VBA シーンに登場する並び替えですが、私は主に何かの処理の前処理や後処理で使っています。
いちいち書くのも、インターネットで探してコピペするのもめんどくさいので、サブルーチン化しておきます。
プログラム
ソースコード
Sub Test()
' アクティブシートのオブジェクト化
Set sh = ThisWorkbook.ActiveSheet
' 並び替えのキーセルのオブジェクト化
Set keyRng = sh.Cells(4, 1)
' 並び替え範囲のオブジェクト化
Set sortRng = sh.Range(keyRng, keyRng.End(xlDown))
' ソートサブルーチンのコール
Call 昇順ソート(sh, keyRng, sortRng)
End Sub
Sub 昇順ソート(ByVal objSheet, ByVal keyRng, ByVal sortRng)
With objSheet.Sort
' ソート設定のクリア
.SortFields.Clear
' ソート設定
.SortFields.Add _
Key:=keyRng, _
SortOn:=xlSortOnValues, _
Order:=xlAscending, _
DataOption:=xlSortNormal
' ソート範囲の設定
.SetRange sortRng
' ソート範囲の 1 行目がタイトルかどうかの設定
.Header = xlNo
' 大文字、小文字の区別設定
.MatchCase = False
' 並び替えの方向設定
.Orientation = xlTopToBottom
' ふりがなの使用設定
.SortMethod = xlPinYin
' 並び替え実行
.Apply
End With
End Sub
コメント
並び替え出来ました。
個人的に降順の並び替えはあまりやったことがないので、降順の並び替えサブルーチンは作りません。
以上
選択肢と選択回数で全パターンを出力する VBA
こんにちは。
仕事の自動化にやりがいと達成感を感じるガッくんです。
この記事の目次
背景・目的
色々な事を考える上で、当たり前のことを言いますが、状況や条件によって結果は変わります。
『OK、NG』や『 A パターン、 B パターン』の繰り返しで、考慮しなければならないパターンはどんどん増加します。
選択肢、選択回数を入力したら、全パターンを出力する VBA を書き留めておきます。
だいぶハマったので、冗長でエレガントなソースコードではありませんので、ご容赦下さい。
プログラム
ソースコード
Sub 組合せ()
選択肢 = Array("同位相", "逆位相")
選択回数 = 5 ' 何回選択するかを設定する変数
選択数 = 1 ' 何回目の選択かをカウントする変数
ReDim arrパターン(1 To 選択回数)
For I1 = LBound(選択肢) To UBound(選択肢)
arrパターン(選択数) = 選択肢(I1)
If 選択数 < 選択回数 Then
選択数 = 選択数 + 1
Call 再帰(選択肢, arrパターン, 選択回数, 選択数)
Else
' 選択回数が 1 回の時は再帰は発生しないのでこの処理で出力する
Debug.Print Join(arrパターン, ", ")
End If
Next I1
End Sub
Function 再帰(ByRef 選択肢, ByRef arrパターン, ByRef 選択回数, ByRef 選択数)
For I1 = LBound(選択肢) To UBound(選択肢)
arrパターン(選択数) = 選択肢(I1)
If 選択数 < 選択回数 Then
選択数 = 選択数 + 1
Call 再帰(選択肢, arrパターン, 選択回数, 選択数)
Else
' 選択回数が 2 回以上の時はこの処理で出力する
Debug.Print Join(arrパターン, ", ")
End If
Next I1
選択数 = 選択数 - 1
End Function
コメント
このプログラムは組合せサブルーチンと再帰関数で構成されていて、中身に共通する部分が多いため、組合せサブルーチンの中身を再帰関数に組み込めるはずです。
選択肢と選択回数を目的に沿って変更すれば、組合せの全パターンを出力出来ます。
Debug.Print で出力しているところでサブルーチンなどをコールすれば組合せパターンを活用出来ます。
ファイル出力のサブルーチンなど。
ハードウェアの運用で生じる故障やトラブルを網羅的に検証したい時に使えそうなプログラムですね。
以上
C# の List の 2次元配列みたいな使い方
こんにちは。
仕事の自動化にやりがいと達成感を感じるガッくんです。
この記事の目次
背景・目的
前の記事でListを使ってみましたが、使い勝手が良さそうなので、2次元配列みたいな使い方をしてます。
C# の List の使い方 - 解析エンジニアの自動化 blog
動作環境
・Windows 7
・Visual Studio 2017
プログラム
ソースコード
using System;
using System.Collections.Generic;
namespace List_How_to_use
{
class Program
{
static void Main(string[] args)
{
// 2 次元配列のように扱える List の定義
var varList = new List<List<int>>();
// 代入する要素の作成用変数
int calc = 0;
// List に要素を代入する
for(int i = 0; i < 10; i++)
{
// i 行目の要素に List を作成する
varList.Add(new List<int>());
// i 行目の要素を作成・追加する
for(int j = 0; j < 10; j++)
{
// 要素の作成
calc = i * j;
// 要素の追加
varList[i].Add(calc);
}
}
// 出力( 10 × 10 の数値が表示される)
// i 行目のループ
for(int i = 0; i < varList.Count; i++)
{
// j 列目のループ
for(int j = 0; j < varList[i].Count; j++)
{
// j 列目が最後の要素なら
if(j + 1 == varList[i].Count)
{
Console.WriteLine(string.Format("{0, 4}", varList[i][j]));
}
// j 列目が最後の要素では無いなら
else
{
Console.Write(string.Format("{0, 4}", varList[i][j]));
}
}
}
// 何かキーが押されるまで待つ
Console.ReadKey();
}
}
}
コメント
Listは使えますね。
とっつきにくくて、Listは使ってこなかったんですが、早く使うべきでした。
ソートや削除の方法もやってみたいと思います。
以上
C# の List の使い方
こんにちは。
仕事の自動化にやりがいと達成感を感じるガッくんです。
この記事の目次
背景・目的
C言語でもVBでも配列をよく使ってきました。
C#では配列の動的な追加、削除の方法が調べてもよく分からず、だいたいListを使う方法が推奨されていました。
C#を使う上でListの使い方を覚えておく必要性はかなり高いと感じたので記事にします。
動作環境
・Windows 7
・Visual Studio 2017
プログラム
ソースコード
using System;
using System.Collections.Generic;
namespace List_How_to_use
{
class Program
{
static void Main(string[] args)
{
// List の作成
var varList = new List<string>();
// List の要素追加
varList.Add("one");
varList.Add("two");
varList.Add("three");
varList.Add("four");
varList.Add("five");
// 出力
Console.WriteLine("[{0}]", string.Join(", ", varList));
// 何かキーが押されるまで待つ
Console.ReadKey();
}
}
}
コメント
かなり使いやすい印象です。
今度はListを2次元配列の様に扱う方法を試してみたいです。
以上
VBA からは操作可能なシートの保護方法
こんにちは。
仕事の自動化にやりがいと達成感を感じるガッくんです。
この記事の目次
背景・目的
Excel VBA でアプリケーションを作った時に、ユーザーに触って欲しくないセルや設定などがあったりします。
そんな時はセルの設定で『ロック』する指定を行い、『シートの保護』を行えば、セルをロックすることができます。
しかし、 VBA からもロックしたセルの値の取得などが出来なくなります。
そこで、 VBA からの操作は出来るけど、ユーザーの操作は出来ないといった設定を行います。
プログラム
ソースコード
このソースコードは VBE の ThisWorkbook モジュールに記述します。VBE は Excel 画面が表示されている時に Alt+F11 を押すと起動します。
Private Sub Workbook_Open()
With ThisWorkbook.Worksheets("保護したいシート名")
' シートが保護されていない場合
If .ProtectContents = False Then
' VBAからの変更はできるパスワード付き保護をかける(描画オブジェクトの操作も出来る)
.Protect UserInterfaceOnly:=True, Password:="pass", DrawingObjects:=False
' シートが保護されている場合
Else
' パスワード付き保護の解除
.Unprotect Password:="pass"
' VBAからの変更はできるパスワード付き保護をかける(描画オブジェクトの操作も出来る)
.Protect UserInterfaceOnly:=True, Password:="pass", DrawingObjects:=False
End If
End With
End Sub
コメント
シートの保護の Protect メソッドの引数は以下の通りたくさんあります。
上のソースコードでは DrawingObjects の VBA 操作を許可しています。
必要に応じて、以下の引数を調べてみてください。
DrawingObjects
Contents
Scenarios
UserInterfaceOnly
AllowFormattingCells
AllowFormattingColumns
AllowFormattingRows
AllowInsertingColumns
AllowInsertingRows
AllowInsertingHyperlinks
AllowDeletingColumns
AllowDeletingRows
AllowSorting
AllowFiltering
AllowUsingPivotTables
以上
C# で2次元の凸包(グラハムスキャン)を計算する
こんにちは。
仕事の自動化にやりがいと達成感を感じるガッくんです。
この記事の目次
背景・目的
板の上に数本の釘が刺さっていて、その全ての釘の外側から輪ゴムをかけた時に出来る多角形を求めることを凸包と言います。
簡単なものであれば、パターンマッチングや点の範囲を割り出す時に使えます。
範囲と範囲の重なりや離れ具合なんかを計算する時にも使えます。
2次元の平面上で凸包された輪郭線を検出したくなったので C# で作成しました。
動作環境
・Windows 7
・Visual Studio 2017
・OpenCvSharp
プログラム
仕様
以前、3次元点群データを作成する記事を書きました。C# でテスト用3次元点群データを作成する - 解析エンジニアの自動化 blog
せっかくなので、このプログラムで作成した点群データを使って、凸包します。
記事の通りのプログラムを実行すれば、デスクトップに Test-Nodes-Data.txt というファイルが作成されます。
プログラムを使う流れを考えます。
①上の記事のプログラムで作った点の情報が記述されたテキストファイルをGUIのテキストボックスにドラッグ&ドロップする。
②実行ボタンを押す。
以上!!
この仕様に見合ったGUIを考えます。
GUI
作成したGUIは図1 の通りです。2種類のコントロールを合計3つ設置しただけのとても簡単なものです。
・テキストボックス
・ボタン(実行)
・ボタン(キャンセル)
ソースコード
描画は OpenCvSharp を使いました。OpenGL(OpenTK)でも良かったのですが、後で OpenCV を使って凸包の結果に対して、さらに何らかの処理をする事を考えて、 OpenCV にしました。
今回はソースコードが長いので、もし、確認するのであれば、 Visual Studio にコピペして確認することをお勧めします。
using System;
using System.Collections;
using System.Windows.Forms;
using System.IO;
using OpenCvSharp.CPlusPlus;
namespace ConvexHuller
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void DD(object sender, DragEventArgs e)
{
// ドロップファイルの取得
string[] args = (string[])e.Data.GetData(DataFormats.FileDrop, false);
// ドロップファイル数の取得
int args_cnt = args.Length;
// ドロップファイル数のチェック
if (args_cnt > 1)
{
MessageBox.Show("引数が多すぎます。" + Environment.NewLine + "ファイルを1つだけドロップしてください。");
return;
}
// sender をテキストボックスとして変数化
TextBox txtTgt = sender as TextBox;
// sender がテキストボックスではないなら終了
if (txtTgt == null)
{
return;
}
// テキストボックスにファイル名出力
txtTgt.Text = args[0];
}
private void DE(object sender, DragEventArgs e)
{
if (e.Data.GetDataPresent(DataFormats.FileDrop))
{
e.Effect = DragDropEffects.All;
}
else
{
e.Effect = DragDropEffects.None;
}
}
//□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□
// 凸包
// ConvexHull(points)
//□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□
private double[][] ConvexHull(double[][] points)
{
// 引き渡されたジャグ配列が何行あるかを取得
int Num = points.Length;
// 同じ行数のジャグ配列の作成
double[][] convex = new double[Num][];
// Y の値が小さい、かつ、 X の値が大きいキー座標を探す
int limit = 0; // キー座標の要素番号記憶用変数
for (int i = 1; i < Num; i++)
{
// 小さい Y の値を見つけたら、要素番号を記憶する
if (points[limit][1] > points[i][1]) { limit = i; }
// Y の値が同じであっても、 X の値が大きければ要素番号を記憶する
else if (points[limit][1] == points[i][1] && points[limit][0] < points[i][0])
{ limit = i; }
}
// キー座標からみた角度を求める 以下のような並び順の配列を作る。ただし、キー座標の角度はゼロにする
// 角度 要否ID X座標 Y座標(3次元座標を持つ場合は凸包したい平面における座標値とする)
// なお、要否ID => 要:0 否:-1
double theta = 0; // 角度
for (int i = 0; i < Num; i++)
{
// キー座標なら、角度=0、要否ID=0、X座標、Y座標
if (limit == i)
{
convex[i] = new double[] { 0, 0, points[i][0], points[i][1] };
}
// キー座標以外は角度を算出して配列の作成
else
{
theta = Math.Atan2(points[i][1] - points[limit][1], points[i][0] - points[limit][0]);
if (theta < 0) { theta = 2 * Math.PI + theta; }
convex[i] = new double[] { theta, 0, points[i][0], points[i][1] };
}
}
// ゼロ列目の角度の大きい順にソートする
Array.Sort(convex, StructuralComparisons.StructuralComparer);
// 角HIJ 計算:角度がマイナスのI点はIDを-1として事実上、無きものとする
int del = 0; // 要素番号の増減用変数
int h; //
int j; //
double theta1 = 0; //
double theta2 = 0; //
int cntMinus = 0; // 要否IDが否になった要素数カウンタ
for (int i = 1; i < Num; i++)
{
if (convex[i][1] == -1)
{
while (convex[i][1] == -1) { i += del; }
}
del = 1; // 次のループのために初期化する
h = i - del; // i点の1個前の点の要素番号を設定する
j = i + del; // i点の1個後の点の要素番号を設定する
// h 点の探索
// 要否ID が「否」ならさらに1個前の要素番号を調べて要否ID が「要」の要素を見つける
while (convex[h][1] == -1) { del++; h = i - del; }
del = 1; // 次のループのために初期化する
// i点の1個後のj点の要素番号が配列の外に到達してしまったら
if (j == Num) { j = 0; }
// j 点の探索
// 要否ID が「否」ならさらに1個後の要素番号を調べて要否ID が「要」の要素を見つける
while (convex[j][1] == -1)
{
del++;
j = i + del;
// i点の1個後のj点の要素番号が配列の外に到達してしまったら
if (j == Num) { j = 0; }
}
del = 1; // 次のループのために初期化する
// i-h角度の算出
theta1 = Math.Atan2(convex[i][3] - convex[h][3], convex[i][2] - convex[h][2]);
if (theta1 < 0) { theta1 = 2 * Math.PI + theta1; }
// j-i角度の算出
theta2 = Math.Atan2(convex[j][3] - convex[i][3], convex[j][2] - convex[i][2]);
if (theta2 < 0) { theta2 = 2 * Math.PI + theta2; }
// 角度がマイナスなら要否ID を「否」にして要素数を減少させる
if (theta2 - theta1 < 0)
{
convex[i][1] = -1; cntMinus++;
while (convex[i][1] == -1) { i -= del; }
i--;
}
del = 1; // 次のループのために初期化する
}
// 戻り値用に配列に入れ直す
// ジャグ配列の準備
double[][] ret = new double[Num - cntMinus][];
int cnt = 0; // 要素番号カウンタ
for (int i = 0; i < convex.Length; i++)
{
// 要素ID が「否」なら戻り値から除外する
if (convex[i][1] != -1)
{
ret[cnt] = new double[] { convex[i][2], convex[i][3] };
cnt++;
}
}
// 戻り値
return ret;
}
//□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□
// OpenCV
// 凸包描画関数
// PlotConvexHull(img, points, Ox, Oy)
//□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□
private void PlotConvexHull(Mat img, double[][] points, double Ox, double Oy)
{
for (int i = 0; i < points.Length; i++)
{
// ジャグ配列で最後の要素はジャグ配列の先頭の要素と直線で結ぶため、要素番号を恣意的に操作して直線で結ぶ
if (i + 1 == points.Length)
{
Cv2.Line(img, new OpenCvSharp.CPlusPlus.Point(Ox + points[i][0], Oy - points[i][1]), new OpenCvSharp.CPlusPlus.Point(Ox + points[0][0], Oy - points[0][1]), new Scalar(0, 0, 255), 1);
}
// ジャグ配列の今のi要素とi+1要素を直線で結ぶ
else
{
Cv2.Line(img, new OpenCvSharp.CPlusPlus.Point(Ox + points[i][0], Oy - points[i][1]), new OpenCvSharp.CPlusPlus.Point(Ox + points[i + 1][0], Oy - points[i + 1][1]), new Scalar(0, 0, 255), 1);
}
}
}
//□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□
// OpenCV
// ×点の OpenCV 出力
// Cv2PlotPoint(img, points, Ox, Oy)
//□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□
private void Cv2PlotPoint(Mat img, double[][] point, double Ox, double Oy)
{
// ×印で点を描画する際の×の長さ設定
double xLength = 1.5;
double deltaX = xLength / Math.Sqrt(2);
double deltaY = xLength / Math.Sqrt(2);
// ×印で点を描画する
for (int i = 0; i < point.Length; i++)
{
// ×の\の描画
Cv2.Line(img, new OpenCvSharp.CPlusPlus.Point(Ox + point[i][0] - deltaX, Oy - point[i][1] - deltaY), new OpenCvSharp.CPlusPlus.Point(Ox + point[i][0] + deltaX, Oy - point[i][1] + deltaY), new Scalar(255, 255, 255));
// ×の/の描画
Cv2.Line(img, new OpenCvSharp.CPlusPlus.Point(Ox + point[i][0] + deltaX, Oy - point[i][1] - deltaY), new OpenCvSharp.CPlusPlus.Point(Ox + point[i][0] - deltaX, Oy - point[i][1] + deltaY), new Scalar(255, 255, 255));
}
}
//■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
// 実行ボタン
//■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
private void button1_Click(object sender, EventArgs e)
{
// このメソッドへのパラメータ
string arg = textBox1.Text;
string sDir;
string sFile;
string[] sReadLines;
int cntNodes = 0;
string[] delimLine;
double sX;
double sZ;
double minX = 0; double maxX = 0; double minZ = 0; double maxZ = 0;
// 引数ファイルの情報取得
sDir = Path.GetDirectoryName(arg);
sFile = Path.GetFileNameWithoutExtension(arg);
// 引数ファイルの読み込み
sReadLines = File.ReadAllLines(arg);
// ノード情報の数をカウント
for (int j = 0; j < sReadLines.Length; j++)
{
if (sReadLines[j].IndexOf('*') == -1)
{
cntNodes++;
}
}
// ノード数分のジャグ配列を用意する
double[][] coorXZ = new double[cntNodes][];
// ノードの X , Z 座標ジャグ配列を作成
cntNodes = 0;
for (int j = 0; j < sReadLines.Length; j++)
{
if (sReadLines[j].IndexOf('*') == -1)
{
delimLine = sReadLines[j].Split(',');
sX = double.Parse(delimLine[1]);
sZ = double.Parse(delimLine[3]);
if (minX > sX) { minX = sX; }
if (maxX < sX) { maxX = sX; }
if (minZ > sZ) { minZ = sZ; }
if (maxZ < sZ) { maxZ = sZ; }
coorXZ[cntNodes] = new double[] { sX, sZ };
cntNodes++;
}
}
// 凸包
double[][] outXZ = ConvexHull(coorXZ);
// 原点とマージン設定
double Ox = 0; double Oz = 0; double margin = 25;
// 画面サイズ
double justHeight = margin + Math.Abs(maxZ - minZ) + margin;
double justWidth = margin + Math.Abs(maxX - minX) + margin;
int screenHeight = (int)Math.Ceiling(justHeight); // 小数点切り上げ
int screenWidth = (int)Math.Ceiling(justWidth); // 小数点切り上げ
if (minX < 0) { Ox = Math.Abs(0 - minX) + margin; }
else { Ox = -minX + margin; }
if (minZ < 0) { Oz = Math.Abs(0 - minZ) + margin; }
else { Oz = screenHeight - minZ + margin; }
// Mat クラスで画像を作成する
Mat img1 = new Mat(screenHeight, screenWidth, MatType.CV_8UC3, new Scalar(0, 0, 0));
Mat imgGray1 = new Mat(screenHeight, screenWidth, MatType.CV_8SC1, new Scalar(0, 0, 0));
Mat imgBinary1 = new Mat(screenHeight, screenWidth, MatType.CV_8SC1, new Scalar(0, 0, 0));
// Mat にノード
Cv2PlotPoint(img1, coorXZ, Ox, Oz);
// Mat に凸包描画
PlotConvexHull(img1, outXZ, Ox, Oz);
Cv2.ImShow("image1", img1);
}
//■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
// キャンセルボタン
//■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
private void button2_Click(object sender, EventArgs e)
{
this.Close();
}
}
}
コメント
3次元の点群データから X 、 Z 座標を選び、平面的に扱いましたが、 X 、 Y 座標でももちろん出来ます。
必要に応じてソースコードを変更してください。
個人的には面白かったので、3次元凸包も試してみたくなりました。
以上