数据排列连在一起，求各就各位

lljd808 · 发表于 2024-5-12 22:59

本帖最后由 lljd808 于 2024-5-12 23:24 编辑

这里共有6种商品（还可以更多比如10种）

但每组数据都是乱的,各个数据整齐准确排列









            具体要达到的效果请见附本，求大侠帮忙，谢谢！

LIUZHU · 发表于 2024-5-13 00:22

这里系统导出来的数据吗？如果是，真不相信系统出具的数据有这么乱

LIUZHU · 发表于 2024-5-13 00:24

数据居然全是错位的

LIUZHU · 发表于 2024-5-13 00:25

还是先想清楚怎么把前端的系统优化好把

sunya_0529 · 发表于 2024-5-13 11:27

WPS里的JSA练习一下，纯力气活儿——

微信截图_20240513112302.png

cnmlgb9998 · 发表于 2024-5-13 13:06

PQ 方案，香不香？？？？？？？？？？？

<div class="blockcode"><blockquote>let
源 = Excel.CurrentWorkbook(){[Name = "表1"]}[Content],
Tab1 = Table.RemoveColumns(源, {"列1", "列2", "列3"}),
Tab2 = Table.TransformColumnTypes(
Tab1,
{
{"价格", type text},
{"代码", type text},
{"商品", type text},
{"入库", type text},
{"出库", type text},
{"余额", type text},
{"比例", type text}
}
),
Tab3 = Table.ReplaceErrorValues(
Tab2,
{
{"价格", null},
{"代码", null},
{"商品", null},
{"入库", null},
{"出库", null},
{"余额", null},
{"比例", null}
}
),
Tab4 = Table.RemoveColumns(Tab3, {"余额"}),
Tab5 = Table.TransformColumns(
Tab4,
{
{
"价格",
each List.Transform(
Splitter.SplitTextByRepeatedLengths(7)(_),
each
if Text.Contains(_, ".") then
Text.PadEnd(_, 7, "0")
else
Text.PadEnd(_ & ".", 7, "0")
)
},
{"代码", each Splitter.SplitTextByRepeatedLengths(6)(_)},
{
"入库",
each [
a = Text.BeforeDelimiter(_, "万"),
b =
if Text.Contains(a, ".") then
{Text.PadEnd(a, 6, "0")}
else
List.Transform(
Splitter.SplitTextByRepeatedLengths(6)(a),
each Text.From(Number.Round(Number.From(_) / 10000, 2))
)
][b]
},
{
"出库",
each [
a = Text.BeforeDelimiter(_, "万"),
b =
if Text.Contains(a, ".") then
{Text.PadEnd(a, 6, "0")}
else
List.Transform(
Splitter.SplitTextByRepeatedLengths(6)(a),
each Text.From(Number.Round(Number.From(_) / 10000, 2))
)
][b]
},
{
"比例",
each [
a = Splitter.SplitTextByCharacterTransition({"0" .. "9"}, {"-"})(_),
b = List.Transform(a, each Text.PositionOf(_, ".", 2)),
c = List.Transform(b, each {0} & List.Transform(_, (x) => x + 3)),
d = List.Select(
List.Combine(
List.Accumulate(
{0 .. List.Count(b)},
{},
(x, y) => x & {try Splitter.SplitTextByPositions(c{y})(a{y}) otherwise {null}}
)
),
each _ <> null and _ <> ""
)
][d]
}
}
),
Lst1 = Table.ToColumns(Tab5),
Lst2 = List.Transform(Lst1, each if _{0} is text or _{0} = null then _ else List.Combine(_)),
Lst3 = Lst2,
Lst4 = List.Transform(List.Zip({Lst3{3}, Lst3{4}}), each Number.From(_{0}) - Number.From(_{1})),
Lst5 = List.InsertRange(Lst3, 5, {Lst4}),
Cols = Table.ColumnNames(Tab1),
Result = Table.FromColumns(Lst5, Cols)
in
Result

复制代码

cnmlgb9998 · 发表于 2024-5-13 13:33

let
  源 = Excel.CurrentWorkbook(){[Name = "表1"]}[Content],
  Tab1 = Table.RemoveColumns(源, {"列1", "列2", "列3"}),
  Tab2 = Table.TransformColumnTypes(
Tab1,
{
   {"价格", type text},
   {"代码", type text},
   {"商品", type text},
   {"入库", type text},
   {"出库", type text},
   {"余额", type text},
   {"比例", type text}
}
  ),
  Tab3 = Table.ReplaceErrorValues(
Tab2,
{
   {"价格", null},
   {"代码", null},
   {"商品", null},
   {"入库", null},
   {"出库", null},
   {"余额", null},
   {"比例", null}
}
  ),
  Tab4 = Table.RemoveColumns(Tab3, {"余额"}),
  Tab5 = Table.TransformColumns(
Tab4,
{
   {
      "价格",
      each List.Transform(
      Splitter.SplitTextByRepeatedLengths(7)(_),
      each
         if Text.Contains(_, ".") then
            Text.PadEnd(_, 7, "0")
         else
            Text.PadEnd(_ & ".", 7, "0")
      )
   },
   {"代码", each Splitter.SplitTextByRepeatedLengths(6)(_)},
   {
      "入库",
      each [
      a = Text.BeforeDelimiter(_, "万"),
      b =
         if Text.Contains(a, ".") then
            {Text.From(Number.Round(Number.From(a), 1))}
         else
            List.Transform(
            Splitter.SplitTextByRepeatedLengths(6)(a),
            each Text.From(Number.Round(Number.From(_) / 10000, 1))
            )
      ][b]
   },
   {
      "出库",
      each [
      a = Text.BeforeDelimiter(_, "万"),
      b =
         if Text.Contains(a, ".") then
            {Text.From(Number.Round(Number.From(a), 1))}
         else
            List.Transform(
            Splitter.SplitTextByRepeatedLengths(6)(a),
            each Text.From(Number.Round(Number.From(_) / 10000, 1))
            )
      ][b]
   },
   {
      "比例",
      each [
      a = Splitter.SplitTextByCharacterTransition({"0" .. "9"}, {"-"})(_),
      b = List.Transform(a, each Text.PositionOf(_, ".", 2)),
      c = List.Transform(b, each {0} & List.Transform(_, (x) => x + 3)),
      d = List.Select(
         List.Combine(
            List.Accumulate(
            {0 .. List.Count(b)},
            {},
            (x, y) => x & {try Splitter.SplitTextByPositions(c{y})(a{y}) otherwise {null}}
            )
         ),
         each _ <> null and _ <> ""
      )
      ][d]
   }
}
  ),
  Lst1 = Table.ToColumns(Tab5),
  Lst2 = List.Transform(Lst1, each if _{0} is text or _{0} = null then _ else List.Combine(_)),
  Lst3 = Lst2,
  Lst4 = List.Transform(
List.Zip({Lst3{3}, Lst3{4}}),
each Number.Round(Number.From(_{0}) - Number.From(_{1}), 1)
  ),
  Lst5 = List.InsertRange(Lst3, 5, {Lst4}),
  Cols = Table.ColumnNames(Tab1),
  Result = Table.FromColumns(Lst5, Cols)
in
  Result

lljd808 · 发表于 2024-5-13 19:00

sunya_0529 发表于 2024-5-13 11:27
WPS里的JSA练习一下，纯力气活儿——

谢谢帮助，辛苦了，今天太忙，才上论坛看到，我在附件里没有找到代码，是不是我没有搞清楚，如果直接写上就好了！

lljd808 · 发表于 2024-5-13 19:05

LIUZHU 发表于 2024-5-13 00:25
还是先想清楚怎么把前端的系统优化好把

有时会这样，有时不是，不稳定，我把平时错的罗列在一起，遇到后就不怕了，现在每天浪费在无意义的辩认调整时间，让人无语。

lljd808 · 发表于 2024-5-13 19:07

cnmlgb9998 发表于 2024-5-13 13:33
let
源 = Excel.CurrentWorkbook(){[Name = "表1"]}[Content],
Tab1 = Table.RemoveColumns(源, {"列 ...

谢谢高手，辛苦了，今天太忙，才上论坛看到，这些代码我看和以前的不太一样，理解起来吃力，如果直接写上就好了！

		自动登录	找回密码
密码			免费注册

[求助] 数据排列连在一起，求各就各位

评分

评分