判斷儲存格之間關係並生成新資訊

excel vba vba

AnakinTai 2020-11-14 11:19:22 ‧ 1761 瀏覽

分享至

我想透過VBA找出儲存格之間的關係
目前有一堆這樣的資料

每日組粗框代表他是同一組,但是在A欄中會發現123456出現了"兩組"分別在A2:A6&A22,第一組(A2:C6)是同一人,同月份,2種或多種假期類別,假期申放時間先連續後單日,第二組(A22:C22)同一人同月份,1種假期類別,假期申放時間單日。
我如何透過VBA來判斷儲存格之間關係並生成新資訊？
如下圖，我在A欄輸入上圖的員工號碼,生成B欄的結果,結果我會用來改檔案名稱.

其實有點複雜,所以請各大神幫幫忙,跪謝!

看更多先前的討論...收起先前的討論...

ccutmis iT邦高手 2 級 ‧ 2020-11-14 15:17:13 檢舉

改用Python處理會簡單很多，EXCEL部份就是把它匯出成txt(tab分隔的文字檔)，
然後在Python裡面讀取txt作判斷整理，整理結果再存成另一個txt(tab分隔的文字檔，可以用EXCEL直接開啟)。

paicheng0111 iT邦大師 5 級 ‧ 2020-11-14 15:58:56 檢舉

題目看不懂

AnakinTai iT邦新手 5 級 ‧ 2020-11-15 16:14:51 檢舉

@PAICHENG0111 大大主要是判斷他與上下之間的關係。如a2:a5他們都是123456。c2:c5是同一假期類別。b2:b5是連續日期。所以得出123456 年假 202001～04。但a5等於a6,所以用「，」分隔。c2:c5與c6不相等，所以是c6，b5與b6不是加1的關係，但年月同等。所以用b6的日「日」。最後得出「123456 年假 202001～04，特休 07」。這樣解釋會明白些嗎？

AnakinTai iT邦新手 5 級 ‧ 2020-11-15 16:18:27 檢舉

@ccutmis 大大我知道有python這東西，但不知道是怎麼打開？還是電腦內置？因為vba我知道excel內可以打開。也有自己錄巨集做簡單的處理。

ccutmis iT邦高手 2 級 ‧ 2020-11-15 18:32:36 檢舉

@Anakin Tai:
網路上跟Python相關的新手教程已經多到爆炸我就不自己寫了(事實上我自己也是靠這些教程自學的^^") 比如說這個:
https://www.youtube.com/watch?v=wqRlKVRUV_k&list=PL-g0fdC5RMboYEyt6QS2iLb_1m7QcgfHk

而Python本身是跨平台的，但是作業系統沒有內建，所以你可以在Google搜Python download ，進官方網站下載適合的windows安裝版本(我自己是用Python3.7.9)，安裝前先在C槽根目錄創建一個 Python37的目錄，然後在安裝時記得勾選將Python加入到PATH及選自訂安裝，將安裝目錄指到剛才新增的Python3目錄，然後就是下一步下一步就完成安裝了。安裝好要測試是不是有正常work很簡單，就是開一個cmd，然後輸入python [enter] 看有沒有出現 python程式的直譯對話介面，有的話就是正常運作了。另外最常遇到的就是安裝python套件，比如說要安裝requests套件，就是開cmd 然後輸入 pip install requests [enter] 等它跑一下就完成了，剩下的就是選你適用的純文字編輯器(很多教學都建議用VS code，我自己是用Notepad++)編輯Python文件(.py)跟如何執行py文件了。

如果你能改用python會容易很多，也不影響原本EXCEL作業(從xls生成csv，編輯生成新的csv都可以用python執行)

AnakinTai iT邦新手 5 級 ‧ 2020-11-15 19:19:14 檢舉

Ccutmis 謝謝你。我試看看吧

ccutmis iT邦高手 2 級 ‧ 2020-11-15 19:36:46 檢舉

不客氣有需要 python 版的參考範例可以另外發問相信會有很多利害的邦友協助你我也可以提供個人土法煉鋼的方法

japhenchen iT邦超人 1 級 ‧ 2020-11-16 08:42:46 檢舉

VBA在新版的OFFICE中也是變成禁用或提問啟用，而且警語也是相當駭人，如果是我，我會用C#來處理EXCEL表格的讀取及輸出，效能會比VBA快上許多且彈性大，至於Python我比較少用，原因是Python直譯環境要個別安裝，編譯成EXE也很擁腫肥大.(相對於C#在標準.net framework上編譯檔幾百KB)

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

1 個回答

ccutmis

iT邦高手 2 級 ‧ 2020-11-17 09:40:26

這裡提供您一個使用Python 土法鍊鋼的方式，首先你需要把要處理的工作表匯出成txt文字檔(tab分隔)，
這部份很簡單，就是開啟excel，然後點 "檔案→另存新檔"，檔案類型選 "文字檔(tab字元分隔)"，然後存成 "假期分類.txt"(可用Excel開啟)

底下是我測試時的 "假期分類.txt" 內容:

員工號碼	開始日期	假期種類	備註
123456	1/1/2020	年假	1天
123458	27/1/2020	年假	1天
123456	2/1/2020	年假	1天
123456	7/1/2020	特休	1天
123457	9/1/2020	年假	1天
123457	12/1/2020	年假	1天
123458	20/1/2020	年假	1天
123456	3/1/2020	年假	1天
123458	25/1/2020	年假	1天
123458	26/1/2020	年假	1天
123458	28/1/2020	年假	1天
123456	4/1/2020	年假	1天
123458	29/1/2020	年假	1天
123458	30/1/2020	年假	1天
123458	31/1/2020	年假	1天
123458	1/2/2020	年假	1天
123456	8/2/2020	病假	1天
123460	28/1/2020	事假	1天

再來就是使用Python的部份，你需要先到Python官網下載適合自己OS版本的Python安裝程式，建議安裝3.7以上版本(我目前是用3.7.9)，安裝好之後開個cmd視窗打python[enter]如果出現的是下列內容就表示安裝成功，可正常執行Python了。

Python 3.7.9 (tags/v3.7.9:13c94747c7, Aug 17 2020, 18:58:18) [MSC v.1900 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>>

接著直接關掉cmd視窗，看一下python程式的部份(分析處理 "假期分類.txt" 文字檔並將結果另存為 "假期分類_完成.txt")
main.py

import datetime

""" 逐行讀取文字檔並傳回陣列 """
def read_file_into_list(file_loc,write_mode="r",encode_set="utf-8",split_char="\n"):
	tmp_list=open(file_loc,write_mode,encoding=encode_set).read().split(split_char)
	out_list=[]
	for i in tmp_list:
		i_list=i.split("\t")
		if len(i_list)==4 and i_list[1].find("/")>-1:
			#將日期格式調為"年月日"(目前是"日月年")
			date_split=i_list[1].split("/")
			i_list[1]=date_split[2]+"/"+fix_date_0(date_split[1])+"/"+fix_date_0(date_split[0])
			out_list.append(i_list)
	return out_list

""" 將陣列元素逐行寫入文字檔 """
def write_file_from_list(file_loc,tmp_list=[],write_mode="w+",encode_set="utf-8"):
	if len(tmp_list)!=0:
		with open(file_loc,write_mode,encoding=encode_set) as f:
			for i in tmp_list:
				f.writelines(i+'\n')
		f.close()
		print("[ OK ] 陣列寫入文字檔 "+get_file_fullname(file_loc)+" 完成!  文件編碼:"+encode_set+"")
	else:
		print("[ XX ] 陣列寫入文字檔 "+get_file_fullname(file_loc)+" 失敗!")
		return False

""" 取得完整檔名(不含路徑) """
def get_file_fullname(input_str1):
	return input_str1.split("\\")[-1]

def fix_date_0(arg):
	return arg.strip().rjust(2,"0")

def day_of_year(day_str):
	return datetime.date(int(day_str[0:4]),int(day_str[5:7]),int(day_str[8:10])).timetuple().tm_yday

def get_weekday(day_str):
	return datetime.date(int(day_str[0:4]),int(day_str[5:7]),int(day_str[8:10])).weekday()

def reduce_d_str(tmp_str):
	return tmp_str[0:10]+"~"+tmp_str[-5:] if len(tmp_str)>10 else tmp_str

def print_row(tmpid,tmp_days,tmp_events):
	if tmp_days.find(',')<0: # 只有一個日期
		return tmpid+'\t'+tmpid+' '+tmp_events+' '+tmp_days+'\t'+'同一人同月份,1種假期類別,申請時間單日'
	else:
		tmp_events_list=tmp_events.split(',')
		events_collection_list=[]
		for i in tmp_events_list:
			if i not in events_collection_list: events_collection_list.append(i)
		#print(tmp_events_list)
		tmp_days_list=tmp_days.split(',')
		#print(tmp_days_list)
		#把休假日期跟種類轉成陣列 用來進行分析 例如
		if len(events_collection_list)==1: #只有一個種類
			month_collect=[]
			for i in tmp_days_list:
				#抓取月份字串，如果month_collect內沒有就把它放入moth_collect
				tmp_mon=i[6:8]
				if tmp_mon not in month_collect:
					month_collect.append(tmp_mon)
			mon_str="同月份"
			if len(month_collect)>1:
				mon_str="不同月份"
			return tmpid+'\t'+tmpid+' '+events_collection_list[0]+' '+reduce_d_str(tmp_days)+'\t'+'同一人'+mon_str+',1種假期類別,申請時間連續多個單日'
		else:
			result_txt=""
			month_collect=[]
			for i in events_collection_list:
				if result_txt!="":
					result_txt+=","
				result_txt+=i
				tmp_txt=""
				for j in range(0,len(tmp_events_list)):
					#抓取月份字串，如果month_collect內沒有就把它放入moth_collect
					tmp_mon=tmp_days_list[j][6:8]
					if tmp_mon not in month_collect:
						month_collect.append(tmp_mon)
					if tmp_events_list[j]==i:
						if tmp_txt!="":
							tmp_txt+=","
						tmp_txt+=tmp_days_list[j]
				if tmp_txt.find(",")!=-1:
					tmp_txt=reduce_d_str(tmp_txt)
				result_txt+= tmp_txt
			mon_str="同月份"
			if len(month_collect)>1:
				mon_str="不同月份"
			result_txt+="\t同一人"+mon_str+","+str(len(events_collection_list))+"種假期類別,申請時間連續多個單日"
			
			return tmpid+'\t'+tmpid+' '+result_txt


if __name__ == "__main__":
	source_txt="假期分類.txt"
	final_txt="假期分類_完成.txt"
	# 讀取"假期分類.txt"並轉成陣列
	list_source=read_file_into_list(source_txt,encode_set="Big5")
	result_list=[]
	result_list.append("員工號碼 \t 生成結果 \t 解釋")
	id_list=[]
	for i in list_source:
		if i[0] not in id_list:
			id_list.append(i[0])
	#print('用戶編號 陣列 : ',id_list,'\n')
	#用日期欄做排序
	list_source=sorted(list_source,key=lambda x: x[1])
	day_box=[]
	event_box=[]
	for i in id_list:
		current_id=i
		tmp_row=[]
		for j in list_source:
			if j[0]==current_id:
				tmp_row.append(j)
		#print('userid: ',i,"  TotalRow: ",len(tmp_row))
		days_str=""
		event_str=""
		last_date=""
		for k in range(0,len(tmp_row)):
			#print(tmp_row[k])
			if days_str=="": #第一筆
				last_date=tmp_row[k][1]
				days_str+=tmp_row[k][1]
				event_str+=tmp_row[k][2]
			elif day_of_year(last_date)+1==day_of_year(tmp_row[k][1]) or (get_weekday(last_date)==5 and get_weekday(tmp_row[k][1])):
				days_str+=","+tmp_row[k][1]
				event_str+=","+tmp_row[k][2]
				last_date=tmp_row[k][1]
			else:
				result_list.append(print_row(i,days_str,event_str))
				#print(i+'\t'+reduce_d_str(days_str),"::",event_str)
				days_str=tmp_row[k][1]
				event_str=tmp_row[k][2]
				last_date=tmp_row[k][1]
		#最末筆
		result_list.append(print_row(i,days_str,event_str))
		#print("-----------")
	#將 result_list 轉存為 txt
	write_file_from_list(final_txt,result_list,encode_set="Big5")

執行結果:
"假期分類_完成.txt"內容

員工號碼 	 生成結果 	 解釋
123456	123456 年假2020/01/01~01/04,特休2020/01/07	同一人同月份,2種假期類別,申請時間連續多個單日
123456	123456 病假 2020/02/08	同一人同月份,1種假期類別,申請時間單日
123458	123458 年假 2020/01/20	同一人同月份,1種假期類別,申請時間單日
123458	123458 年假 2020/01/25~02/01	同一人不同月份,1種假期類別,申請時間連續多個單日
123457	123457 年假 2020/01/09	同一人同月份,1種假期類別,申請時間單日
123457	123457 年假 2020/01/12	同一人同月份,1種假期類別,申請時間單日
123460	123460 事假 2020/01/28	同一人同月份,1種假期類別,申請時間單日

以下是參考源碼下載:
http://www.web3d.url.tw/ITHELP/py_xls_txt_analyzer.zip

對了，編輯.py文件我個人推薦 Notepad++ 免費好用佔用資源少

Excel能處理的話就直接在Excel處理自然比較省事，但是有時如果條件設太複雜用Excel內建功能會變得更難，以這邊的例子用Python處理就簡單很多(當然你設的條件愈複雜，在程式裡就要花更多工夫去處理這個不管是Python或VBA都是一樣的)。
一般常見的python excel處理會用到pandas套件，考慮到樓主可能還沒碰過Python，我還是先把問題單純化，從最基本的文字檔讀入python轉陣列，在python中跑廻圈作條件判斷，再將結果另存文字檔。
等你熟悉Python的程式之後，一些例行的工作也可以弄成自動化處理(例如寫一個robot，把每天例行工作寫成十個腳本，每天固定時間去執行它，然後等下班._." sorry扯遠了)
總之，希望對您的問題有一些幫助。