获取外部数据时，最后一个零被截断 [英] Last Zeros Truncated When Getting External Data

查看：89
本文介绍了获取外部数据时，最后一个零被截断的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

在Excel 2019中选择Data>Get Data>From Other Sources>From Web从网络中导入数据时，数字的最后一个(尾部)零被截断，导致出现以下导入"列:

While importing data from the web in Excel 2019 choosing Data>Get Data>From Other Sources>From Web, the last (trailing) zeros of numbers are being truncated resulting in the following 'Import' column: 

EU
Import | Desired
968,8  |  968800
891,01 |  891010
413,47 |  413470
410,3  |  410300
43,25  |   43250
17,8   |   17800
15,05  |   15050
3,61   |    3610
6,05   |    6050
4,9    |    4900

US
Import | Desired
968.8  |  968800
891.01 |  891010
413.47 |  413470
410.3  |  410300
43.25  |   43250
17.8   |   17800
15.05  |   15050
3.61   |    3610
6.05   |    6050
4.9    |    4900

我想将文本(逗号，句号保留成千上万个分隔符)的数据转换为所需列中的数字.

I would like to convert the data which is text (commas, periods are remaining thousands separators), to numbers like in the Desired column.

我已经夸大了下面的VBA功能:

I've overdone the following working VBA function:

Option Explicit

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function UnTruncate(SourceVariant As Variant, _
  Optional TruncateString As String = "0", _
  Optional SplitSeparator As String = ",", _
  Optional NumberOfDigits As Long = 3) As Long

    Dim vnt As Variant        ' String Array (0-based, 1-dimensional)
    Dim strSource As String   ' Source String
    Dim strResult As String   ' Resulting String
    Dim strUB As String       ' Upper Bound String
    Dim i As Long             ' String Array Elements Counter

    ' Convert SourceVariant to a string (Source String (strSource)).
    strSource = CStr(SourceVariant)

    ' Check if Source String (strSource) is "" (UnTruncate = 0, by default).
    If strSource = "" Then Exit Function

    ' Split Source String (strSource) by SplitSeparator.
    vnt = Split(strSource, SplitSeparator)
    ' Assign the value of the last element in String Array (vnt)
    ' to Upper Bound String (strUB).
    strUB = vnt(UBound(vnt))

    ' Check if there is only one element in String Array (vnt). If so,
    ' write its value (strUB) to Resulting String (strResult) and go to
    ' ProcedureSuccess.
    If UBound(vnt) = 0 Then strResult = strUB: GoTo ProcedureSuccess

    ' Check if the length of Upper Bound String (strUB) is greater than
    ' NumberOfDigits. (UnTruncate = 0, by default)
    If Len(strUB) > NumberOfDigits Then Exit Function

    ' Add the needed number of TruncateStrings to Upper Bound String.
    strUB = strUB & String(NumberOfDigits - Len(strUB), TruncateString)

    ' Loop through the elements of String Array (vnt), from beginning
    ' to the element before the last, and concatenate them one after another
    ' to the Resulting String (strResult).
    For i = 0 To UBound(vnt) - 1: strResult = strResult & vnt(i): Next
    ' Add Upper Bound String (strUB) to the end of Resulting String (strResult).
    strResult = strResult & strUB

ProcedureSuccess:
    ' Convert Resulting String (strResult) to the resulting value of UnTruncate.
    UnTruncate = Val(strResult)

End Function
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

但是我感觉我缺少一些要点.

But I have a feeling I'm missing some important points.

我正在寻找其他解决方案:功能的改进，Excel公式，强大的查询解决方案，...可能在Import列中的数据可以为数字或文本的情况下.

I'm looking for other solutions: an improvement of my function, an Excel formula, a Power Query Solution, ... possibly when the data in Import column could be numbers or text.

推荐答案

似乎您使用的是旧版向导，而不是Power Query.

It seems you were using the Legacy Wizard rather than Power Query.

如果使用Power Query，请在选择表后选择Transform.

If you use Power Query, after selecting the Table, select Transform.

然后，如果数字列已作为文本导入，并且显示逗号的数字分隔符，请不要删除逗号.而是:

Then, if the number column has been imported as text, and is showing the digits separator of the comma, don't remove the commas. Rather:

  • 右键单击列标题
  • 从右键单击"下拉菜单中:
    • 选择Change Type --> Using Locale
    • 数据类型:整数
    • Right Click on the Column Header
    • From the Right-Click dropdown menu:
      • Select Change Type --> Using Locale
      • Data type: Whole Number

      那应该照顾好一切.

      关于使用Power Query保留Web表中的超链接，它不像使用旧式向导"那样简单，但是，这似乎可以与您的源一起使用.

      With regard to retaining hyperlinks from a web table using Power Query, it is not as straightforward as with the Legacy Wizard, but here is a method that seems to work with your source.

      它需要三个查询和一个函数.下载后，您需要编辑表格以设置数字格式以及可能的超链接的格式.

      It requires three queries and a function. And you will need to edit the table after the download to format the numbers, and possible the hyperlinks.

      • 查询表0"； 下载没有链接的网络表格
      • 查询"getLinks"； 下载与视频相关的链接
      • 查询"Merge1" 合并上面的两个查询
      • 查询fx"ExcelTrim" 通过消除视频标题中单词之间的多余空格，复制Excel的修剪，以便能够与前两个查询中的视频名称匹配.
      • Query "Table 0" Download the web table without links
      • Query "getLinks" Download the links associated with the Videos
      • Query "Merge1" Merge the two queries above
      • Query fx"ExcelTrim" Replicate Excel's trim so as to be able to match the video names in the first two queries, by eliminating excess spaces between words in the video title.

      在空白查询的高级编辑器中输入以下代码 

      let ExcelTrim = (TextToTrim) =>
    let
        ReplacedText = Text.Replace(TextToTrim, "  ", " "),
        Result = if not(Text.Contains(ReplacedText, "  "))
            then ReplacedText
                else @ExcelTrim(ReplacedText)
    in
        Text.Trim(Result)
in
    ExcelTrim

      表0

      注意，我使用了Changed Type with Locale功能，该功能应该可以消除掉零的问题.

      Table 0

      Note I used the Changed Type with Locale feature which should eliminate your dropped zero's problem.

      let
    Source = Web.Page(Web.Contents("https://kworb.net/youtube/")),
    Data = Source{0}[Data],
    #"Changed Type with Locale" = Table.TransformColumnTypes(Data, {{"Views", Int64.Type}, {"Likes", Int64.Type}}, "en-US"),
    #"Added Custom" = Table.AddColumn(#"Changed Type with Locale", "trimmedVideo", each ExcelTrim([Video]))
in
    #"Added Custom"

      getLinks 

      let
    Source = Table.FromColumns({Lines.FromBinary(Web.Contents("https://kworb.net/youtube/"))}),
    #"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Column1], "href")),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each Text.Contains([Column1], "<div><a href=")),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows1", "Link", each Text.BetweenDelimiters([Column1],"<a href=""","</a>")),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom", "Link", Splitter.SplitTextByEachDelimiter({""">"}, QuoteStyle.None, false), {"Link.1", "Link.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Link.1", type text}, {"Link.2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column1"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Columns", "trimmedVideo", each ExcelTrim([Link.2])),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "normLinks", each if not Text.StartsWith([Link.1],"http") then 
    "https://kworb.net/youtube/" & [Link.1] else 
    [Link.1])
in
    #"Added Custom2"

      合并1

      在视频"的单独列中返回链接 

      let
    Source = Table.NestedJoin(#"Table 0", {"trimmedVideo"}, getLinks, {"trimmedVideo"}, "getLinks", JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(Source, "Links", each Table.Column([getLinks],"normLinks")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Links", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"trimmedVideo", "getLinks"})
in
    #"Removed Columns"

      或者，您可以使用:

      将HYPERLINK公式返回到该表，该表提供了一个具有友好名称的可点击链接.

      Returns a HYPERLINK formula to the table which provides a clickable link with a friendly name.

      let
    Source = Table.NestedJoin(#"Table 0", {"trimmedVideo"}, getLinks, {"trimmedVideo"}, "getLinks", JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(Source, "Links", each Table.Column([getLinks],"normLinks")),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom","""","""""",Replacer.ReplaceText,{"Video"}),
    #"Extracted Values" = Table.TransformColumns(#"Replaced Value", {"Links", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"trimmedVideo", "getLinks"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Columns", "Linked Videos", each "=HYPERLINK(""" & [Links] & """," & """" &[Video] & """)"),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom1",{{"Linked Videos", type text}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type",{"Video", "Links"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"", "2", "Linked Videos", "Views", "Likes"})
in
    #"Reordered Columns"

      如果使用 Merge1(2)获取超链接，则保存后，需要选择Linked Video列，并对=进行Find/Replace或=为了将公式从文本字符串转换为公式.如果刷新查询，则需要重复此过程.

      If you use Merge1 (2) to get the hyperlinks, after saving, you will need to select the Linked Video column, and do a Find/Replace or = with = in order to turn the formula from a text string into a formula. If you refresh the query, you will need to repeat this process.

      您可能还需要设置Views和Likes列的格式，以显示成千上万个分隔符.

      You may also want to format the Views and Likes columns to show your thousands separators.

      这里是使用带有超链接和我的数千个分隔符的`Merge1(2)的示例.

      Here is an example using `Merge1 (2) with the hyperlinks and my thousands separators.

      这篇关于获取外部数据时，最后一个零被截断的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆