查找表/行中的所有非零值

发布于 2024-12-11 05:26:54 字数 416 浏览 1 评论 0原文

搜索了一段时间，决定把它扔出去，看看是否有人可以为此提供一个好的方向。我的表有约 100 行（并且正在扩展），大约有 24 列，看起来像这样：

ID  yr1 yr2 yr3 yr4

id1  $-      $-      $35     $-   
id2  $40     $45     $-      $-   
id3  $35     $-      $30     $37

我想忽略所有零值并创建一个如下所示的表表示（我不关心列标题）：

ID          
id1  $35        
id2  $40     $45    
id3  $35     $30     $37

似乎就像应该有一个简单的解决方案，但我还没有弄清楚。非常感谢任何帮助！

原文

Been searching for awhile and decided to throw this out there to see if anyone could provide a good direction for this. My table has ~100 (and expanding) rows by about 24 columns and looks something like:

ID  yr1 yr2 yr3 yr4

id1  $-      $-      $35     $-   
id2  $40     $45     $-      $-   
id3  $35     $-      $30     $37

I want to ignore all zero values and create a representation of this table that looks like this (I don't care about the column headings):

ID          
id1  $35        
id2  $40     $45    
id3  $35     $30     $37

Seems like there should be a simple(ish) solution, but I haven't figured it out yet. Any help is greatly appreciated!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

神也荒唐 2024-12-18 05:26:54

[更新：删除了最初的建议并替换为 VBA 解决方案]

Sub ReArrange()
Dim ws As Worksheet
Dim X
Dim lngRow As Long
Dim lngCol As Long
X = [a1].CurrentRegion
For lngRow = 1 To UBound(X, 1)
    For lngCol = 1 To UBound(X, 2)
        If X(lngRow, lngCol) = 0 Then X(lngRow, lngCol) = "=1/0"
    Next
Next
Application.ScreenUpdating = False
Set ws = Sheets.Add
ws.[a1].Resize(UBound(X, 1), UBound(X, 2)) = X
On Error Resume Next
ws.UsedRange.SpecialCells(xlCellTypeFormulas, 16).Delete xlToLeft
On Error GoTo 0
Application.ScreenUpdating = True
End Sub

在此处输入图像描述

[Update: Removed initial suggestion and replaced with VBA solution]

Sub ReArrange()
Dim ws As Worksheet
Dim X
Dim lngRow As Long
Dim lngCol As Long
X = [a1].CurrentRegion
For lngRow = 1 To UBound(X, 1)
    For lngCol = 1 To UBound(X, 2)
        If X(lngRow, lngCol) = 0 Then X(lngRow, lngCol) = "=1/0"
    Next
Next
Application.ScreenUpdating = False
Set ws = Sheets.Add
ws.[a1].Resize(UBound(X, 1), UBound(X, 2)) = X
On Error Resume Next
ws.UsedRange.SpecialCells(xlCellTypeFormulas, 16).Delete xlToLeft
On Error GoTo 0
Application.ScreenUpdating = True
End Sub

enter image description here

回复收藏 0 原文

~没有更多了~