Excel按量分配太複雜!關鍵數據不便共享!用VBA拆分為獨立工作薄

2020-12-05 vba雜談

前景提要

隨著時代的不斷發展，現在各行各業都非常強調團隊合作，協同作業，提高生產效率和質量，我們平時進行數據處理，數據校對的過程中也需要這樣的團隊合作，雖然科技在不斷的發展進步，但是有時候數據的錄入受限於場景等因素，還是需要手工操作的，手工錄入就不可避免的涉及輸入錯誤的情況，所以二次的數據核對是非常重要的，但是有時候數據非常的龐大，依靠個人去完成數據核對是非常慢的，但是一些數據又不便於共享出來，那這個時候如何實現團隊效益呢？

場景模擬

這裡我們依然借用我們上節的案例

這是我們的工作數據，裡面記載著所有銷售人員上月的銷售業績，這個時候財務再核對的時候，覺得有必要在進行核對一次，因為這些數據都是手工錄入的，但是發現，僅僅依靠自己想要短時間核對完數據是非常的困難的，而這種關鍵性的數據又不能放到公司共享讓銷售員自行核對，所以只能自己幾個管理層級的同事來幫忙核對，但是總表只有一個，大家公用一個表很容易亂，如果能夠按量拆分成獨立工作薄，一個人完成文檔在匯總就非常方便了

代碼區

來看看這樣的場景該如何用代碼來實現

Sub chai()

Dim rng As Range, sth As Worksheet, BookN As Workbook, pathn$

pathn = ActiveWorkbook.Path

Set sth = ActiveSheet

Set rng = Application.InputBox("請選擇表頭區域", "表頭區域的確定", , , , , , 8)

TitleR = rng.Rows.Count

TitleC = rng.Column

TitleColNum = rng.Columns.Count

num = InputBox("請輸入間隔拆分的行數")

l = ActiveSheet.Cells(Rows.Count, TitleR).End(xlUp).Row

CountR = l - TitleR

For i = TitleR + 1 To l Step num

k = k + 1

Workbooks.Add

Set BookN = ActiveWorkbook

rng.Copy BookN.Worksheets(1).Cells(1, 1)

sth.Activate

Range(Cells(i, TitleC), Cells(i + num - 1, TitleColNum-TitleR+1)).Copy BookN.Worksheets(1).Cells(TitleR + 1, 1)

BookN.SaveAs pathn & "\" & "第" & k & "次拆分"

BookN.Close False

Next i

End Sub

看看代碼的流程

首先依然是需要我們選擇指定的表頭範圍，在進行Excel數據處理的過程中，表頭是非常重要的，因為大家總是喜歡設置各種不同的表頭，雖然看起來很好看，但是等到了數據處理，數據分析的時候，表頭反而成為了硬傷

然後輸入我們要拆分的行數，這裡可以隨意輸入，比方說你有100個數據，你有4個人可以協助你，那就是按照25行進行拆分，當然你可以任性，隨意輸入一個數據20，都可以，不影響結果的，這裡我隨機輸入15

看看結果怎麼樣

拆分完畢，我們來抽檢下，因為15肯定不是平分的，我們直接看最後一個工作薄

數據非常完美，就算是最後一個不夠15個數據，也將剩餘的數據全部生成了一個獨立工作薄

代碼解析

實現了需求之後，我們來看看代碼

Set rng = Application.InputBox("請選擇表頭區域", "表頭區域的確定", , , , , , 8)

TitleR = rng.Rows.Count

TitleC = rng.Column

TitleColNum = rng.Columns.Count

這一段代碼就是幫助我們實現工作表表頭區域的獲取的，同時其中的inputbox函數，也讓我們通過自由靈活的輸入，和程序之間形成了交互，讓整個程序更加的靈活

num = InputBox("請輸入間隔拆分的行數")

l = ActiveSheet.Cells(Rows.Count, TitleR).End(xlUp).Row

有了上面的兩段代碼的輔助之後，我們就可以開始主程序了。

和昨天的方式一樣，這裡我們循環的方式依然是跳躍式的循環，按照拆分的行數來實現循環遍歷

每循環一次就要新建一個工作薄，然後將數據裝入新的工作薄中，並且保存複製重命名

Workbooks.Add'工作薄的創建

Set BookN = ActiveWorkbook

rng.Copy BookN.Worksheets(1).Cells(1, 1)'複製表頭

工作薄的創建就非常的簡單了，在進行工作薄的創建之後，我們順手將表頭複製過來，這樣使得新建的工作薄更加完整

表頭是哪裡？就是我們前面第一個窗體中選擇的區域，我們將它複製給了變量rng

然後我們就需要填充數據區域了。

數據區的構造還是和昨天一樣的，不太清楚的話，可以看看昨天文章的分析非常規路線的工作表拆分之按指定行數拆分工作表

或者是直接看下面的這個圖進行理解

現在就可以將這些獨立的工作薄分發出去，一人負責一部分，核對完了之後，有問題的再發給財務進行修改就可以了，大家都可以在自己的工作崗位進行操作，同時作業，效率提升很多。

相關焦點

  • Excel小技巧:使用VBA,10秒鐘搞定拆分工作表(內附代碼)
    前面我們發布過將多個工作簿中的工作表合併到一個工作表簿中，就有網友提了一個問題，如何講一個工作表拆分成多個工作表，其實實現的方法很多，如果數據少的話，我們直接採用篩選後複製粘貼就可以了，如果數據比較多，或者是日常工作的話，每天這樣複製粘貼，就很麻煩~，或者我們使用透視表也可以。。
  • 使用pandas和openpyxl處理複雜Excel數據
    關於Excel數據處理，很多同學可能使用過Pyhton的pandas模塊，用它可以輕鬆地讀取和轉換Excel數據。但是實際中Excel表格結構可能比較雜亂，數據會分散不同的工作表中，而且在表格中分布很亂，這種情況下啊直接使用pandas就會非常吃力。本文蟲蟲給大家介紹使用pandas和openpyxl讀取這樣的數據的方法。
  • 工作中為什麼有必要學點VBA
    從office97開始，VBA作為MS的標準內置配置，彌補了excel內在的基本函數不足以支持的複雜計算，提供了一種相對輕量級的、所見即所得的解決方案，濤哥在21世紀初因內部工作需要（統計代碼行數記工作量）第一次接觸了VBA，並在以後的工作中多次出色的完成相關任務，甚至給濤嫂編的幾個小程序大大提升了她們的工作效率，還受到了時任行長的讚許，現在想來還歷歷在目。
  • 用excel製作文件管理器,所有版本皆可使用
    Hello，大家好，之前跟大家分享了使用excel中的power query功能製作一個文件管理器，但是很多分析反應自己的excel版本不夠高，無法使用，今天就跟大家分享如何使用宏表函數製作文件管理器，他也是可以實現文件刷新的，這個的操作也不難，下面就讓我們來看下他是如何設置的一、什麼是宏表函數以及FILES函數宏表函數是早期低版本excel的產物
  • 掌握這7條excel函數,自動化生成數據周報上篇
    excel的二八原則曾經在面試時候被問到VLOOKUP和HLOOKUP有什麼區別，我回答的是前者是以列匹配，後者是以行匹配。面試完我一個勁兒後悔沒有回答好，對這個函數不熟悉，回答太簡單。諷刺的是已經過去好幾年，我卻一次未用到過HLOOKUP。所以真的沒必要抱著一本excel大全在那挨個學函數，浪費時間。
  • 懂Excel就能輕鬆入門Python數據分析包pandas(十六):合併數據
    此系列文章收錄在公眾號中：數據大宇宙 > 數據處理 >E-pd經常聽別人說 Python 在數據領域有多厲害，結果學了很長時間，連數據處理都麻煩得要死。後來才發現，原來不是 Python 數據處理厲害，而是他有數據分析神器—— pandas前言本系列上一節說了拆分數據的案例，這次自然是說下怎麼合併數據。
  • 如何快速將阿拉伯數字轉化為中文數字?excel表格輕鬆搞定!
    在工作中，有時候會遇到把阿拉伯數字轉轉換為中文簡體，中文繁體，如果數據很多，而且一時找不到轉換的軟體，試試電腦上的excel吧，一個函數即可搞定。方法一：單元格格式。如下圖所示，按ctrl+1打開設置單元格格式對話框，選擇特殊中的中文小寫數字和中文大寫數字即可把阿拉伯數字轉化為中文簡體字符和中文繁體字符。但是請注意編輯欄，轉化完之後單元格裡的內容實質還是阿拉伯數字，只是顯示效果為中文小寫或者大寫。這種轉換結果是不能把轉換後的文字複製到word表格、微信聊天窗口等。如果想要把單元格裡的「實質」內容也變成中文大小寫，就需要用到函數了。方法二：函數法。
  • 如何將Excel數據表單元格保存為數組,VBA數組進階應用
    No.1在vba編程中，數組應用是一個十分有用的方法。如何把數據表作為數組進行處理，是編程過程中的一個重要技能。學會之後，將大大提高編程技術，也將使數據處理的過程變得更加流暢。No.2上圖為例，把表格內容作為數組值進行保存，然後通過流程編碼對成績進行一個比較判斷，最終得出一個成績等級的判定。
  • Jupyter Notebooks嵌入Excel並使用Python替代VBA宏
    好了，現在你可以使用Excel處理數據，並使用Python處理相同的數據。 將Excel用作用於組織和可視化數據的交互式操作，無縫切換到Python以使用更複雜的功能。　　將Jupyter筆記本用作草稿板，以使用Python代碼。在Jupyter筆記本上完全用Python編寫Excel函數，並進行實時測試。
  • Excel VBA函數篇-3.19大數據時代必備查找技能 萬條數據能奈我何
    前景提要經常看電視或者是一些招聘信息的童鞋，應該就比較熟悉大數據這個概念，大數據簡單的理解就是非常龐大的數據處理，數據量的提升，最直接的結果就是普通的數據處理方法越來越慢了，現在也是推出了很多種針對大數據處理的語言，比方說比較火熱的python，他的pandas模塊，numpy模塊，完全就是為大數據而生的，說到這裡肯定很多童鞋就方了，那麼excel是不是就沒有用處了呢
  • 多年來Excel填報數據的各種不如意,終於現在用這個神器全部都解決了
    另外,要想共享數據,就需要將文件通過微信、釘釘等軟體或者U盤、移動硬碟等硬體傳來傳去,這個過程不僅繁瑣,而且數據的一致性和實時更新都不能保證。還有,Excel無法滿足企業中對於權限管控的需求。並且,Excel不是採用基於資料庫選擇的方式,大家輸入數據的時候隨意粘貼複製,有時候多一個字,有時候少一個字,數據質量就很差。
  • 如何快速核對兩個工作薄的數據是否完全一致
    Hello大家好工作中我們經常會對兩份excel進行核對核對其數據是否完全一致對比表格數據的方法很多vlookup,數據透視表都能達到數據對比的效果但是效率都非常低，今天就跟大家分享一種快速核對表格數據的方法核對上萬個單元格的數據僅需幾分鐘就搞定了先來看下數據，在這裡我們有表1和表
  • excel函數應用:如何快速製作考生座次分配表
    選擇G4單元格，輸入公式「=INT((ROW()-4)/32)+1&"考室"」分配第一個考生的考室號。向下填充公式至記錄結尾，為所有報名的考生分配考室號。函數ROW()用於獲得當前考生數據的行號，由於數據區域的第1行位於工作表的第4行，因此需要將此行號減4。
  • ArcGIS將Excel經緯度數據轉換為shp點數據
    一、操作準備 1.1 軟體 ArcMap10.4.1 1.2 數據 excel數據（全國機場點數據.xls） ★excel數據中要含有經緯度。
  • 快速搞定excel多sheet匯總,表頭順序不一樣也能匯總數據
    hello，大家好，在日常工作中我覺得最令我們抓狂，也是最讓人害怕的excel工作莫過於數據匯總了，當匯總的表格數量比較多，表格的格式又不統一的時候，加班到深夜都有可能，今天就跟大家分享一種多sheet匯總的方法，即使表頭的順序不一致也能匯總數據，堪稱數據匯總神奇，話不多說，讓我們直接開始吧
  • 使用簡單而強大的Excel來進行數據分析
    在本文中，將為大家提供一些在Excel的技巧，這樣可以節省工作或處理數據的時間。這篇文章比較適合那些熱衷於升級其數據分析技能的人們。需要注意的是：如果你認為自己是數據科學領域的大師級人員，那麼你可能覺得這篇文章對你來說不會有任何幫助。對於其他人，我建議你練習這些技巧以對它們有一個具體的了解。
  • excel抓取網頁數據,並實現自動刷新,再也不用傻傻的複製粘貼了
    一、獲取數據首選我們需要新建一個工作薄，打開它，然後點數據功能組，點擊新建查詢，然後選擇從其它源，選擇自網站這樣的話就會彈出一個對話框，將想要提取數據的網址直接複製進去，然後點擊確定，點擊確定後excel會自動的連接計算數據當計算完成後會進入
  • 數據成為要素並參與分配的思考與建議
    文件第六部分「加快培育數據要素市場」，從推進政府數據開放共享、提升社會數據資源價值、加強數據資源整合和安全保護三個方面，闡述了加快培育數據要素市場的具體措施。 黨的十九屆四中全會《關于堅持和完善中國特色社會主義制度 推進國家治理體系和治理能力現代化若干重大問題的決定》首次在正式文件中提及數據可作為生產要素按貢獻參與分配。
  • 數據作為要素參與分配:價值、意義與實現過程
    A:首先，數據參與分配需要良好的法律制度的支持；其次，由於掌握數據內容、數據採集、數據分析等各環節的參與者並不相同，因此在分配時需要兼顧多方的利益，特別是數據採集者、加工者與內容所有者的產權確認；第三，在加強數據共享利用的同時，注重數據權益的保護；最後，組織有關機構為數據參與分配創造良好的氛圍，增強社會對數據作為要素參與分配的認可。