iT邦幫忙

0

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

  • 分享至 

  • xImage

我想透過VBA找出儲存格之間的關係
目前有一堆這樣的資料
https://ithelp.ithome.com.tw/upload/images/20201114/20122398mrBac1sGli.png
每日組粗框代表他是同一組,但是在A欄中會發現123456出現了"兩組"分別在A2:A6&A22,第一組(A2:C6)是同一人,同月份,2種或多種假期類別,假期申放時間先連續後單日,第二組(A22:C22)同一人同月份,1種假期類別,假期申放時間單日。
我如何透過VBA來判斷儲存格之間關係並生成新資訊?
如下圖,我在A欄輸入上圖的員工號碼,生成B欄的結果,結果我會用來改檔案名稱.
https://ithelp.ithome.com.tw/upload/images/20201114/20122398VG4D42ZSXR.png
其實有點複雜,所以請各大神幫幫忙,跪謝!

看更多先前的討論...收起先前的討論...
ccutmis iT邦高手 2 級 ‧ 2020-11-14 15:17:13 檢舉
改用Python處理會簡單很多,EXCEL部份就是把它匯出成txt(tab分隔的文字檔),
然後在Python裡面讀取txt作判斷整理,整理結果再存成另一個txt(tab分隔的文字檔,可以用EXCEL直接開啟)。
題目看不懂
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 版的參考範例可以另外發問 相信會有很多利害的邦友協助你 我也可以提供個人土法煉鋼的方法
VBA在新版的OFFICE中也是變成禁用或提問啟用,而且警語也是相當駭人,如果是我,我會用C#來處理EXCEL表格的讀取及輸出,效能會比VBA快上許多且彈性大,至於Python我比較少用,原因是Python直譯環境要個別安裝,編譯成EXE也很擁腫肥大.(相對於C#在標準.net framework上編譯檔幾百KB)
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

1 個回答

0
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扯遠了)
總之,希望對您的問題有一些幫助。

AnakinTai iT邦新手 5 級 ‧ 2020-11-18 12:14:08 檢舉

謝謝你的熱心幫助.我會努力研究的

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

不客氣:)

我要發表回答

立即登入回答