用EXCEL VBA程式，各自單獨建立分公司分頁表，並增設超連結方便索引

因業務需要，有5個分公司(甲~戊)業務要管，想用EXCEL表，

提供給各單位填報回傳資料進行管理，並以分公司名稱 各自單獨建立1個工作表，

同時增設超連結方便索引 。

EXCEL表已先建立好各分公司名稱於A欄位中，如下:
         A
1  分公司名稱  
2  甲分公司
3  乙分公司
4  丙分公司
5  丁分公司
6  戊分公司


藉由AI(ChatGPT)協助建立各自建立分公司獨立分頁，並提供超連結設置，方便檢索

PROMPT提示訊息下法:
EXCEL表中，自A2儲存格起,有多個分公司 放於A欄位中,請提供VBA程式，幫忙建立 分公司分頁 ，並且設置超連結，方便檢索


Sub CreateCompanySheets()
    Dim ws As Worksheet
    Dim company As Range
    
    ' 在第一個工作表中建立一個名為 「 各分公司索引清單」的分頁
    Set ws = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    ws.Name = "各分公司索引清單"
    
    ' 在「 各分公司索引清單」分頁中設置標題
    ws.Range("A1").Value = "分公司"
    
    ' 從A2儲存格起，逐一遍歷分公司名稱
    For Each company In ThisWorkbook.Sheets(1).Range("A2:A" & ThisWorkbook.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row)
        ' 檢查分公司分頁是否已存在
        If Not SheetExists(company.Value) Then
            ' 如果分公司分頁不存在，則建立一個新的分頁
            ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)).Name = company.Value
        End If
        
        ' 在「 各分公司索引清單」分頁中設置超連結
        ws.Hyperlinks.Add Anchor:=ws.Range("A" & ws.Cells(Rows.Count, 1).End(xlUp).Row + 1), _
                          Address:="", _
                          SubAddress:="'" & company.Value & "'!A1", _
                          TextToDisplay:=company.Value
    Next company
    
    ' 回到  第一個工作表
    ThisWorkbook.Sheets(1).Activate

End Sub

Function SheetExists(sheetName As String) As Boolean
    Dim ws As Worksheet
    
    SheetExists = False
    For Each ws In ThisWorkbook.Sheets
        If ws.Name = sheetName Then
            SheetExists = True
            Exit Function
        End If
    Next ws
End Function


請按照以下步驟使用此程式：

STEP1:開啟您的Excel檔案。
STEP2:按下Alt + F11進入VBA編輯器。
STEP3:在VBA編輯器中，插入一個新的模組Module。
STEP4:將上述VBA程式碼，複製並貼上到新的模組Module中。
STEP5:在VBA編輯器中，按下F5運行程式。

VBA程式，將會在您的Excel檔案中建立一個名為「 各分公司索引清單」的分頁，並在該分頁中列出 所有分公司的超連結。點擊超連結即可跳轉到相對應的分公司 分頁 。

將多個不同分公司EXCEL成本花費表,合併成單一EXCEL總表

總公司有許多分公司,甲、乙、丙分公司,各自維護獨立 成本花費EXCEL報表,每月需匯整成單一EXCEL總表中

甲、乙、丙分公司各自有成本花費EXCEL報表,且它們成本花費報表  ,其首欄位都有相同花費成本細項，但欄位放置位置都不一定相同

匯整甲、乙、丙分公司，成本花費EXCEL報表欄位全數欄位有    地點,姓名,加班費,差旅費,雜費,租賃費,其它 等欄位

所以,總公司,每月均需匯整甲、乙、丙分公司之成本花費EXCEL報表,合併成單一 EXCEL成本花費總表

To merge the three Excel files into a single file named "MergeOK.xlsx" in the "總公司" folder which located in the D drive,

藉由AI(ChatGPT)寫合併EXCEL
PROMPT提示訊息下法:
在D槽中有 總公司 資料夾，存放3個EXCEL檔,每份表格的首欄為標題,分別有地點,姓名,加班費,差旅費,雜費,租賃費,其它 欄位,每個表格的首欄標題位置皆不同,請寫PYTHON程式,將3個EXCEL表格,合併成 MergeOK.XLSX


import pandas as pd

# File paths 甲分公司檔名為file1.xlsx 、乙分公司檔名為file2.xlsx、丙分公司檔名為file3.xlsx

folder_path = 'D:/總公司/'
file_names = ['file1.xlsx', 'file2.xlsx', 'file3.xlsx']

# Read each Excel file into a DataFrame
dfs = []
for file_name in file_names:
    file_path = folder_path + file_name
    df = pd.read_excel(file_path)
    dfs.append(df)

# Merge the DataFrames
merged_df = pd.concat(dfs)

# Save the merged DataFrame to a new Excel file
output_file = folder_path + 'MergeOK.xlsx'
merged_df.to_excel(output_file, index=False)


相關查詢:
How to join Excel data from Multiple files?

複数のExcelファイルを1つにまとめる方法？


後記:
合併多個EXCEL檔成單一檔方法很多,可以用EXCEL本身功能,或用VBA、或用PYTHON,如果要改成VBA，本範例是用PYHTON，如要使用VBA，只要將PROMPT提示訊息下法，將上面  請寫PYTHON程式  ，改成 EXCEL VBA程式即可

用PYTHON程式,對Disk Analysis磁碟空間剖析

隨著儲存空間愈做愈大，如果沒有做管理將造成備份時間、儲存空間成本的增加、使用效能下降等，因此對磁碟儲存狀態剖析管理就相對重要，可透過PYTHON程式，對磁碟機進行分析檔案目錄、檔名、修改日期、檔案大小進行分析，將結果產出至OUTPUT.TXT，方能掌控磁碟空間之使用(以下為對D槽進行分析)。

import os
from datetime import datetime, timedelta

def analysis_directory(path):
    with open('OUTPUT.TXT', 'w', encoding='UTF-8') as f:
        for root, dirs, files in os.walk(path):
            for file in files:
                file_path = os.path.join(root, file)
                file_size = os.path.getsize(file_path)
                file_date = os.path.getmtime(file_path)
                file_date_str = datetime.fromtimestamp(file_date).strftime('%Y/%m/%d %H:%M:%S')
                f.write(f'{file_path}\t{file_date_str}\t{file_size}\n')

analysis_directory('d:\\')      # 將 'D:\' 改為您想要剖析的磁碟機路徑

 

備註:

上面程式，測試如有型態錯誤訊息,可做調整

#程式開頭，另再加入此行
import time 

#請將datetime物件，替換成下面time物件

datetime.fromtimestamp(file_date).strftime('%Y/%m/%d %H:%M:%S')

#改用time物件

time.strftime('%Y-%m-%d %H:%M:%S')

########################################################

除上面剖析方式外，可搭配DOS指令 TREE 樹狀圖型結構，快速檢視檔案架構

步驟一: 開始  | 執行 | cmd   ,進入DOS 命令提示列

步驟二:  tree /f d: > d:\TreeView.txt     

(將D槽檔案結構，結果產出至   d:\TreeView.txt     )

 

用AI寫Batch批次檔

因為工作關係,常要以日期流水號建檔,所以每次需開檔案總管後，建立年月日資料夾,開始將工作歸納到此資料夾內，想藉由AI工具(ChatGPT),幫忙寫Batch檔,簡化工作😄

步驟一:

https://chateverywhere.app/zh

步驟二:語音語言，選擇  國語繁體

步驟三: (輸入您想請AI幫忙處理功能後，按下右邊 三角形圖示)

 請寫batch檔,依民國年月日建立資料夾,如遇相同資料夾,則自動+1

步驟四:

請將下面範例保存為一個BATCH批次檔（例如 create_folder.bat），然後執行該檔案，
它將根據當前的民國年月日建立一個資料夾。如果該資料夾已存在，它將自動在資料夾名稱後面加上數字，直到找到一個可用的資料夾名稱為止。

@echo off
setlocal enabledelayedexpansion

REM 取得當前的民國年月日
for /f "tokens=1-3 delims=/" %%a in ("%date%") do (
    set /a year=%%a-1911
    set month=%%b
    set day=%%c
)

REM 建立初始資料夾名稱
set folderName=%year%%month%%day%

REM 檢查資料夾是否已存在，若存在則自動加1
set i=1
:checkFolderExist
if exist "%folderName%" (
    set /a i+=1
    set folderName=%year%%month%%day%_%i%
    goto checkFolderExist
)

REM 建立資料夾
mkdir "%folderName%"

後記:

一、其實這網站,除能寫各種不同語文(如:日文令和年)的PYTHON程式

二、這網站亦能寫教案，您只要提供想要的大綱、教學時間、學生學習等級(國、高中、大學)，並在最後附上其它一個網站參考教案範例，它就能依您的教案需求，並參考您附上的網站參考教案作為參考基準，編寫出符合您的需求的教案

三、突然間覺得... AI儼然已成為生活中不可或缺的小幫手了

AIでPYTHONプログラムを書く

AIでPYTHONプログラムを書く

令和   今日の日付の名前でフォルダを作成し、同じフォルダに出会えば自動的に+1するpythonプログラムを書いてください

ステップ 1
https://chateverywhere.app/zh

ステップ 2
語音語言--> 日本語

ステップ 3  (請輸入訊息)

令和   今日の日付の名前でフォルダを作成し、同じフォルダに出会えば自動的に+1するpythonプログラムを書いてください

ステップ 4
自動的にPYTHONプログラムコードを生成しました。

下記の通りの例をPythonファイルに保存し（例：create_folder.py）、実行すると、
令和の年月日に基づいてフォルダが作成されます。同じ名前のフォルダが既に存在する場合、番号が自動的に追加されます。

import os
import datetime

# 今日の日付を取得
today = datetime.date.today()

# 令和の年を計算
year = today.year - 2018

# フォルダ名を作成
folder_name = f"{year}{today.month:02}{today.day:02}"

# フォルダが存在するかどうかを確認し、存在する場合は番号を追加
i = 1
while os.path.exists(folder_name):
    folder_name = f"{year}{today.month:02}{today.day:02}_{i}"
    i += 1

# フォルダを作成
os.mkdir(folder_name)

jpg2txt圖檔轉換為文字檔(PYTHON)

作業環境前置準備:

 安裝必要性元件(辨識核心元件tesseract-ocr 需下載安裝，外加要辦識之語系)

 

安裝相關套件

 pip install pillow

 pip install pytesseract

 

 PYTHON程式，如下:

# -*- coding: utf-8 -*-
'''
python取圖檔，辨識中文
'''
#'開啟檔案總管 (開啟轉換後之檔案使用)'
import os,sys
import subprocess
import glob
from os import path

from PIL import Image
import pytesseract

#'開啟GUI 取得來源檔(準備欲轉換聲音來源,開啟檔案對話視窗宣告處理)
import tkinter as tk
from tkinter import filedialog

root = tk.Tk()
root.withdraw()

file_path = filedialog.askopenfilename(initialdir = "/",title = "Select file for OCR (選擇欲辨識圖檔轉文字檔)",filetypes = (("JPG files","*.jpg"),("JPEG files","*.jpeg"),("All files","*.*")))

#'預設位址如下,但如安裝不同處,需告訴PYHTON 辨識核心元件在哪，如此方能辦識處理
pytesseract.pytesseract.tesseract_cmd = 'C:/Program Files/Tesseract-OCR/tesseract.exe'

img = Image.open(file_path)
text1 = pytesseract.image_to_string(img, lang='chi_tra')


with open('file.txt', mode = 'w') as f:
    f.write(text1)
    f.close()

#'將剛才圖檔辨識後結果，以檔案總管直接將它開啟查閱
subprocess.Popen('explorer "file.txt"')
    

VLOOKUP 查詢對應資料有誤

發現VLOOKUP回傳對應資料有誤

原因分析:
1.拚字錯誤  ，如:參照對應陣列關鍵字1o4 ，而其查詢來源為 104，
                           參照對應陣列關鍵字,部分字示為全形字元,而其來源全為半形字元
   ,導致欲查詢之關鍵字，與參照對應陣列值回傳錯誤

2.格式不同,如:參照對應陣列關鍵字，欄位之儲存格格式,類別(C):屬性為文字，而其來源欄位之儲存格格式，類別(C):屬性為數值 

3.CELLS 儲存格,內含 看不到多餘的空白字元^^^(例如:NBSP空白字元CHR(160), )，如:參照對應陣列關鍵字104^^^ ，而其來源為 104，原因多了看不到的3個空白字元(NBSP、CR、LF),導致vlookup回傳對應資料錯誤 

解決辦法:
可以透過trim函數或 substitue函數，去除CELLS儲存格多餘空白字元 ，trim(參照對應陣列關鍵字欄位),即可將參照對應陣列關鍵字,去除多餘空白字元.


相關查詢:
https://myblog-johnnyit.blogspot.com/2008/04/vlookup.html