エクセル雑感
ITエンジニアなら知らないと恥ずかしい数学20選

users = [{"name": "Alice", "active": True}, {"name": "Bob", "active": True}]
all_active = all(u["active"] for u in users)
has_bob = any(u["name"] == "Bob" for u in users)

Sub CheckLogic()
    Dim names As Variant, active As Variant
    Dim i As Long
    Dim allActive As Boolean, hasBob As Boolean

    names = Array("Alice", "Bob")
    active = Array(True, True)
    allActive = True
    hasBob = False

    For i = LBound(names) To UBound(names)
        If Not active(i) Then allActive = False
        If names(i) = "Bob" Then hasBob = True
    Next i

    Debug.Print allActive, hasBob
End Sub

const users = [
    { name: "Alice", active: true },
    { name: "Bob", active: true }
];

const allActive = users.every(u => u.active);
const hasBob = users.some(u => u.name === "Bob");

console.log(allActive, hasBob);

user = {"active": True, "role": "editor", "suspended": False}

can_edit = (
    user["active"]
    and user["role"] in {"admin", "editor"}
    and not user["suspended"]
)

print(can_edit)

Sub PermissionExample()
    Dim active As Boolean, suspended As Boolean
    Dim role As String
    Dim canEdit As Boolean

    active = True
    role = "editor"
    suspended = False

    canEdit = active And _
              (role = "admin" Or role = "editor") And _
              Not suspended

    Debug.Print canEdit
End Sub

const user = {
    active: true,
    role: "editor",
    suspended: false
};

const canEdit =
    user.active &&
    ["admin", "editor"].includes(user.role) &&
    !user.suspended;

console.log(canEdit);

not (is_active and has_permission)
# 同値
(not is_active) or (not has_permission)

Not (isActive And hasPermission)
' 同値
(Not isActive) Or (Not hasPermission)

!(isActive && hasPermission)
// 同値
!isActive || !hasPermission

from itertools import groupby
data = [("A", 1), ("A", 2), ("B", 3)]
for key, group in groupby(data, lambda x: x[0]):
    print(key, list(group))

※ itertools.groupby は、同じキーの要素が連続している範囲をグループ化します。キーの順序を問わず同じキーの要素をまとめたい場合は、あらかじめキーでソートしておく必要があります。

Sub GroupExample()
    Dim dict As Object
    Dim groups As Collection
    Dim keys As Variant
    Dim i As Long
    Dim key As String
    Dim value As Variant
    Set dict = CreateObject("Scripting.Dictionary")
    For i = 1 To 3
        If i < 3 Then
            key = "A"
        Else
            key = "B"
        End If
        value = i
        If Not dict.Exists(key) Then
            Set groups = New Collection
            dict.Add key, groups
        End If
        Set groups = dict(key)
        groups.Add value
    Next i
    keys = dict.keys
    For i = LBound(keys) To UBound(keys)
        Set groups = dict(keys(i))
        Debug.Print keys(i) & ":"
        For Each value In groups
            Debug.Print " " & value
        Next value
    Next i
End Sub

const data = [["A", 1], ["A", 2], ["B", 3]];

const grouped = data.reduce((acc, [key, value]) => {
    if (!acc[key]) {
        acc[key] = [];
    }
    acc[key].push(value);
    return acc;
}, {});

console.log(grouped);

viewer = {"read"}
editor = {"read", "write"}
admin = {"read", "write", "delete"}

print(viewer <= editor)
print(editor <= admin)

Function IsSubset(smaller As Object, larger As Object) As Boolean
    Dim key As Variant
    For Each key In smaller.Keys
        If Not larger.Exists(key) Then Exit Function
    Next key
    IsSubset = True
End Function

Sub RoleOrderExample()
    Dim viewer As Object, editor As Object, admin As Object
    Set viewer = CreateObject("Scripting.Dictionary")
    Set editor = CreateObject("Scripting.Dictionary")
    Set admin = CreateObject("Scripting.Dictionary")

    viewer("read") = True
    editor("read") = True: editor("write") = True
    admin("read") = True: admin("write") = True: admin("delete") = True

    Debug.Print IsSubset(viewer, editor)
    Debug.Print IsSubset(editor, admin)
End Sub

function isSubset(smaller, larger) {
    return [...smaller].every(x => larger.has(x));
}

const viewer = new Set(["read"]);
const editor = new Set(["read", "write"]);
const admin = new Set(["read", "write", "delete"]);

console.log(isSubset(viewer, editor));
console.log(isSubset(editor, admin));

numbers = [1, 2, 3]
squared = list(map(lambda x: x ** 2, numbers))
print(squared)

Sub MapExample()
    Dim arr(2) As Long, i As Long
    arr(0) = 1: arr(1) = 2: arr(2) = 3
    For i = LBound(arr) To UBound(arr)
        arr(i) = arr(i) * arr(i)
    Next i
    MsgBox arr(2)
End Sub

const numbers = [1, 2, 3];
const squared = numbers.map(x => x ** 2);
console.log(squared);

items = ["A", "B", "C"]

for mask in range(1 << len(items)):
    subset = [
        items[i]
        for i in range(len(items))
        if mask & (1 << i)
    ]
    print(subset)

Sub BitSearchExample()
    Dim items As Variant
    Dim mask As Long, i As Long
    Dim subset As String

    items = Array("A", "B", "C")

    For mask = 0 To (2 ^ (UBound(items) + 1)) - 1
        subset = ""

        For i = 0 To UBound(items)
            If (mask And CLng(2 ^ i)) <> 0 Then
                subset = subset & items(i)
            End If
        Next i

        Debug.Print subset
    Next mask
End Sub

const items = ["A", "B", "C"];

for (let mask = 0; mask < (1 << items.length); mask++) {
    const subset = [];

    for (let i = 0; i < items.length; i++) {
        if (mask & (1 << i)) {
            subset.push(items[i]);
        }
    }

    console.log(subset);
}

users = [
    {"first": "Taro", "last": "Yamada", "active": True},
    {"first": "Hanako", "last": "Sato", "active": False},
]

view_models = [
    {
        "displayName": f'{u["last"]} {u["first"]}',
        "status": "有効" if u["active"] else "停止",
    }
    for u in users
]

print(view_models)

Sub ApiMappingExample()
    Dim firstName As Variant
    Dim lastName As Variant
    Dim active As Variant
    Dim i As Long

    firstName = Array("Taro", "Hanako")
    lastName = Array("Yamada", "Sato")
    active = Array(True, False)

    For i = 0 To 1
        Debug.Print lastName(i) & " " & firstName(i), _
                    IIf(active(i), "有効", "停止")
    Next i
End Sub

const users = [
    { first: "Taro", last: "Yamada", active: true },
    { first: "Hanako", last: "Sato", active: false }
];

const viewModels = users.map(u => ({
    displayName: `${u.last} ${u.first}`,
    status: u.active ? "有効" : "停止"
}));

console.log(viewModels);

READ = 0b0001
WRITE = 0b0010
DELETE = 0b0100

permission = READ | WRITE

print(bool(permission & WRITE))
print(bool(permission & DELETE))

permission |= DELETE
print(bool(permission & DELETE))

Sub PermissionBitMaskExample()
    Const READ As Long = 1
    Const WRITE As Long = 2
    Const DELETE As Long = 4
    Dim permission As Long

    permission = READ Or WRITE

    Debug.Print (permission And WRITE) <> 0
    Debug.Print (permission And DELETE) <> 0

    permission = permission Or DELETE
    Debug.Print (permission And DELETE) <> 0
End Sub

const READ = 0b0001;
const WRITE = 0b0010;
const DELETE = 0b0100;

let permission = READ | WRITE;

console.log((permission & WRITE) !== 0);
console.log((permission & DELETE) !== 0);

permission |= DELETE;
console.log((permission & DELETE) !== 0);

graph = {
    "A": ["B", "C"],
    "B": ["D"],
    "C": ["D"],
    "D": []
}

def dfs(node, visited):
    if node in visited:
        return

    visited.add(node)
    print(node)

    for next_node in graph[node]:
        dfs(next_node, visited)

dfs("A", set())

Sub DFSExample()
    Dim graph As Object
    Dim visited As Object

    Set graph = CreateObject("Scripting.Dictionary")
    Set visited = CreateObject("Scripting.Dictionary")

    graph.Add "A", Array("B", "C")
    graph.Add "B", Array("D")
    graph.Add "C", Array("D")
    graph.Add "D", Array()

    DFS "A", graph, visited
End Sub

Sub DFS(node As String, graph As Object, visited As Object)
    Dim nextNode As Variant

    If visited.Exists(node) Then Exit Sub

    visited.Add node, True
    Debug.Print node

    For Each nextNode In graph(node)
        DFS CStr(nextNode), graph, visited
    Next nextNode
End Sub

const graph = {
    A: ["B", "C"],
    B: ["D"],
    C: ["D"],
    D: []
};

function dfs(node, visited) {
    if (visited.has(node)) return;

    visited.add(node);
    console.log(node);

    for (const nextNode of graph[node]) {
        dfs(nextNode, visited);
    }
}

dfs("A", new Set());

dependents = {
    "database": ["order-api", "billing-api"],
    "order-api": ["web"],
    "billing-api": ["web"],
    "web": [],
}

def affected(service, seen=None):
    seen = seen or set()
    for next_service in dependents[service]:
        if next_service not in seen:
            seen.add(next_service)
            affected(next_service, seen)
    return seen

print(affected("database"))

Sub ImpactExample()
    Dim graph As Object, seen As Object
    Set graph = CreateObject("Scripting.Dictionary")
    Set seen = CreateObject("Scripting.Dictionary")

    graph.Add "database", Array("order-api", "billing-api")
    graph.Add "order-api", Array("web")
    graph.Add "billing-api", Array("web")
    graph.Add "web", Array()

    TraceImpact "database", graph, seen
End Sub

Sub TraceImpact(service As String, graph As Object, seen As Object)
    Dim nextService As Variant
    For Each nextService In graph(service)
        If Not seen.Exists(nextService) Then
            seen.Add nextService, True
            Debug.Print nextService
            TraceImpact CStr(nextService), graph, seen
        End If
    Next nextService
End Sub

const dependents = {
    "database": ["order-api", "billing-api"],
    "order-api": ["web"],
    "billing-api": ["web"],
    "web": []
};

function affected(service, seen = new Set()) {
    for (const nextService of dependents[service]) {
        if (!seen.has(nextService)) {
            seen.add(nextService);
            affected(nextService, seen);
        }
    }
    return seen;
}

console.log([...affected("database")]);

user_dict = {"usr_001": "Alice"}
print(user_dict.get("usr_001"))

Sub HashExample()
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")
    dict.Add "usr_001", "Alice"
    MsgBox dict("usr_001")
End Sub

const userMap = new Map();
userMap.set("usr_001", "Alice");
console.log(userMap.get("usr_001"));

tree = {
    "value": "A",
    "left": {"value": "B", "left": None, "right": None},
    "right": {"value": "C", "left": None, "right": None}
}

def preorder(node):
    if node is None:
        return
    print(node["value"])
    preorder(node["left"])
    preorder(node["right"])

preorder(tree)

Sub TreeExample()
    Dim value As Variant
    Dim left As Variant
    Dim right As Variant

    value = Array("A", "B", "C")
    left = Array(1, -1, -1)
    right = Array(2, -1, -1)

    Preorder value, left, right, 0
End Sub

Sub Preorder(value As Variant, left As Variant, right As Variant, index As Long)
    If index < 0 Then Exit Sub

    Debug.Print value(index)
    Preorder value, left, right, left(index)
    Preorder value, left, right, right(index)
End Sub

const tree = {
    value: "A",
    left: { value: "B", left: null, right: null },
    right: { value: "C", left: null, right: null }
};

function preorder(node) {
    if (node === null) return;
    console.log(node.value);
    preorder(node.left);
    preorder(node.right);
}

preorder(tree);

organization = {
    "name": "開発部",
    "children": [
        {"name": "基盤チーム", "children": []},
        {"name": "業務チーム", "children": []},
    ],
}

def walk(node, depth=0):
    print("  " * depth + node["name"])
    for child in node["children"]:
        walk(child, depth + 1)

walk(organization)

Sub OrganizationTreeExample()
    Dim names As Variant
    Dim parent As Variant
    Dim i As Long

    names = Array("開発部", "基盤チーム", "業務チーム")
    parent = Array(-1, 0, 0)

    PrintChildren names, parent, -1, 0
End Sub

Sub PrintChildren( _
    names As Variant, _
    parent As Variant, _
    parentIndex As Long, _
    depth As Long)

    Dim i As Long

    For i = LBound(names) To UBound(names)
        If parent(i) = parentIndex Then
            Debug.Print Space(depth * 2) & names(i)
            PrintChildren names, parent, i, depth + 1
        End If
    Next i
End Sub

const organization = {
    name: "開発部",
    children: [
        { name: "基盤チーム", children: [] },
        { name: "業務チーム", children: [] }
    ]
};

function walk(node, depth = 0) {
    console.log("  ".repeat(depth) + node.name);
    node.children.forEach(child => walk(child, depth + 1));
}

walk(organization);

values = [10, 11, 12, 13, 24]
modulus = 12

print([value % modulus for value in values])

Sub ModuloExample()
    Dim values As Variant
    Dim i As Long
    Dim modulus As Long

    values = Array(10, 11, 12, 13, 24)
    modulus = 12

    For i = LBound(values) To UBound(values)
        Debug.Print values(i) Mod modulus
    Next i
End Sub

const values = [10, 11, 12, 13, 24];
const modulus = 12;

console.log(values.map(value => value % modulus));

base = 7
exponent = 13
modulus = 10

print(pow(base, exponent, modulus))

Sub ModularPowerExample()
    Dim base As Long, exponent As Long, modulus As Long
    Dim result As Long

    base = 7
    exponent = 13
    modulus = 10
    result = 1
    base = base Mod modulus

    Do While exponent > 0
        If exponent Mod 2 = 1 Then
            result = (result * base) Mod modulus
        End If
        base = (base * base) Mod modulus
        exponent = exponent \ 2
    Loop

    Debug.Print result
End Sub

※ 繰り返し二乗法による実装です。ただし、VBAの Long には扱える整数の範囲に制限があるため、暗号で扱うような巨大整数をそのまま計算する用途には使用できません。

let base = 7n;
let exponent = 13n;
const modulus = 10n;
let result = 1n;

while (exponent > 0n) {
    if (exponent % 2n === 1n) {
        result = (result * base) % modulus;
    }
    base = (base * base) % modulus;
    exponent = exponent / 2n;
}

console.log(result.toString());

※ JavaScriptでは、整数リテラルの末尾に n を付けると BigInt になります。通常の Number では正確に扱える整数の範囲に限界があるため、大きな整数を扱う場合に使用します。ここでは、大きな整数を扱う場面を想定して BigInt を使用しています。BigInt と Number は混在させて演算できません。

engineers = ["Aki", "Mika", "Ken"]

for day in range(7):
    person = engineers[day % len(engineers)]
    print(day + 1, person)

Sub RotationExample()
    Dim engineers As Variant
    Dim day As Long, index As Long

    engineers = Array("Aki", "Mika", "Ken")

    For day = 0 To 6
        index = day Mod (UBound(engineers) + 1)
        Debug.Print day + 1, engineers(index)
    Next day
End Sub

const engineers = ["Aki", "Mika", "Ken"];

for (let day = 0; day < 7; day++) {
    const person = engineers[day % engineers.length];
    console.log(day + 1, person);
}

def is_power_of_two(n):
    return n > 0 and (n & (n - 1)) == 0

print(is_power_of_two(8))
print(is_power_of_two(10))

Function IsPowerOfTwo(n As Long) As Boolean
    If n <= 0 Then
        IsPowerOfTwo = False
    Else
        IsPowerOfTwo = (n And (n - 1)) = 0
    End If
End Function

Sub PowerOfTwoExample()
    Debug.Print IsPowerOfTwo(8)
    Debug.Print IsPowerOfTwo(10)
End Sub

function isPowerOfTwo(n) {
    return n > 0n && (n & (n - 1n)) === 0n;
}

console.log(isPowerOfTwo(8n));
console.log(isPowerOfTwo(10n));

a = 0b101101
b = 0b100001

distance = (a ^ b).bit_count()
print(distance)

Function HammingDistance(a As Long, b As Long) As Long
    Dim x As Long
    x = a Xor b

    Do While x <> 0
        x = x And (x - 1)
        HammingDistance = HammingDistance + 1
    Loop
End Function

Sub HammingExample()
    Debug.Print HammingDistance(45, 33)
End Sub

function bitCount(n) {
    let count = 0;
    while (n !== 0) {
        n &= n - 1;
        count++;
    }
    return count;
}

const a = 0b101101;
const b = 0b100001;

console.log(bitCount(a ^ b));

※このJavaScript例は、説明を簡潔にするため非負の32ビット整数を対象としています。

def fib(n):
    if n < 2: return n
    return fib(n - 1) + fib(n - 2)

Function Fib(n As Long) As Long
    If n < 2 Then
        Fib = n
    Else
        Fib = Fib(n - 1) + Fib(n - 2)
    End If
End Function

function fib(n) {
    if (n < 2) return n;
    return fib(n - 1) + fib(n - 2);
}

console.log(fib(10));

data = [10, 13, 18, 20, 25]

diff = [
    data[i + 1] - data[i]
    for i in range(len(data) - 1)
]

print(diff)

Sub DifferenceExample()
    Dim data As Variant
    Dim diff() As Long
    Dim i As Long

    data = Array(10, 13, 18, 20, 25)
    ReDim diff(0 To UBound(data) - 1)

    For i = 0 To UBound(diff)
        diff(i) = data(i + 1) - data(i)
    Next i

    For i = 0 To UBound(diff)
        Debug.Print diff(i)
    Next i
End Sub

const data = [10, 13, 18, 20, 25];

const diff = [];
for (let i = 0; i < data.length - 1; i++) {
    diff.push(data[i + 1] - data[i]);
}

console.log(diff);

stock = 100
changes = [-10, -20, 50, -15]

for change in changes:
    stock = stock + change
    print(stock)

Sub StockTransitionExample()
    Dim stock As Long
    Dim changes As Variant
    Dim i As Long

    stock = 100
    changes = Array(-10, -20, 50, -15)

    For i = LBound(changes) To UBound(changes)
        stock = stock + changes(i)
        Debug.Print stock
    Next i
End Sub

let stock = 100;
const changes = [-10, -20, 50, -15];

for (const change of changes) {
    stock = stock + change;
    console.log(stock);
}

wait = 1

for retry in range(5):
    print(retry + 1, wait)
    wait *= 2

Sub ExponentialBackoffExample()
    Dim wait As Long
    Dim retry As Long

    wait = 1

    For retry = 1 To 5
        Debug.Print retry, wait
        wait = wait * 2
    Next retry
End Sub

let wait = 1;

for (let retry = 1; retry <= 5; retry++) {
    console.log(retry, wait);
    wait *= 2;
}

※実際の指数バックオフでは、待機時間の上限を設けたり、同時再試行の集中を避けるためにランダムな揺らぎ（jitter）を加えたりすることがあります。

total = 0.0
term = 1.0

for _ in range(30):
    total += term
    term /= 2

print(total)

Sub InfiniteSeriesExample()
    Dim total As Double
    Dim term As Double
    Dim i As Long

    total = 0#
    term = 1#

    For i = 1 To 30
        total = total + term
        term = term / 2
    Next i

    Debug.Print total
End Sub

let total = 0;
let term = 1;

for (let i = 0; i < 30; i++) {
    total += term;
    term /= 2;
}

console.log(total);

estimate = 1000.0
target = 250.0
epsilon = 0.1

while True:
    next_estimate = (estimate + target) / 2
    if abs(next_estimate - estimate) < epsilon:
        estimate = next_estimate
        break
    estimate = next_estimate

print(estimate)

Sub ConvergenceExample()
    Dim estimate As Double, target As Double
    Dim nextEstimate As Double, epsilon As Double

    estimate = 1000#
    target = 250#
    epsilon = 0.1

    Do
        nextEstimate = (estimate + target) / 2
        If Abs(nextEstimate - estimate) < epsilon Then
            estimate = nextEstimate
            Exit Do
        End If
        estimate = nextEstimate
    Loop

    Debug.Print estimate
End Sub

let estimate = 1000.0;
const target = 250.0;
const epsilon = 0.1;

while (true) {
    const nextEstimate = (estimate + target) / 2;
    if (Math.abs(nextEstimate - estimate) < epsilon) {
        estimate = nextEstimate;
        break;
    }
    estimate = nextEstimate;
}

console.log(estimate);

result = 0.1 + 0.2
print(result)
print(result == 0.3)

Sub FloatingPointErrorExample()
    Dim result As Double

    result = 0.1 + 0.2
    Debug.Print result
    Debug.Print result = 0.3
End Sub

const result = 0.1 + 0.2;
console.log(result);
console.log(result === 0.3);

import math

a = 0.1 + 0.2
b = 0.3

epsilon = 1e-12
print(math.isclose(a, b, rel_tol=0.0, abs_tol=epsilon))

Sub ApproximateCompareExample()
    Dim a As Double, b As Double
    Dim epsilon As Double

    a = 0.1 + 0.2
    b = 0.3
    epsilon = 0.000000000001

    Debug.Print Abs(a - b) <= epsilon
End Sub

const a = 0.1 + 0.2;
const b = 0.3;
const epsilon = 1e-12;

console.log(Math.abs(a - b) <= epsilon);

actual = (0.1 + 0.2) * 100
expected = 30.0
epsilon = 1e-10

assert abs(actual - expected) <= epsilon
print("OK")

Sub NumericTestExample()
    Dim actual As Double, expected As Double
    Dim epsilon As Double

    actual = (0.1 + 0.2) * 100
    expected = 30#
    epsilon = 0.0000000001

    Debug.Assert Abs(actual - expected) <= epsilon
    Debug.Print "OK"
End Sub

const actual = (0.1 + 0.2) * 100;
const expected = 30.0;
const epsilon = 1e-10;

console.assert(Math.abs(actual - expected) <= epsilon);
console.log("OK");

list_a = [1, 2, 3]
list_b = [2, 3, 4]

# 二重探索：O(n × m)
slow_result = [x for x in list_a if any(x == y for y in list_b)]

# Setを使った探索：Set作成 O(m) + 探索 O(n)（平均的）
set_b = set(list_b)
fast_result = [x for x in list_a if x in set_b]

print(slow_result)
print(fast_result)

Sub OrderExample()
    Dim listA As Variant, listB As Variant
    Dim dict As Object
    Dim slowResult As Collection, fastResult As Collection
    Dim i As Long, j As Long

    listA = Array(1, 2, 3)
    listB = Array(2, 3, 4)
    Set slowResult = New Collection
    Set fastResult = New Collection

    ' 二重探索：O(n × m)
    For i = LBound(listA) To UBound(listA)
        For j = LBound(listB) To UBound(listB)
            If listA(i) = listB(j) Then
                slowResult.Add listA(i)
                Exit For
            End If
        Next j
    Next i

    ' Dictionaryを使った探索：作成 O(m) + 探索 O(n)（平均的）
    Set dict = CreateObject("Scripting.Dictionary")
    For j = LBound(listB) To UBound(listB)
        dict(listB(j)) = True
    Next j

    For i = LBound(listA) To UBound(listA)
        If dict.Exists(listA(i)) Then fastResult.Add listA(i)
    Next i

    For i = 1 To slowResult.Count
        Debug.Print slowResult(i)
    Next i
    For i = 1 To fastResult.Count
        Debug.Print fastResult(i)
    Next i
End Sub

const listA = [1, 2, 3];
const listB = [2, 3, 4];

// 二重探索：O(n × m)
const slowResult = listA.filter(x => listB.some(y => x === y));

// Setを使った探索：Set作成 O(m) + 探索 O(n)（平均的）
const setB = new Set(listB);
const fastResult = listA.filter(x => setB.has(x));

console.log(slowResult);
console.log(fastResult);

master_codes = {"P001", "P002", "P003"}
sales_codes = ["P001", "P999", "P002", "P888"]

unknown = [code for code in sales_codes if code not in master_codes]
print(unknown)

Sub MasterLookupExample()
    Dim master As Object
    Dim sales As Variant
    Dim code As Variant

    Set master = CreateObject("Scripting.Dictionary")
    master("P001") = True
    master("P002") = True
    master("P003") = True

    sales = Array("P001", "P999", "P002", "P888")

    For Each code In sales
        If Not master.Exists(code) Then Debug.Print code
    Next code
End Sub

const masterCodes = new Set(["P001", "P002", "P003"]);
const salesCodes = ["P001", "P999", "P002", "P888"];

const unknown = salesCodes.filter(code => !masterCodes.has(code));
console.log(unknown);

data = [10, 20, 30, 40, 50, 60, 70, 80, 90, 100]
target = 70
left, right = 0, len(data) - 1

while left <= right:
    mid = (left + right) // 2
    if data[mid] == target:
        print(mid)
        break
    elif data[mid] < target:
        left = mid + 1
    else:
        right = mid - 1

Sub BinarySearchExample()
    Dim data As Variant
    Dim target As Long
    Dim left As Long, right As Long, mid As Long

    data = Array(10, 20, 30, 40, 50, 60, 70, 80, 90, 100)
    target = 70
    left = LBound(data)
    right = UBound(data)

    Do While left <= right
        mid = (left + right) \ 2

        If data(mid) = target Then
            Debug.Print mid
            Exit Do
        ElseIf data(mid) < target Then
            left = mid + 1
        Else
            right = mid - 1
        End If
    Loop
End Sub

const data = [10, 20, 30, 40, 50, 60, 70, 80, 90, 100];
const target = 70;
let left = 0;
let right = data.length - 1;

while (left <= right) {
    const mid = Math.floor((left + right) / 2);

    if (data[mid] === target) {
        console.log(mid);
        break;
    } else if (data[mid] < target) {
        left = mid + 1;
    } else {
        right = mid - 1;
    }
}

import itertools
perms = list(itertools.permutations([1, 2, 3], 2))
print(perms)

Sub PermutationExample()
    Dim i As Long, j As Long

    For i = 1 To 3
        For j = 1 To 3
            If i <> j Then
                Debug.Print i & "," & j
            End If
        Next j
    Next i
End Sub

const items = [1, 2, 3];
const permutations = [];

for (const a of items) {
    for (const b of items) {
        if (a !== b) permutations.push([a, b]);
    }
}

console.log(permutations);

import itertools

items = ["A", "B", "C", "D"]

for combination in itertools.combinations(items, 2):
    print(combination)

Sub CombinationExample()
    Dim items As Variant
    Dim i As Long, j As Long

    items = Array("A", "B", "C", "D")

    For i = LBound(items) To UBound(items)
        For j = i + 1 To UBound(items)
            Debug.Print items(i) & "," & items(j)
        Next j
    Next i
End Sub

const items = ["A", "B", "C", "D"];

for (let i = 0; i < items.length; i++) {
    for (let j = i + 1; j < items.length; j++) {
        console.log([items[i], items[j]]);
    }
}

import itertools

os_list = ["Windows", "macOS"]
browsers = ["Chrome", "Edge"]
roles = ["admin", "user"]

for case in itertools.product(os_list, browsers, roles):
    print(case)

Sub TestMatrixExample()
    Dim osList As Variant, browsers As Variant, roles As Variant
    Dim i As Long, j As Long, k As Long

    osList = Array("Windows", "macOS")
    browsers = Array("Chrome", "Edge")
    roles = Array("admin", "user")

    For i = 0 To 1
        For j = 0 To 1
            For k = 0 To 1
                Debug.Print osList(i), browsers(j), roles(k)
            Next k
        Next j
    Next i
End Sub

const osList = ["Windows", "macOS"];
const browsers = ["Chrome", "Edge"];
const roles = ["admin", "user"];

for (const os of osList) {
    for (const browser of browsers) {
        for (const role of roles) {
            console.log([os, browser, role]);
        }
    }
}

total, spam, spam_and_keyword = 1000, 100, 80
prob_keyword_given_spam = spam_and_keyword / spam
print(prob_keyword_given_spam)

Sub ProbabilityExample()
    Dim total As Long
    Dim spam As Long
    Dim spamAndKeyword As Long
    Dim probKeywordGivenSpam As Double

    total = 1000
    spam = 100
    spamAndKeyword = 80

    probKeywordGivenSpam = spamAndKeyword / spam

    MsgBox probKeywordGivenSpam
End Sub

const total = 1000;
const spam = 100;
const spamAndKeyword = 80;
const probKeywordGivenSpam = spamAndKeyword / spam;

console.log(probKeywordGivenSpam);

alert_count = 120
outage_with_alert = 36

p_outage_given_alert = outage_with_alert / alert_count
print(p_outage_given_alert)

Sub ConditionalOutageExample()
    Dim alertCount As Long, outageWithAlert As Long
    Dim probability As Double

    alertCount = 120
    outageWithAlert = 36

    probability = outageWithAlert / alertCount
    Debug.Print probability
End Sub

const alertCount = 120;
const outageWithAlert = 36;

const pOutageGivenAlert = outageWithAlert / alertCount;
console.log(pOutageGivenAlert);

import random
val = random.gauss(0, 1)
print(val)

Function NormalRandom() As Double
    Dim u1 As Double, u2 As Double

    u1 = Rnd
    Do While u1 = 0
        u1 = Rnd
    Loop
    u2 = Rnd

    NormalRandom = Sqr(-2# * Log(u1)) * Cos(2# * 3.14159265358979# * u2)
End Function

Sub NormalSampleExample()
    Randomize
    Debug.Print NormalRandom()
End Sub

function normalApprox() {
    let u1 = Math.random();
    while (u1 === 0) {
        u1 = Math.random();
    }
    const u2 = Math.random();
    return Math.sqrt(-2 * Math.log(u1)) * Math.cos(2 * Math.PI * u2);
}

console.log(normalApprox());

import math

n = 10
k = 6
p = 0.5

prob = math.comb(n, k) * p ** k * (1 - p) ** (n - k)
print(prob)

Function Combination(n As Long, k As Long) As Double
    Dim i As Long
    Dim result As Double

    result = 1
    For i = 1 To k
        result = result * (n - k + i) / i
    Next i

    Combination = result
End Function

Sub BinomialExample()
    Dim n As Long, k As Long
    Dim p As Double
    Dim prob As Double

    n = 10
    k = 6
    p = 0.5

    prob = Combination(n, k) * p ^ k * (1 - p) ^ (n - k)
    Debug.Print prob
End Sub

function combination(n, k) {
    let result = 1;
    for (let i = 1; i <= k; i++) {
        result *= (n - k + i) / i;
    }
    return result;
}

const n = 10;
const k = 6;
const p = 0.5;

const prob = combination(n, k) * p ** k * (1 - p) ** (n - k);
console.log(prob);

latencies = [120, 150, 180, 220, 260, 310, 480, 520, 800, 1200]
threshold = 500

slow_rate = sum(x > threshold for x in latencies) / len(latencies)
print(slow_rate)

Sub LatencyDistributionExample()
    Dim latencies As Variant
    Dim threshold As Double
    Dim slowCount As Long, i As Long

    latencies = Array(120, 150, 180, 220, 260, 310, 480, 520, 800, 1200)
    threshold = 500

    For i = LBound(latencies) To UBound(latencies)
        If latencies(i) > threshold Then slowCount = slowCount + 1
    Next i

    Debug.Print slowCount / (UBound(latencies) + 1)
End Sub

const latencies = [120, 150, 180, 220, 260, 310, 480, 520, 800, 1200];
const threshold = 500;

const slowCount = latencies.filter(x => x > threshold).length;
const slowRate = slowCount / latencies.length;
console.log(slowRate);

import scipy.stats as stats
a = [1, 2, 3]
b = [2, 3, 4]
t_stat, p_val = stats.ttest_ind(a, b, equal_var=False)
print(p_val)

Sub TTestExample()
    Dim a As Variant
    Dim b As Variant
    Dim pVal As Double

    a = Array(1, 2, 3)
    b = Array(2, 3, 4)

    pVal = WorksheetFunction.TTest(a, b, 2, 3)
    MsgBox pVal
End Sub

※ WorksheetFunction.TTest はExcelのワークシート関数をVBAから呼び出す方法です。実行にはExcel環境が必要です。

function mean(values) {
    return values.reduce((sum, x) => sum + x, 0) / values.length;
}

function variance(values) {
    const m = mean(values);
    return values.reduce((sum, x) => sum + (x - m) ** 2, 0) / (values.length - 1);
}

const a = [1, 2, 3];
const b = [2, 3, 4];
const t = (mean(a) - mean(b)) /
    Math.sqrt(variance(a) / a.length + variance(b) / b.length);

console.log(t);

from scipy import stats

old_api = [420, 390, 450, 410, 430]
new_api = [360, 340, 380, 355, 370]

t_stat, p_value = stats.ttest_ind(old_api, new_api, equal_var=False)
print(t_stat, p_value)

Sub ApiSpeedTTestExample()
    Dim oldApi As Variant, newApi As Variant
    Dim pVal As Double

    oldApi = Array(420, 390, 450, 410, 430)
    newApi = Array(360, 340, 380, 355, 370)

    pVal = WorksheetFunction.TTest(oldApi, newApi, 2, 3)
    Debug.Print pVal
End Sub

function mean(values) {
    return values.reduce((sum, x) => sum + x, 0) / values.length;
}

function variance(values) {
    const m = mean(values);
    return values.reduce((sum, x) => sum + (x - m) ** 2, 0) / (values.length - 1);
}

const oldApi = [420, 390, 450, 410, 430];
const newApi = [360, 340, 380, 355, 370];
const t = (mean(oldApi) - mean(newApi)) /
    Math.sqrt(variance(oldApi) / oldApi.length + variance(newApi) / newApi.length);

console.log(t);

※Python版とVBA版ではp値を求めていますが、JavaScript版ではライブラリを使わず計算過程を示すため、Welchのt統計量までを求めています。

import math
p = 0.25
information = -math.log2(p)
print(information)

Sub SelfInformationExample()
    Dim p As Double, info As Double
    p = 0.25
    info = -Log(p) / Log(2)
    MsgBox info
End Sub

const p = 0.25;
const information = -Math.log2(p);
console.log(information);

import math

total_logs = 10000
security_alerts = 10
p = security_alerts / total_logs

information = -math.log2(p)
print(information)

Sub RareLogInformationExample()
    Dim totalLogs As Long, securityAlerts As Long
    Dim p As Double, information As Double

    totalLogs = 10000
    securityAlerts = 10
    p = securityAlerts / totalLogs

    information = -Log(p) / Log(2)
    Debug.Print information
End Sub

const totalLogs = 10000;
const securityAlerts = 10;
const p = securityAlerts / totalLogs;

const information = -Math.log2(p);
console.log(information);

p_a = 0.01
p_b_given_a = 0.9
p_b = 0.05
p_a_given_b = (p_b_given_a * p_a) / p_b
print(p_a_given_b)

Sub BayesExample()
    Dim pa As Double, pba As Double, pb As Double
    pa = 0.01: pba = 0.9: pb = 0.05
    MsgBox (pba * pa) / pb
End Sub

const pA = 0.01;
const pBGivenA = 0.9;
const pB = 0.05;
const pAGivenB = (pBGivenA * pA) / pB;

console.log(pAGivenB);

p_outage = 0.01
p_alert_given_outage = 0.90
p_alert_given_normal = 0.05

p_alert = (
    p_alert_given_outage * p_outage
    + p_alert_given_normal * (1 - p_outage)
)
p_outage_given_alert = p_alert_given_outage * p_outage / p_alert

print(p_outage_given_alert)

Sub AlertBayesExample()
    Dim pOutage As Double
    Dim pAlertGivenOutage As Double, pAlertGivenNormal As Double
    Dim pAlert As Double, pOutageGivenAlert As Double

    pOutage = 0.01
    pAlertGivenOutage = 0.9
    pAlertGivenNormal = 0.05

    pAlert = pAlertGivenOutage * pOutage + _
             pAlertGivenNormal * (1 - pOutage)
    pOutageGivenAlert = pAlertGivenOutage * pOutage / pAlert

    Debug.Print pOutageGivenAlert
End Sub

const pOutage = 0.01;
const pAlertGivenOutage = 0.90;
const pAlertGivenNormal = 0.05;

const pAlert =
    pAlertGivenOutage * pOutage +
    pAlertGivenNormal * (1 - pOutage);
const pOutageGivenAlert = pAlertGivenOutage * pOutage / pAlert;

console.log(pOutageGivenAlert);

x = 10.0
lr = 0.1
for _ in range(5):
    grad = 2 * x
    x -= lr * grad
print(x)

Sub OptimizationExample()
    Dim x As Double, lr As Double, i As Long
    x = 10#: lr = 0.1
    For i = 1 To 5
        x = x - lr * (2 * x)
    Next i
    MsgBox x
End Sub

let x = 10.0;
const lr = 0.1;

for (let i = 0; i < 5; i++) {
    const grad = 2 * x;
    x -= lr * grad;
}

console.log(x);

batch_size = 20.0
learning_rate = 0.1

for _ in range(10):
    # cost = (batch_size - 64)^2 + 100
    gradient = 2 * (batch_size - 64)
    batch_size -= learning_rate * gradient

print(batch_size)

Sub BatchSizeTuningExample()
    Dim batchSize As Double, learningRate As Double
    Dim gradient As Double, i As Long

    batchSize = 20#
    learningRate = 0.1

    For i = 1 To 10
        gradient = 2 * (batchSize - 64)
        batchSize = batchSize - learningRate * gradient
    Next i

    Debug.Print batchSize
End Sub

let batchSize = 20.0;
const learningRate = 0.1;

for (let i = 0; i < 10; i++) {
    const gradient = 2 * (batchSize - 64);
    batchSize -= learningRate * gradient;
}

console.log(batchSize);

import numpy as np
matrix = np.array([[1, 2], [3, 4]])
vector = np.array([1, 1])
print(np.dot(matrix, vector))

Sub MatrixVectorMultiplyExample()
    Dim matrix(1 To 2, 1 To 2) As Double
    Dim vector(1 To 2) As Double
    Dim result(1 To 2) As Double

    matrix(1, 1) = 1#: matrix(1, 2) = 2#
    matrix(2, 1) = 3#: matrix(2, 2) = 4#

    vector(1) = 1#: vector(2) = 1#

    result(1) = matrix(1, 1) * vector(1) + matrix(1, 2) * vector(2)
    result(2) = matrix(2, 1) * vector(1) + matrix(2, 2) * vector(2)

    Debug.Print result(1)
    Debug.Print result(2)
End Sub

const matrix = [[1, 2], [3, 4]];
const vector = [1, 1];

const result = matrix.map(row =>
    row.reduce((sum, value, i) => sum + value * vector[i], 0)
);

console.log(result);

import numpy as np

scale = np.array([[0.5, 0.0], [0.0, 0.5]])
point = np.array([800, 600])

new_point = scale @ point
print(new_point)

Sub ImageScaleExample()
    Dim x As Double, y As Double
    Dim scale As Double
    Dim newX As Double, newY As Double

    x = 800#: y = 600#
    scale = 0.5

    newX = scale * x + 0 * y
    newY = 0 * x + scale * y

    Debug.Print newX, newY
End Sub

const scale = [[0.5, 0.0], [0.0, 0.5]];
const point = [800, 600];

const newPoint = scale.map(row =>
    row.reduce((sum, value, i) => sum + value * point[i], 0)
);

console.log(newPoint);

import numpy as np

A = np.array([[2, 1], [1, 2]], dtype=float)
v = np.array([1, 1], dtype=float)
lam = 3.0

print(A @ v)
print(lam * v)
print(np.allclose(A @ v, lam * v))

Sub EigenvectorCheckExample()
    Dim a11 As Double, a12 As Double
    Dim a21 As Double, a22 As Double
    Dim vx As Double, vy As Double, lambda As Double
    Dim av1 As Double, av2 As Double

    a11 = 2#: a12 = 1#
    a21 = 1#: a22 = 2#
    vx = 1#: vy = 1#: lambda = 3#

    av1 = a11 * vx + a12 * vy
    av2 = a21 * vx + a22 * vy

    Debug.Print av1, av2
    Debug.Print lambda * vx, lambda * vy
    Debug.Print Abs(av1 - lambda * vx) < 0.000000000001 And _
                Abs(av2 - lambda * vy) < 0.000000000001
End Sub

const A = [[2, 1], [1, 2]];
const v = [1, 1];
const lambda = 3;

const Av = [
    A[0][0] * v[0] + A[0][1] * v[1],
    A[1][0] * v[0] + A[1][1] * v[1]
];
const lambdaV = v.map(x => lambda * x);

console.log(Av);
console.log(lambdaV);
console.log(Av.every((x, i) => Math.abs(x - lambdaV[i]) < 1e-12));

import numpy as np

A = np.array([[2, 1], [1, 2]], dtype=float)
v = np.array([1.0, 0.2])

for _ in range(20):
    v = A @ v
    v = v / np.linalg.norm(v)

print(v)

Sub PowerIterationExample()
    Dim a11 As Double, a12 As Double
    Dim a21 As Double, a22 As Double
    Dim x As Double, y As Double, nx As Double, ny As Double
    Dim norm As Double, i As Long

    a11 = 2#: a12 = 1#
    a21 = 1#: a22 = 2#
    x = 1#: y = 0.2

    For i = 1 To 20
        nx = a11 * x + a12 * y
        ny = a21 * x + a22 * y
        norm = Sqr(nx * nx + ny * ny)
        x = nx / norm
        y = ny / norm
    Next i

    Debug.Print x, y
End Sub

const A = [[2, 1], [1, 2]];
let v = [1, 0.2];

for (let i = 0; i < 20; i++) {
    const x = A[0][0] * v[0] + A[0][1] * v[1];
    const y = A[1][0] * v[0] + A[1][1] * v[1];
    const norm = Math.hypot(x, y);
    v = [x / norm, y / norm];
}

console.log(v);

import numpy as np

transition = np.array([
    [0.0, 0.5, 1.0],
    [0.5, 0.0, 0.0],
    [0.5, 0.5, 0.0],
])
rank = np.array([1/3, 1/3, 1/3])

for _ in range(20):
    rank = transition @ rank

print(rank)

Sub PageRankLikeExample()
    Dim m(1 To 3, 1 To 3) As Double
    Dim rank(1 To 3) As Double, nextRank(1 To 3) As Double
    Dim i As Long, j As Long, k As Long

    m(1, 1) = 0: m(1, 2) = 0.5: m(1, 3) = 1
    m(2, 1) = 0.5: m(2, 2) = 0: m(2, 3) = 0
    m(3, 1) = 0.5: m(3, 2) = 0.5: m(3, 3) = 0
    rank(1) = 1 / 3: rank(2) = 1 / 3: rank(3) = 1 / 3

    For k = 1 To 20
        For i = 1 To 3
            nextRank(i) = 0
            For j = 1 To 3
                nextRank(i) = nextRank(i) + m(i, j) * rank(j)
            Next j
        Next i
        For i = 1 To 3
            rank(i) = nextRank(i)
        Next i
    Next k

    Debug.Print rank(1), rank(2), rank(3)
End Sub

const transition = [
    [0.0, 0.5, 1.0],
    [0.5, 0.0, 0.0],
    [0.5, 0.5, 0.0]
];
let rank = [1 / 3, 1 / 3, 1 / 3];

for (let k = 0; k < 20; k++) {
    rank = transition.map(row =>
        row.reduce((sum, value, i) => sum + value * rank[i], 0)
    );
}

console.log(rank);

import numpy as np
v1 = np.array([1, 2, 3])
v2 = np.array([2, 4, 6])
dot_prod = np.dot(v1, v2)
print(dot_prod)

Sub DotProductExample()
    Dim v1(0 To 2) As Double, v2(0 To 2) As Double
    Dim dotProd As Double

    v1(0) = 1: v1(1) = 2: v1(2) = 3
    v2(0) = 2: v2(1) = 4: v2(2) = 6

    dotProd = v1(0) * v2(0) + v1(1) * v2(1) + v1(2) * v2(2)
    MsgBox dotProd
End Sub

const v1 = [1, 2, 3];
const v2 = [2, 4, 6];

const dotProd = v1.reduce((sum, value, i) => sum + value * v2[i], 0);
console.log(dotProd);

import math

query = [0.8, 0.1, 0.4]
documents = {
    "manual": [0.9, 0.1, 0.3],
    "invoice": [0.1, 0.9, 0.2],
    "faq": [0.7, 0.2, 0.5],
}

def cosine(a, b):
    dot = sum(x * y for x, y in zip(a, b))
    norm_a = math.sqrt(sum(x * x for x in a))
    norm_b = math.sqrt(sum(x * x for x in b))
    return dot / (norm_a * norm_b)

for name, vector in documents.items():
    print(name, cosine(query, vector))

Function CosineSimilarity(a As Variant, b As Variant) As Double
    Dim i As Long
    Dim dot As Double, normA As Double, normB As Double

    For i = LBound(a) To UBound(a)
        dot = dot + a(i) * b(i)
        normA = normA + a(i) * a(i)
        normB = normB + b(i) * b(i)
    Next i

    CosineSimilarity = dot / (Sqr(normA) * Sqr(normB))
End Function

Sub VectorSearchExample()
    Dim query As Variant
    query = Array(0.8, 0.1, 0.4)

    Debug.Print "manual", CosineSimilarity(query, Array(0.9, 0.1, 0.3))
    Debug.Print "invoice", CosineSimilarity(query, Array(0.1, 0.9, 0.2))
    Debug.Print "faq", CosineSimilarity(query, Array(0.7, 0.2, 0.5))
End Sub

function cosine(a, b) {
    const dot = a.reduce((sum, x, i) => sum + x * b[i], 0);
    const normA = Math.sqrt(a.reduce((sum, x) => sum + x * x, 0));
    const normB = Math.sqrt(b.reduce((sum, x) => sum + x * x, 0));
    return dot / (normA * normB);
}

const query = [0.8, 0.1, 0.4];
const documents = {
    manual: [0.9, 0.1, 0.3],
    invoice: [0.1, 0.9, 0.2],
    faq: [0.7, 0.2, 0.5]
};

for (const [name, vector] of Object.entries(documents)) {
    console.log(name, cosine(query, vector));
}