Excel公式太复杂?我花一晚上用Python做了个格式化工具

Excel公式太复杂?我花一晚上用Python做了个格式化工具
↑↑↑关注后"星标"简说Python

人人都可以简单入门Python、爬虫、数据分析
简说Python推荐作者:小小明来源:凹凸数据
作者简介:
www.zeeklog.com  - Excel公式太复杂?我花一晚上用Python做了个格式化工具
小小明,熟悉python、java、scala,了解go、c/c++。10年左右编码经验,逻辑思维能力良好,做过windows应用程序开发和大数据开发与运维,会大数据、web全栈开发、自动化办公、pandas数据处理,了解区块链开发、机器学习、 VBA、爬虫。

大家好,我是老表~

之前在交流群跟一些小伙伴有个讨论:

www.zeeklog.com  - Excel公式太复杂?我花一晚上用Python做了个格式化工具

大概就是很多跟数据打交道的人都需要面对过很复杂的excel,嵌套层数特别多,肉眼观看很容易蒙圈。有了这样的需求,小小明就有了解决问题的想法,说干就干于是一个比较牛逼的excel公式格式化的工具出现了。

1、效果体验

先看看效果吧:

=IF(C11>100%*C4,IF(C11<=200%*C4,C11*50%-C4*15%,C11*60%-C4*35%),IF(C11<=C4*50%,C11*30%,C11*40%-C4*5%))

的格式化结果是:

=IF(
  C11>100%*C4,
  IF(
    C11<=200%*C4,
    C11*50%-C4*15%,
    C11*60%-C4*35%
  ),
  IF(
    C11<=C4*50%,
    C11*30%,
    C11*40%-C4*5%
  )
)
www.zeeklog.com  - Excel公式太复杂?我花一晚上用Python做了个格式化工具
(SMA(MAX(CLOSE-DELAY(CLOSE,1),0),12,1)/SMA(ABS(CLOSE-DELAY(CLOSE,1)),12,1)*100-MIN(SMA(MAX(CLOSE-DELAY(
CLOSE,1),0),12,1)/SMA(ABS(CLOSE-DELAY(CLOSE,1)),12,1)*100,12))/(MAX(SMA(MAX(CLOSE-DELAY(CLOSE,1),0),12,
1)/SMA(ABS(CLOSE-DELAY(CLOSE,1)),12,1)*100,12)-MIN(SMA(MAX(CLOSE-DELAY(CLOSE,1),0),12,1)/SMA(ABS(
CLOSE-DELAY(CLOSE,1)),12,1)*100,12))

的格式化结果为:

(
  SMA(MAX(CLOSE-DELAY(CLOSE,1),0),12,1)
  /
  SMA(ABS(CLOSE-DELAY(CLOSE,1)),12,1)
  *
  100-MIN(
    SMA(MAX(CLOSE-DELAY(CLOSE,1),0),12,1)
    /
    SMA(ABS(CLOSE-DELAY(CLOSE,1)),12,1)*100,
    12
  )
)
/
(
  MAX(
    SMA(MAX(CLOSE-DELAY(CLOSE,1),0),12,1)
    /
    SMA(ABS(CLOSE-DELAY(CLOSE,1)),12,1)*100,
    12
  )
  -
  MIN(
    SMA(MAX(CLOSE-DELAY(CLOSE,1),0),12,1)
    /
    SMA(ABS(CLOSE-DELAY(CLOSE,1)),12,1)*100,
    12
  )
)
www.zeeklog.com  - Excel公式太复杂?我花一晚上用Python做了个格式化工具
=IF(ROW()>COLUMN(),"",IF(ROW()=COLUMN(),$B15,ROUNDDOWN($B15*INDIRECT(SUBSTITUTE(ADDRESS(1,3+COLUMN()-ROW(),
4),1,"")&56),0)))

的格式化结果为:

=IF(
  ROW()>COLUMN(),
  "",
  IF(
    ROW()=COLUMN(),
    $B15,
    ROUNDDOWN(
      $B15*INDIRECT(
        SUBSTITUTE(ADDRESS(1,3+COLUMN()-ROW(), 4),1,"")
        &
        56
      ),
      0
    )
  )
)
www.zeeklog.com  - Excel公式太复杂?我花一晚上用Python做了个格式化工具

如果你已经心动了,可以进入下滑到文末,点击阅读原文!直接使用体验!但本人不保证服务器一直会续费,网址能够永久使用。

不过接下来,将公布这套格式化程序的完整代码和开发思想,有技术能力的小伙伴可以考虑改进该代码。

2、完整代码

__author__ = 'xiaoxiaoming'

from collections import deque
import re


class Node:
    def __init__(self, parent=None, tab_size=0):
        self.parent = parent
        self.tab_size = tab_size
        self.data = []

    def is_single_node(self):
        for e in self.data:
            if not isinstance(e, str):
                return False
        return True

    def get_single_text(self):
        return "".join(self.data)


def split_text_blocks(excel_func_text):
    """
    将excel公式字符串,按照一定的规则切割成数组
    :param excel_func_text: 被切割的excel公式字符串
    :return: 切割后的结果
    """
    excel_func_text = excel_func_text.replace('\n', '').replace('\r', '')
    excel_func_text = re.sub(" +", " ", excel_func_text)
    lines = []
    i, j = 0, 0
    while j < len(excel_func_text):
        c = excel_func_text[j]
        if (c == '(' and excel_func_text[j + 1] != ')') or c == ',':
            lines.append(excel_func_text[i:j + 1])
            i = j = j + 1
        elif c == ')' and excel_func_text[j - 1] != '(':
            if i < j:
                lines.append(excel_func_text[i:j])
                i = j  # 起始文件块置于)处
            # 以下代码查找,如果中间不包含(或),则将)和,之间的文本块加入到划分结果
            k = excel_func_text.find(",", j + 1)
            l = excel_func_text.find("(", j + 1, k)
            m = excel_func_text.find(")", j + 1, k)
            if k != -1 and l == -1 and m == -1:
                lines.append(excel_func_text[i:k + 1])
                i = j = k + 1
            elif j + 1 < len(excel_func_text) and excel_func_text[j + 1] != ')':
                lines.append(")")
                lines.append(excel_func_text[j + 1])
                i = j = j + 2
            else:
                lines.append(")")
                i = j = j + 1
        elif c == '"':
            j = excel_func_text.find('"', j + 1) + 1
        else:
            j += 1
    return lines


blank_char_count = 2


def combine_node(root, text_max_length=60, max_combine_layer=3):
    """
    合并最内层的只有纯文本子节点的节点为单个文本节点
    :param root: 被合并的节点
    :param text_max_length: 合并后的文本长度不超过该参数,则应用该合并替换原节点
    :param max_combine_layer: 最大合并层数
    :return:
    """
    for _ in range(max_combine_layer):
        no_change = True
        stack = deque([root])
        while stack:
            node = stack.pop()
            tmp = {}
            for i, e in enumerate(node.data):
                if isinstance(e, Node):
                    if e.is_single_node():
                        single_text = e.get_single_text()
                        if len(single_text) < text_max_length:
                            tmp[i] = single_text
                    else:
                        stack.append(e)
            for i, e in tmp.items():
                node.data[i] = e
            if len(tmp) != 0:
                no_change = False
        if no_change:
            break


def node_next_line(node):
    for i, e in enumerate(node.data):
        if isinstance(e, str):
            if i == 0 or i == len(node.data) - 1:
                tab = node.tab_size - 1
            else:
                tab = node.tab_size
            yield f"{' ' * blank_char_count * tab}{e}"
        else:
            yield from node_next_line(e)
            

def excel_func_format(excel_func_text, blank_count=2, combine_single_node=True, text_max_length=60,
                      max_combine_layer=3):
    """
    将excel公式格式化成比较容易阅读的格式
    :param excel_func_text: 被格式化的excel公式字符串
    :param blank_count: 最终显示的格式化字符串的1个tab用几个空格表示
    :param combine_single_node: 是否合并纯文本节点,该参数设置为True后面的参数才生效
    :param text_max_length: 合并后的文本长度不超过该参数,则应用该合并替换原节点
    :param max_combine_layer: 最大合并层数
    :return: 格式化后的字符串
    """
    global blank_char_count
    blank_char_count = blank_count
    blocks = split_text_blocks(excel_func_text)
    # print("\n".join(blocks))
    # print('-----------拆分结果-----------')
    tab_size = 0
    node = root = Node()
    for block in blocks:
        if block.endswith("("):
            tab_size += 1
            child_node = Node(node, tab_size)
            node.data.append(child_node)
            node = child_node
            node.data.append(block)
        elif block.startswith(")"):
            tab_size -= 1
            node.data.append(block)
            node = node.parent
        else:
            node.data.append(block)
    if combine_single_node:
        combine_node(root, text_max_length, max_combine_layer)
    result = [line for line in node_next_line(root)]
    return "\n".join(result)

3、处理流程浅析

下面都以如下公式作为示例:

=IF(ROW()>COLUMN(),"",IF(ROW()=COLUMN(),$B15,ROUNDDOWN($B15*INDIRECT(SUBSTITUTE(ADDRESS(1,3+COLUMN()-ROW(),
4),1,"")&56),0)))

文本分块切分

def split_text_blocks(excel_func_text):
    """
    将excel公式字符串,按照一定的规则切割成数组
    :param excel_func_text: 被切割的excel公式字符串
    :return: 切割后的结果
    """
    excel_func_text = excel_func_text.replace('\n', '').replace('\r', '')
    excel_func_text = re.sub(" +", " ", excel_func_text)
    lines = []
    i, j = 0, 0
    while j < len(excel_func_text):
        c = excel_func_text[j]
        if (c == '(' and excel_func_text[j + 1] != ')') or c == ',':
            lines.append(excel_func_text[i:j + 1])
            i = j = j + 1
        elif c == ')' and excel_func_text[j - 1] != '(':
            if i < j:
                lines.append(excel_func_text[i:j])
                i = j  # 起始文件块置于)处
            # 以下代码查找,如果中间不包含(或),则将)和,之间的文本块加入到划分结果
            k = excel_func_text.find(",", j + 1)
            l = excel_func_text.find("(", j + 1, k)
            m = excel_func_text.find(")", j + 1, k)
            if k != -1 and l == -1 and m == -1:
                lines.append(excel_func_text[i:k + 1])
                i = j = k + 1
            elif j + 1 < len(excel_func_text) and excel_func_text[j + 1] != ')':
                lines.append(")")
                lines.append(excel_func_text[j + 1])
                i = j = j + 2
            else:
                lines.append(")")
                i = j = j + 1
        elif c == '"':
            j = excel_func_text.find('"', j + 1) + 1
        else:
            j += 1
    return lines

s = """=IF(ROW()>COLUMN(),"",IF(ROW()=COLUMN(),$B15,ROUNDDOWN($B15*INDIRECT(SUBSTITUTE(ADDRESS(1,3+COLUMN()-ROW(),
        4),1,"")&56),0))) """

blocks = split_text_blocks(s)
for block in blocks:
    print(block)

的运行结果为:

=IF(
ROW()>COLUMN(),
"",
IF(
ROW()=COLUMN(),
$B15,
ROUNDDOWN(
$B15*INDIRECT(
SUBSTITUTE(
ADDRESS(
1,
3+COLUMN()-ROW(),
 4
),
1,
""
)
&
56
),
0
)
)
)

这端代码首先替换掉所有的换行符,将多个空格替换为单个空格,然后将左右括号和逗号作为切分点进行切分。

但存在一些特殊情况,例如ROW()和COLUMN()括号内部没有任何内容,所有这种括号应该作为普通字符处理,另外被""包含的字符串可能包含括号,也应该作为普通字符。

构建多叉树层次结构

设计数据结构:

class Node:
    def __init__(self, parent=None, tab_size=0):
        self.parent = parent
        self.tab_size = tab_size
        self.data = []

parent存储父节点的指针,tab_size存储当前节点的层级,data存储当前节点的所有数据。

构建代码:

tab_size = 0
node = root = Node()
for block in blocks:
    if block.endswith("("):
        tab_size += 1
        child_node = Node(node, tab_size)
        node.data.append(child_node)
        node = child_node
        node.data.append(block)
    elif block.startswith(")"):
        tab_size -= 1
        node.data.append(block)
        node = node.parent
    else:
        node.data.append(block)

构建完毕后,这段数据在内存中的结构(仅展示data)如下:

www.zeeklog.com  - Excel公式太复杂?我花一晚上用Python做了个格式化工具

遍历打印这颗多叉树

def node_next_line(node):
    for i, e in enumerate(node.data):
        if isinstance(e, str):
            if i == 0 or i == len(node.data) - 1:
                tab = node.tab_size - 1
            else:
                tab = node.tab_size
            yield f"{' ' * 2 * tab}{e}"
        else:
            yield from node_next_line(e)
            
result = [line for line in node_next_line(root)]
print("\n".join(result))

结果:

=IF(
  ROW()>COLUMN(),
  "",
  IF(
    ROW()=COLUMN(),
    $B15,
    ROUNDDOWN(
      $B15*INDIRECT(
        SUBSTITUTE(
          ADDRESS(
            1,
            3+COLUMN()-ROW(),
             4
          ),
          1,
          ""
        )
        &
        56
      ),
      0
    )
  )
)

合并最内层的节点

显然将最内层的node5节点合并一下阅读性更好:

www.zeeklog.com  - Excel公式太复杂?我花一晚上用Python做了个格式化工具

首先给数据结构增加判断是否为纯文本节点的方法:

class Node:
    def __init__(self, parent=None, tab_size=0):
        self.parent = parent
        self.tab_size = tab_size
        self.data = []

    def is_single_node(self):
        for e in self.data:
            if not isinstance(e, str):
                return False
        return True

    def get_single_text(self):
        return "".join(self.data)

下面是合并纯文本节点的实现,max_combine_layer决定了合并的最大次数,如果合并后长度超过text_max_length参数,则不应用这次合并:

from collections import deque

def combine_node(root, text_max_length=60, max_combine_layer=3):
    """
    合并最内层的只有纯文本子节点的节点为单个文本节点
    :param root: 被合并的节点
    :param text_max_length: 合并后的文本长度不超过该参数,则应用该合并替换原节点
    :param max_combine_layer: 最大合并层数
    :return:
    """
    for _ in range(max_combine_layer):
        no_change = True
        stack = deque([root])
        while stack:
            node = stack.pop()
            tmp = {}
            for i, e in enumerate(node.data):
                if isinstance(e, Node):
                    if e.is_single_node():
                        single_text = e.get_single_text()
                        if len(single_text) < text_max_length:
                            tmp[i] = single_text
                    else:
                        stack.append(e)
            for i, e in tmp.items():
                node.data[i] = e
            if len(tmp) != 0:
                no_change = False
        if no_change:
            break

合并一次:

combine_node(root, 100, 1)
result = [line for line in node_next_line(root)]
print("\n".join(result))

结果:

=IF(
  ROW()>COLUMN(),
  "",
  IF(
    ROW()=COLUMN(),
    $B15,
    ROUNDDOWN(
      $B15*INDIRECT(
        SUBSTITUTE(
          ADDRESS(1,3+COLUMN()-ROW(), 4),
          1,
          ""
        )
        &
        56
      ),
      0
    )
  )
)

合并二次:

combine_node(root, 100, 2)
result = [line for line in node_next_line(root)]
print("\n".join(result))

结果:

=IF(
  ROW()>COLUMN(),
  "",
  IF(
    ROW()=COLUMN(),
    $B15,
    ROUNDDOWN(
      $B15*INDIRECT(
        SUBSTITUTE(ADDRESS(1,3+COLUMN()-ROW(), 4),1,"")
        &
        56
      ),
      0
    )
  )
)

合并三次:

combine_node(root, 100, 3)
result = [line for line in node_next_line(root)]
print("\n".join(result))

结果:

=IF(
  ROW()>COLUMN(),
  "",
  IF(
    ROW()=COLUMN(),
    $B15,
    ROUNDDOWN(
      $B15*INDIRECT(SUBSTITUTE(ADDRESS(1,3+COLUMN()-ROW(), 4),1,"")&56),
      0
    )
  )
)

合并三次后的内存情况:

www.zeeklog.com  - Excel公式太复杂?我花一晚上用Python做了个格式化工具

4、体验网站

http://xiaoxiaoming.xyz:8088/excel

不保证永久有效。

简说Python 2021第一波学习交流群入群开启,加微信:pythonbrief,回复:2021,即可加入。

入群规则:

1)本公众号留言总次数199+的读者可以直接添加好友进入;

2)2021年留言次数9+次的读者可以直接添加好友进入;

3)成功添加好友后,支付9.9元入群费用的读者可以直接进入(自动退群时会归还9.9元);

入群截止时间:2021.1.8 20:00

【注】设置入群规则主要是为了排除伸手党和推广营销的人,学习交流群不欢迎伸手党和推广者加入,一经发现直接清除、拉黑。

【图书推荐】《海量数据处理与大数据技术实战》是大数据开发领域中以实战案例为主旨的经典之作。本书全面阐述了大数据开发领域中常用的技术原理和框架,以及框架对应的实战案例。全书共分为四大篇章:大数据基础篇、大数据离线批处理技术篇、大数据在线实时处理技术篇、大数据处理实战案例篇。

www.zeeklog.com  - Excel公式太复杂?我花一晚上用Python做了个格式化工具

【最新投稿福利】

1>投稿规则:Java、前端、Python等方向的技术文章;内容不少于500字;可以是实战,也可以是欢快程序员类型,也可以是知识点科普;需要是首次发布在微信公众号平台,以原创发布,会注明作者及相关作者简介。

2>福利:按文章质量每1000字(不含代码)激励50-100元不等,投稿被采纳4次及以上者,可以另外获得赠书和简说编程专栏勋章,投稿越多,激励越多。

www.zeeklog.com  - Excel公式太复杂?我花一晚上用Python做了个格式化工具

扫码,备注:投稿



长按扫码关注,一起学Python



学习更多:
整理了我开始分享学习笔记到现在超过250篇优质文章,涵盖数据分析、爬虫、机器学习等方面,别再说不知道该从哪开始,实战哪里找了

Read more

使用本地大模型调用代码,根本就是一场骗局!

使用本地大模型调用代码,根本就是一场骗局!

通过大模型调用其他工具到底可不可行?ChatGPT 或许能轻松搞定一切,但同样的需求落在本地大模型上,恐怕就要打个问号了。 法国开发工程师 Emilien Lancelot 尝试了多款号称具备工具调用功能的 agent 框架,来看看本地大模型到底能不能完成任务,但结果就像他总结的“一无所获”。是什么让这位工程师失望了? 用 AutoGPT,得会点“糊弄学” AutoGPT 是款貌似强大的框架,提供很酷的 CLI 外加 Flutter UI,能够通过浏览器创建 agent。其主要功能是处理用户的文档、音频、视频等本地内容。 但是……它主要依靠 ChatGPT 或其他专有大模型服务来完成繁重工作,至少给我们的感觉是如此。 我们必须“唬弄”AutoGPT 才能使用 Ollama 端点,让其误认为是 ChatGPT。 ## OPENAI_API_KEY - OpenAI API

By Ne0inhk
详细说明Encoder-only架构的特点以及他和encoder decoder的区别和联系

详细说明Encoder-only架构的特点以及他和encoder decoder的区别和联系

Poe Encoder-only 架构的特点 Encoder-only 模型只使用 Transformer 的编码器部分,专注于对输入序列的理解和表征学习。这类模型通常被用在需要对输入文本进行深度理解的任务上,如文本分类、问答和语义匹配。以下是 Encoder-only 架构的核心特点: 1. 双向上下文建模 * 特点:Encoder-only 模型通过自注意力机制(Self-Attention)同时关注输入序列的前后文。 * 优势:相比单向模型(如 Decoder-only),它可以更全面地捕捉输入序列的全局语义,适合需要理解复杂上下文的任务。 * 实现方式:在训练过程中,不对输入序列进行因果掩码(Causal Masking),允许模型在任何位置访问序列的所有位置。 * 例子:BERT 的 Masked Language Model(MLM)训练任务通过随机遮盖部分单词,依赖左侧和右侧的信息来预测被遮盖的词,即双向建模的典型体现。 2. 适用于理解任务 * 特点:Encoder-only 模型专注于理解输入序列,而不生成输出序列,因此适合处理分类、

By Ne0inhk
手把手教学,DeepSeek-R1微调全流程拆解

手把手教学,DeepSeek-R1微调全流程拆解

手把手教学,DeepSeek-R1微调全流程拆解 原创 极客见识  2025年02月09日 09:02 广东 DeepSeek 通过发布其开源推理模型 DeepSeek-R1 颠覆了 AI 格局,该模型使用创新的强化学习技术,以极低的成本提供与 OpenAI 的 o1 相当的性能。 更令人印象深刻的是,DeepSeek 已将其推理能力提炼成几个较小的模型。这篇文章,我们将使用其蒸馏版本之一引导大家完成 DeepSeek-R1 的整个微调过程。 本文章将演示了如何微调其中一个模型(使用我们自己的自定义思维链数据集),然后保存和部署微调后的模型。 高级推理模型微调 DeepSeek 简介 DeepSeek-R1 是由深度求索(DeepSeek)公司开发的突破性推理模型。DeepSeek-R1 基于 DeepSeek-V3-Base(总共 671B 个参数,每次推理 37B 处于活动状态)构建,使用强化学习 (RL) 在提供最终答案之前生成思路链

By Ne0inhk