2023年4月至2024年3月视频小结

Learning never stops, sharing never ends.

Since April 2023, one thing I have been doing is answering questions online and making representative questions into videos and posting them on Douyin (Douyin ID: 1175420452) and WeChat Video (Video ID: sphuXyqOUFhiMfw), and this will continue in the future.

As of March 11, 2024, I have posted a total of 191 videos with over 110,000 views.

I carefully produce each video.

• First of all, in terms of video production itself, I try to make it as smooth as possible and avoid major mistakes and verbal errors.
• Secondly, I anonymize the personal information of each questioner and the data they provide to avoid causing trouble to others.
• Third, and most importantly, is the content of the video itself.

There are too many videos, here are a few examples.

2024-2-14 the use of abbreviations and full names was mixed.https://www.douyin.com/video/7335427424982764835

In the data table where abbreviations and full names are mixed, various searches are very common, and in fact, there is no perfect solution, which caused me to spend a long time on this video. At first, I just explained in detail a formula that can solve most of the problems. As far as the data provided by the questioner is concerned, this formula can solve the problem well. But I feel that as a publicly released video, it cannot be so hasty, and the situations where errors may occur should be explained clearly, so I added relevant content. Later, I felt that explaining the errors clearly does not mean solving the problem. Since the function formula is powerless for this problem, let's change the angle, so I added the method of listing the "abbreviation and full name comparison table" in advance. After communicating with the questioner, I learned that the people filling in the data are unknown and it is impossible to fully predict the content they fill in, so the "abbreviation and full name comparison table" can only play a partial role. In the end, I decided to add the method of using data verification to control in advance, so that this problem has a complete answer from pre-control to post-processing.

2024-1-21 split and merge tables.https://www.douyin.com/video/7326520981420297512

2024-1-22 cross-workbook reference to final data.https://www.douyin.com/video/7326891861308755240

This was originally a seemingly simple question, batch modification of specified data in all worksheets in a workbook, but I made two videos for it. The second video answered the questioner's question, and the first video compared the differences between split and merged tables in the process of data entry, reconciliation, statistics, and report production, in order to illustrate one of the table-making ideas I have been advocating for a long time: tables with the same structure are placed in the same worksheet. Although Excel now has the Vstack function and Power Query, which can quickly and easily merge multiple tables, it is undoubtedly more efficient to be able to make a merged table in advance.

2023-5-10 Merge multiple Chinese-style tables with PQ.https://www.douyin.com/video/7231513339292028199

Many people, when using Excel, bring the idea of manual tabulation into the cells, such as the split tables mentioned earlier, where tables with the same structure are divided into several worksheets or even workbooks, as well as the table structure shown in this video, where the title has several rows and a large number of merged cells, resulting in duplicate fields. Such tables undoubtedly bring great inconvenience to further statistical analysis, yet they are very common. There is more than one way to organize such tables. Before making the video, I tried several solutions and finally chose the one that is easiest for beginners to accept and made it into a video. Even if you don't know how to use M functions, you can follow the steps in the video to achieve the final goal step by step.

2024-2-12 How to calculate the area with length and width in one cell – function.https://www.douyin.com/video/7334684806313184546

2024-2-13 How to calculate the area with length and width in one cell – PQ.https://www.douyin.com/video/7335055996672412928

This is another data organization problem. Before making the video, I also tried several solutions. There was a small episode during the production of this video: I first completed the function part, and when I was making Power Query, I found that the results of the two solutions were inconsistent. After carefully looking at each formula, I found that the result of the TextSplit function was a text-type number, which was fine when multiplied directly, but it was wrong when multiplied by the Product function. At this time, I have two choices, one is to add a corrected result at the end of the video, and the other is to redo the entire video. A 4-minute video seems very short, but the actual time spent is often not enough for half an hour, but in the end I decided to redo it all, striving for every released video to be smooth and complete.

2023-12-10 Converting data in the attendance system. – function.https://www.douyin.com/video/7310935596074159412

2023-12-12 Converting data in the attendance system.-PQ.https://www.douyin.com/video/7311678714687933731

Although this kind of function problem that converts the table structure is not difficult, it is indeed very complicated. The formula that is written well may have errors such as "dislocation" of the results when it is changed to another table. Even if you understand the calculation rules of the function formula, you may not be able to find the cause quickly after an error occurs. Therefore, after completing the function version, I added a Power Query version to give the questioner and those who encounter similar problems and see the video more solutions.

2023-5-31 Comparing two tables without using M functions.https://www.douyin.com/video/7239302992242019599

Power Query can make up for the shortcomings of built-in functions by customizing M functions, but for some novices who are not familiar with it, M functions are like heavenly books. Therefore, when answering questions or making videos, I try to use built-in functions as much as possible. The requirement of this questioner to solve the problem is not to customize M functions. Although this adds several steps, it is more convenient for novices to learn.

2023-4-27 Organizing room numbers with PQ.https://www.douyin.com/video/7226685777512549664

The example in the video was caused by the randomness of data recording. In the first case provided by the questioner, the room number only had a hyphen as a separator. After I gave the solution, he raised other separators, and this repeated many times. In fact, many people do not fully consider the problem before solving Excel problems, but solve it bit by bit. Sometimes this method is feasible, but sometimes, when new problems arise, it means that all previous work has to be overturned, and it is very important and efficient to make a comprehensive plan in advance. So finally when I made this video, I used the method of listing all the conditions first, which not only shared a technical issue of Power Query, but also shared my problem-solving ideas to a certain extent.

2023-11-11Imitating a doughnut chart.https://www.douyin.com/video/7300177115964509480

2023-11-13 Imitating a bubble chart.https://www.douyin.com/video/7300918575848557859

2024-2-17 Is a 'broken' column chart really necessary?https://www.douyin.com/video/7336540739825913128

These three videos are all about transforming pictures into charts. The first two are relatively simple, as long as you recognize the type of chart, the basic shape can be achieved.

The third is a problem with the production of column charts, because the data difference is too large, the excessively long column needs to be 'broken' in the middle. In this video, I gave two solutions, one can clearly show the data, but did not achieve the 'broken column chart' effect as expected by the questioner. The advantage of this solution is that the operation process is simple and easy to learn; the other is to use a scatter plot to make a fake vertical coordinate axis, although it achieves the 'broken column chart' effect, the production process is very cumbersome. This has always been my philosophy, not that I must give an answer 100% according to the questioner's thinking, but from the perspective of efficiency, give the most convenient and effective answer. Sometimes cumbersome answers may be dazzling, but not practical.

The above content was translated by Microsoft 365 Copilot.

The following is the original text in Chinese.

学习不停，分享不止。

从2023年4月至今，我一直在做的一件事就是在线答疑，并将其中有代表性的问题制作成视频发布在抖音（抖音号：1175420452）和微信视频号（视频号ID：sphuXyqOUFhiMfw）上，这件事未来还将继续。

截止2024年3月11日，我一共发布了视频191个，浏览11万+。

这每一个视频，我都认真制作。

• 首先，就视频制作本身来讲，我尽可能做到整体的流畅，避免较大的误操作和口误。
• 其次，我将每一个提问人的个人信息，以及他们所提供的数据虚化，以免给他人带来困扰。
• 第三，也就是最重要的，就是视频内容本身。

视频太多了，以下举几个例子。

2024-2-14简全称混用https://www.douyin.com/video/7335427424982764835

在简全称混用的数据表里进行各种查找，这个问题十分普遍，且事实上，没有完美的解决方法，这导致这个视频我做了很长时间。起初我只是详细解释了一个可以解决大部分问题的公式，就提问人所给出的数据而言，这个公式已经可以很好地解决问题了。但是我觉得作为一个公开发布的视频，不能如此草率，还应将有可能出现错误的情况解释清楚，于是增加了相关内容。后来又觉得，解释清楚了错误不等于解决了问题，既然这个问题函数公式无能为力了，那就换一个角度，于是又增加了事先列出“简全称对照表”的方式。后来与提问人沟通后得知，填写数据的人都是未知的，无法对他们填写的内容进行完全的预测，因此“简全称对照表”也只能起到部分作用。最后我决定增加用数据验证事先控制的方法，使得这一问题从事先控制到事后处理有了一个完整的解答。

2024-1-21分表与合表https://www.douyin.com/video/7326520981420297512

2024-1-22跨工作簿引用期末数据https://www.douyin.com/video/7326891861308755240

这本来是一个看似很简单的问题，批量修改工作簿中所有工作表的指定数据，但我却为此做了两个视频，第二个视频就提问人的问题进行了解答，第一个视频则是通过比较分表与合表在录入、对帐、统计、制作报表等过程的差异，用以说明我长期以来所提倡的制表思路之一：相同结构的表放在同一个工作表内。虽然现在的Excel有Vstack函数，有Power Query，可以非常快速方便地进行多表合并，但能够事先就制作出合并好的表无疑更为高效。

2023-5-10多个中式表格合并-PQ https://www.douyin.com/video/7231513339292028199

很多人在使用Excel的时候，会把以前手工制表的思路带到单元格里，比如前面提到的分表，相同结构的表分成若干个工作表甚至工作簿，还有就是本视频中所展示的这种表格结构，标题有好几行，并且带有大量的合并单元格，如此一来字段就会有重复。这样的表无疑会给进一步的统计分析带来很大不便，然而这样的表却十分普遍。

整理这样的表方法不止一种，我在制作视频之前先尝试了多种方案，最终挑选了一个最易于让新手接受的方案制作成视频，即便不会使用M函数，照着视频的步骤也可以一步一步实现最终目标。

2024-2-12长宽等在一个单元格里如何计算面积-函数https://www.douyin.com/video/7334684806313184546

2024-2-13长宽等在一个单元格里如何计算面积-PQ https://www.douyin.com/video/7335055996672412928

这又是一个数据整理的问题，在视频制作之前，我同样是尝试了多种方案。这个视频在制作过程中还有一个小插曲：我先完成了函数部分，在制作Power Query的时候发现两种解法的结果不一致，仔细看过每一个公式才发现是TextSplit函数的结果是文本型数字，直接相乘没问题，但用Product函数相乘就出错了。这时我有两个选择，一是在视频后面补以一个修正的结果，另一是整个视频全部重做。4分钟的一个视频看似很短，实际所花费时间常常半小时都不够，但是最终我还是决定全部重做，力求每一个发布出来的视频都流畅完整。

2023-12-10转换考勤系统中的数据-函数https://www.douyin.com/video/7310935596074159412

2023-12-12转换考勤系统中的数据-PQ https://www.douyin.com/video/7311678714687933731

这类转换表格结构的函数问题虽然谈不上有多难，但确实很复杂，写好的公式换到另一个表中就有可能出现结果“错位”等错误，即便了解了函数公式的运算规律，也未必会在出错后很快找出原因，因此我在做完了函数版以后又添加了一个Power Query版，让提问人和遇到类似问题看到视频的人有更多的解决方案。

2023-5-31两表比对无M版https://www.douyin.com/video/7239302992242019599

Power Query可以通过自定义M函数来弥补内置功能的不足，但是M函数对于一些不太熟悉的新手来说犹如天书，所以我在解答问题或制作视频时尽可能地使用内置功能。这位提问人对解决问题的要求就是不要自定义M函数，这样一来虽然增加了好几个步骤，但却更加方便新手学习。

2023-4-27整理房间号-PQ https://www.douyin.com/video/7226685777512549664

视频中的例子是由于数据记录时过于随意而造成的，提问人第一次提供的案例中，房号只有以横杠作为分隔符的情形，在我给出解决方案以后他又提出其他分隔符，如此反复多次。事实上有很多人在解决Excel问题之前，并没有一开始就将问题完整地考虑清楚，而是遇到一点解决一点，有时这种方法是可行的，但有的时候，当新问题来临时，就意味着要推翻前面全部，事先做好全盘的规划显得十分重要且高效。所以最后我在做这个视频的时候，我采用了先将所有条件列明的方法，这就不仅仅是分享一个Power Query的技术问题，还在一定程度上分享了我解决问题的思路。

2023-11-11模仿环形图https://www.douyin.com/video/7300177115964509480

2023-11-13模仿气泡图https://www.douyin.com/video/7300918575848557859

2024-2-17“断”柱图真的需要吗？https://www.douyin.com/video/7336540739825913128

这三个视频都是图片改造成图表的问题。前两个相对简单，只要认清图表类型，基本的形状就可以实现。

第三个是柱形图表制作的问题，因其数据差异过大，需要将过长的柱形从中间“断”开。在这个视频里我给出了两个方案，一个是能够清楚地展现数据，但并没有如提问人预期般实现“断柱图”效果，这一方案的好处是操作过程简单，相应地学习起来也容易；另一个是利用散点图做假纵坐标轴的方法，虽然达到“断柱图”效果，但制作过程十分繁琐。这是我向来的理念，并不是提问人问什么我就必须要百分百地按照他的思路给出答案，而是从高效的角度出发，给出最方便且能达到效果的答案，有时繁琐的答案也许很炫，但并不实用。