EXCEL 多欄表格橫轉直 cross table to list 交叉表格轉為列表 Unpivot Table 免巨集

雖然EXCEL內建的簡單的資料橫直對調的功能，但是資料的呈現不如人意

另外一種方法是用Unpivot Table功能(翻譯寫逆透視表格，我找不到別的翻譯)，有興趣的話可以google看看，不過這樣只是把資料的值轉過去，連動是做不到的

我爬文發現還有最新的函數tocol可以輕鬆做到想要的樣子，但是限定要OFFICE365，OFFICE2021沒有，所以還是想要研究出用函數直接處理的方式。

最後我終於想出來了，不過僅限於固定種類，筆數可以無限增加連動，但如果增加種類要再改過函數。

 

如圖，我想把原本姓名跟成績交錯的表，轉成列表

我使用的方法是這樣，首先要增加一個欄位【編號】做索引

然後因為有四個科目，所以我在新生成的結果要有的第一個數列是1111222233334444

然後姓名的部分很好處理，用VLOOKUP靠編號去找A:B的第二欄就好

科目的部分，則是要找出編號那一列的第3 4 5 6欄，成績也是

所以下一個要生成的就是無限的3456數列 

所以只要去找出編號1的第3欄，第4欄，第5欄，第6欄，接著去找編號2的，就可以靠函數一直往下找了

我另外做了1234數列來方便理解

----

姓名

=VLOOKUP(H2,A:B,2,FALSE)

-----

11223344數列

=INT(ROW(A2)/2)

111222333444數列

=INT(ROW(A3)/3)

1111222233334444數列

=INT(ROW(A4)/4)

 11111222223333344444數列

=INT(ROW(A5)/5) 

用ROW函數取得當前列號，要重複幾次就除以多少，然後用INT取整數，就會從1開始得到所需的數列，注意第一格要能整除為1，所以要除以5就要用A5這格

-----

1212數列

=MOD(ROW(A2),2)+1

123123數列

=MOD(ROW(A3),3)+1

12341234數列

=MOD(ROW(A4),4)+1

用MOD函數去取 ROW函數的餘數，跟上面的函數一樣，第一格要能整除，就能依據所要的數量取得相對應的數列

-----

34數列 345數列 3456數列

這個直接拿上面的結果再加2就好了 

=MOD(ROW(A2),2)+3

=MOD(ROW(A3),3)+3

=MOD(ROW(A4),4)+3

---

科目

=VLOOKUP("編號",$A$1:$F$1,J2,FALSE) 

=VLOOKUP("編號",$A$1:$F$1,J3,FALSE)

=VLOOKUP("編號",$A$1:$F$1,J4,FALSE)

=VLOOKUP("編號",$A$1:$F$1,J5,FALSE)

簡單來說就是用編號這個值，去找A1:F1的第3~6欄，這邊注意A1:F1要鎖定A$1:F$1

3~6欄我們前面已經產生了3~6樹列了，所以直接去找J2~J5

----

成績 

=VLOOKUP(H2,A:F,J2,FALSE)

=VLOOKUP(H3,A:F,J3,FALSE)

=VLOOKUP(H4,A:F,J4,FALSE)

=VLOOKUP(H5,A:F,J5,FALSE) 

=VLOOKUP(H6,A:F,J6,FALSE)

用H2的編號值1，去找A:F的第3欄，也就是J2

到了H5，一樣用編號值2， 去找A:F的第3欄，也就是J6

----

以上就是我用編號進行索引去將表格轉向的作法

範例檔參考

 

https://docs.google.com/spreadsheets/d/1KC9vDv3-2dm-758venJFc4ylUzAdgPVI/edit?usp=drive_link&ouid=111432457316698416668&rtpof=true&sd=true

 

EXCEL:利用VBA巨集刪除有錯誤值的列 刪除特定欄有空值的列 刪除值為0的列

EXCEL在利用VLOOKUP函數以及引用資料的時候，偶爾會發生找不到資料的情況，

會顯示為#N/A

或是找出來的該列資料有錯誤其實是不需要的，如果要手動排序找出這些值刪掉列當然可以

不過其實有更快的方法，就是直接在VBA裡面執行一段程式碼

例如我要找出A欄所有有錯誤的列並刪除

你可以這麼寫

Sub 刪除A欄有錯誤的列()

Range("A:A").SpecialCells(xlCellTypeFormulas, xlErrors).EntireRow.Delete

End Sub

也就是尋找A欄中的特殊儲存格，其值為錯誤的，將該列(entire row)刪除

這樣就可以輕鬆刪掉所有不需要錯誤列了

也可以把中間這行程式碼 放在編寫好的巨集末端，使用上會更方便。

 

另外，如果是貼上資料想要刪除有空值的列

也可以把xlCellTypeFormulas, xlErrors換成xlcelltypeblanks

 

也就是下面這個巨集

Sub 刪除A欄值為空白的列() 

Range("A:A").SpecialCells(xlcelltypeblanks).EntireRow.Delete

End Sub

 

如果是想要刪除某欄為0的列

Sub刪除值為0的列()

Dim R As Range, Rng As Range

For Each R In ActiveSheet.Range("A:A").SpecialCells(xlCellTypeConstants).Rows


      If Not IsError(Application.Match(0, R, 0)) Then

         If Rng Is Nothing Then Set Rng = R Else Set Rng = Union(R, Rng)

     End If

Next

If Not Rng Is Nothing Then Rng.EntireRow.Delete

End Sub

利用VBA巨集複製區域，選擇性貼上值後指定位置另存新檔 檔名也自訂

先決條件是要會錄製巨集動作，

如果今天想要用巨集複製內容後

自動另外開新檔案，貼上值，存在指定的位置，檔名也是自訂，

實現可以一鍵輸出所需檔案的功能

你可以參考下面這個巨集

不過我會建議你利用錄製的功能，
先完成複製，開新檔案，選擇性貼上值， 存檔關閉，然後關掉錄製，
去打開你完成的巨集內容
再來跟我這個檔案比較一下，把原本絕對值指定位置跟檔名的部分，
改成我底下宣告的方式，達成在檔案裡面可以自由編輯存檔路徑與檔名的需求

我是複製B:D這個範圍

然後存檔的路徑去抓工作表!A1

舉例來說要存D槽，這個地方我的值 就會是「D:\」

檔名抓工作表!A2加上日期

所以我的檔名會是「設定的檔名_202209121559」

你也可以把&之後的的日期拿掉

然後我同時還指定了新檔案的工作表名稱為 EmployeeData

這是因為我需要產出一個固定匯入系統的EXCEL檔所需

需要特別修改的部分 我都用顏色標記起來了

您可以參考看看

 

Sub 巨集1()
'
' 巨集1'
'

iPath$ = CStr([工作表!A1])  '指定路徑宣告

NewName$ = CStr([工作表!A2]) & Format(Now, "_yyyymmddhhmmss") '檔名宣告

    Columns("B:D").Select '選取B:D
    Application.CutCopyMode = False
    Selection.Copy
    Workbooks.Add

    Sheets("工作表1").Select '選擇新檔案的工作表1
    Sheets("工作表1").Name = "EmployeeData" '變更工作表名稱

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveWorkbook.SaveAs Filename:=iPath & NewName, FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWindow.Close
End Sub

開啟網路磁碟機上的EXCEL檔案LAG 失去回應 無法操作 網路芳鄰/網路硬碟

話說我在開我們內部網路硬碟的EXCEL檔案時，

常常過一陣子就會無預警的LAG，

完全無法操作而且有時長達一分鐘，

連帶同時開啟的其他EXCEL檔案也不能作業，

但不是當掉失去回應，

如果我不等就只能去工作管理員把所有正在執行的工作強制關掉。

本來想說會不會是我的作業系統或是OFFICE有問題，

所以我重灌了WINDOWS11，OFFICE也裝了最新的LTSC 2021 

但問題依舊存在

後來我查一了下可能是這個問題，NAS要啟用 Opportunistic Locking

https://answers.microsoft.com/zh-hant/msoffice/forum/all/%E7%B6%B2%E8%B7%AF%E7%A3%81%E7%A2%9F%E6%A9%9F/6e32a13c-f3aa-44cd-81c6-51cd5b720e35

但後來詢問MIS

發現我們用的是WINDOWS SERVER架的網路磁碟機

得改用下面這個解法

在EXCEL的其他/選項/信任中心/信任中心設定/信任位置/勾選允許我的網路上信任的位置