我的天

昨天我才发现，我把Excel文件升级完以后，我的搭档没有用过。因为连续好长一段时间，单位的作业时间都超长，每天都在正常办公室下班时间之前，还没结束。通常会折腾到晚上11点多，运气好的话，可能晚上七八点就结束了，反正总的来说，在我升级完我自己的文件以后，一直都只是我一个人使用。我在三台电脑上用过那个文件。我用的是同一个软件打开，用的是Microsoft 365。其中两台电脑是台式机，搭配的是64位的Win7系统，而另外一个是笔记本电脑，搭配的是64位Win10。二者是有区别的，因为某些功能只有在Win10系统才有更新，但到底具体是些什么，至今我还没搞清楚。自从用了Microsoft 365以后，我也说不准那个软件会什么时候给我更新，比如说晚上回到宿舍，单位的作业很晚才结束，我赶着要把数据搞完然后睡觉，但偏偏在那个时候，打开电脑，打开Microsoft 365，却告诉我他正在升级。这种状况很让人抓狂，因为晚上10-11点的时候，单位宿舍的网速可能非常慢。如果Office在那个时候进行升级，而那个安装包又非常大，这将是毁灭性的。所以下次大概我要给软件设置提醒我升级，但不能为我自动升级，应该由我去选择升级时间，这比开电脑就被吓一跳好多了。理论上说Win7系统上的Microsoft 365对应的是2016的功能，Win上的365对应的是2019的功能，但无论是Win7还是Win10，我的365还是会比传统的Office 2016高级那么一点点，至于高级在哪里，我不知道。

Microsoft是个神奇的存在。office软件不知道从什么版本开始都有32跟64位的版本，365貌似给我的安装包都是32位的版本，但是365在不同的Windows系统下面又有其他分支。

大体上我觉得，Win7下的Microsoft 365，应该跟Office 2016差不多吧。但昨天的实际遭遇告诉我，不是那回事。昨天我才发现，在我三台电脑上都开得好好的公式，在我的搭档的电脑上几乎所有地方都显示错误。这不可能吧！一开始，我怀疑是SUMIFS不能在2016上使用，但搜索过后发现那个函数已经在Office 2016上通行了，那到底是什么呢？结果发现原来是我搭档Excel的选项设置里面勾选了引用空单元格时显示错误，为什么居然有这种脑残设定呢？我用了这么多年的office，从来没遇到过这种事，昨天之前我甚至不知道原来还有个这样的设置。把那个勾去掉以后，还有不少公式的地方显示错误，点击那些公式后发现，在我的电脑上没有表示数组公式的地方，在她的电脑上居然默认成了数组公式，那些东西根本不是数组变成了数组，当然会出状况。所以解决办法也很简单，重新把那个公式确定变回普通公式，所有数据都回来了。为什么在不同的电脑上普通公式会自动变成数组公式呢？这个问题之前我也曾经遇到过，但没有让我的公式全部失效，所以我也就没有理会了，但我发现在某些函数前会多了个@的标志，至于那个东西是干什么的，我不知道。

带着这个问题，我立马去搜索。先是去百度，接着是去必应，最后我还是在Google找到了答案（1，2）。原来在Microsoft 365里，默认把所有公式都认为是数组公式，虽然没有很明确地标注出来。不是所有函数都会被误解，但一些包含相对变量的函数就会出现这种毛病，比如说公式里大量使用的indirect。这些公式在数组与非数组里理解是不一样的。当我的搭档在她电脑上把数组公式转为普通公式以后，在我的电脑上，发现数组公式前面多了个@，而这个东西。就是为了让老office兼容，在365里面有没有@，效果完全一致，但在老office里面，多了个@，就说明这个不是数组公式。

我不知道为什么office的进化经常会在一些相对引用与绝对引用里纠缠不清，之前有数据透视表的数据源变成绝对引用，搞死所有人。现在又有偷偷地把公式默认为数组公式。让老版本的软件看到一大片数据错误。他们在这个默默改变的的时候，其实完全可以推荐用户另存为别的Excel格式，通过转换格式把公式修正为老版本可以理解。

这个默认数组公式的问题我没见过中文解释，是不是因为谁用Microsoft 365中文版的人还不够多呢？

理清思路，比埋头苦干重要很多。如果一开始就掌握了方法，就不需要在路上兜那么多的弯。处理数据有这么一句行话，汇总时掉的汗，是录入时脑子进的水。如果一开始就把数据结构设定好，往后的汇总那是行云流水般自然而然的事。我是那种习惯于白手起家的人，通常我不会借用什么特殊的工具，比如说某个系统，我只用最普通常用的办公软件解决问题。

当然了，办公软件我是挑的，比如我只喜欢微软，我不喜欢WPS。而之所以不喜欢WPS，是因为我觉得很多东西他们仍旧留抄袭的层面，在一些非常核心的数据控制方面，他们远没有Office这么强大。有一些经常用到的小技巧，他们的确做了很好的封装要优化。但是会看的看门道，不会看的看热闹，就如一开始所说的，如果数据结构做好了，那些小窍门是不需要用到的。那些小窍门通过Office的高级公式是可以完成的。有人觉得那些小窍门非常有用，但是我觉得如果全盘数据由我控制，我的脑子不会那般进水，自然就不会挖坑让自己踩，那些所谓窍门也就不需要用上了。

跟数据透视表交了朋友以后，我明白到明细数据和汇总数据是彻底不一样的两种东西。你把他们混合起来用，结果将非常恐怖。通常，大家都喜欢这么干，而且觉得这么干是理所当然的事，而之所以有这样的看法，是因为当没有办公软件，没有Excel没有电脑之前，他们在纸质上就是这么干的。在纸质上这么干，可以让他们对整体数据有一个全面的认识了解。但实质上，他们所做的那些事正是数据透视表最擅长的。在纸上完成，只能做某一个分类的，如果要换个统计口径，那一大片数据等于白费。很多人在用Excel的时候，实际上只是把他们在纸上做的那些搬到电脑上。Excel不是一个画图软件，不是艺术家的画板，也不是一个用键盘操控的笔记本。这个强大的软件是有很强汇总计算功能的，我们必须用好这个，用不好别人的优点就是在增加自己的麻烦。软件的天马行空建立在我们说了一些他们能理解听懂的话。软件也是有脾气的，你得按他们的语法去表达你的东西，他才能用他的高超技术化腐朽为神奇。所以，我们首先要知道自己有什么，自己想得到什么，还有软件习惯用什么格式去处理问题。把我们的需求用机器语言翻译出来，然后我们就能得到我们想要的结果。把明细数据和汇总搞在一起其实并不是我们最初获取的数据心态，我们做的其实已经发展过了，要软件替我们做汇总分类，我们就要退回最原始的状态。为什么明细数据和汇总数据必须在一个页面反映出来呢？看数据的时候，我们到底是看每一条数据有没有问题，还是我们只是把那个明细数据当做是一个凑数的工具，而我们的眼睛直接瞄到了最后呢？如果我们关注的只是最后的汇总数据，明细数据摆在那里，难道就只是用来让我们多滑几下鼠标到最后吗？对基层人员来说，他们必须保证每一条原始数据的真实性、准确性和完整性，而对领导来说，明细是什么不重要，他们需要的是各种维度的汇总结果，每个领导的口味不一样，他们想要的汇总口径五花八门。成千上万的明细数据再用一开始纸质那种画大图的方法来汇总，根本赶不上这个时代的节奏。不仅仅是累处理数据的这个人，看结果的人也很烦。

程序语言也是一种语言，射手座有语言天赋，我觉得这可能是真的。

昨天研究了一个晚上的Excel。其实也没折腾出什么高端的东西出来，都是一些大路的功能。用我以前的方式，也能实现，不过昨天晚上我把它更进一步，让使用者更直观简单。之所以要改进自己，很重要的一个因素是我发现单位的仓号命名会导致筛选一些比较小的仓号的时候如果用输入的方式会误杀。比如说我选1仓的时候，当我输入1，那些什么11，13，16全部都会中招。要避免出现这种低级错误，最简单的方法我觉得是全部用两位数字，甚至直接用上三位。001，002这种东西不会在999之内重叠。但其实，以我们单位的占地，应该达不到三位数字。一个10万吨的立筒仓群里面有18个立筒仓，10个星仓。如果有5个以上的立筒仓群，两位数的命名就会出状况，但显然我们单位没有那么多的地方建这么多组立筒仓。至于浅圆仓，至少是1万吨一个，现在新建的那些甚至达到了2万吨。再怎么牛逼位，我们单位也规划不下100个浅圆仓。因为这就意味着起码有150万吨以上的仓容。加上立筒仓，要达到200万吨几乎不可能。以我们单位的占地，以及所建厂房的仓容，即便到达200万吨也用不上三位数字的编码。

以普通人的思路去考虑，从1-99编码是再正常不过的事，但是从一个不重叠数字的角度考虑，如果用的是CTRL+F的搜索，尚且可以选择单元格匹配，但是如果那是一个列表里面的筛选项，效果就会很尴尬。当然，也有一个不会中招的方式，就是用手动单选。如果你手头上有100个仓，你要单选其中一个。无论如何，这都比不上你直接输入数字快。点到某个单元格的下拉菜单里面去挑选某个东西是一个非常没有效率的做法。昨晚我突然领会到在这种时候，我需要使用切片器。其实切片器跟传统的筛选没什么区别，但是切片器可以直接放在工作表外面，你可以把它拉得很长。在普通的下拉菜单里面，某个视图你可能只有5-10个选项，但是只要你的电脑屏幕足够大，你的切片器就可以很大，极限情况下，一列估计能排下20个。5个选项和20个选项的区别在于下拉的滚动条被大大缩小了。Office 2003没有切片器，我不知道2010有没有，因为我几乎没有用过2010，但我知道2013年以后的Office具备这种功能，除了切片器以外，还有日程表。日程表这个东西我试用了一下，感觉能实现我的某些功能，但是为什么那个东西只能用滑动条而不能手动输入数据控制呢？如果要做到自由的控制，难道我要为那个日程表写个宏？

洗澡的时候我一直在考虑。计算堆存费这个东西最根本的是判断起始时间。决定了开始与结束，中间的那些东西就只是重复得做同样的事情而已。可悲的是，我脑子里只有python，没有Excel VBA。几乎不假思索地，我就已经设定好了有了开始结束以后，中间的循环在python应该怎么写。虽然我完全不知道要怎么让python读取我的Excel文件。python能实现的功能，Excel VBA应该也能实现，顶多是语句比较麻烦而已。做好了前期准备以后，计算那些东西就只是一个秒杀的过程。

当别人在想用什么程序可以解放自己，策划这要为之付多少钱的时候，我想的却是怎么自己写一个程序，解放生产力。

改变字典的键值规则就可以把从一本书里挑随机单词这件事轻松搞定，我真搞不懂参考答案为啥要那么折腾。在Think Python 2的第十三章里，字典的默认规则是单词是键，词频是键值。既然这道题要唯一的索引找随机单词，我把键值变成唯一序号不就完事大吉了？再来一个zip把字典的键值和键互换，random.choice()直接就到达随机单词了。我只改了生成字典的规则，耗时0.12秒，参考答案折腾了不只一点点，耗时0.42秒。之所以参考答案不修改字典规则，是因为他们要灌输python拼装模块的特性，拼装很方便，但事实证明效率不一定最高。

This algorithm works, but it is not very efficient; each time you choose a random word, it rebuilds the list, which is as big as the original book. An obvious improvement is to build the list once and then make multiple selections, but the list is still big.

An alternative is: Use keys to get a list of the words in the book. Build a list that contains the cumulative sum of the word frequencies (see Exercise 2). The last item in this list is the total number of words in the book, n. Choose a random number from 1 to n. Use a bisection search (See Exercise 10) to find the index where the random number would be inserted in the cumulative sum. Use the index to find the corresponding word in the word list.

Exercise 7: Write a program that uses this algorithm to choose a random word from the book. Solution: http://thinkpython2.com/code/analyze_book3.py.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

import string
import random
from time import time
def set_book(fin1):
    useless = string.punctuation + string.whitespace + '“' + '”' # 标点符号、换行符全部咔嚓掉
    d = {}
    i = 1
    for line in fin1:
        line = line.replace('-', ' ') # 有-的单词全部一分为二，这样真的好吗？
        for word in line.split():
            word = word.strip(useless)
            word = word.lower()
            if word not in d:
                d[word] = i # 录入字典的时候键值就是序号
                i += 1
            # d[word] = d.get(word, 0) + 1 # 反正我不算词频，这个没必要了
    return d
fin1 = open('emma.txt', encoding='utf-8')
start = time()
book1 = set_book(fin1)
book2 = dict(zip(book1.values(), book1.keys())) # 键和键值互换，序号成了唯一索引号
print('100 random words in book')
for i in range(100):
    if i > 1 and i%8 == 0:
        print()
    print(random.choice(book2), end=' ') # 索引号找词，想多快有多快
print()
end = time()
print(end - start)
# 100 random words in book
# solicit laughing preserve inebriety elton's unimpeded effusions unselfish
# intimate connect native judges charities travel informs colours
# enigmas bragge case greensward cox's particularly unexampled promise
# prone greensward dignity maps fourth christmas creature maximum
# graver mildest pleasant corrected increased named partridge marks
# following kept gloom conjecturing parlour inheriting say consulting
# magnified abundant produces sons malt add unenforceability beautifully
# richly striking confuse greatness asleep steps humility upon
# already paper delight liberties confide appendages undecided male
# prophecies esteem unadorned likelihood shopping deeply unbiased horrors
# man's dumplings business chapter shakespeare sees counsels attentive
# silenced ventured singular double mean waltzes requisite checks
# unattended qualified blessed surmises
# 0.12100672721862793

算算书里有多少单词应该是很大路简单的事，但实际上各种状况层出不穷。有些是你料到的，比如排版的用了全角的标点符号，程序默认会删掉标点符号，万一排版那个没有规范地使用空格呢？有些是你不会料到的，比如手误创造出奇葩字符串。很早以前我就发现Notepad++和Word里算的字数是不一致的，Notepad++通常算出来的数都会大一些。谁对谁错，随缘吧，知道大概差不多也就行了，毕竟高考的时候你写少几个字不到800也不会真扣你的分。

字典和列表的相爱相杀我体会得越来越深刻了。

words.txt在这里，emma.txt在这里。

Exercise 1: Write a program that reads a file, breaks each line into words, strips whitespace and punctuation from the words, and converts them to lowercase. Hint: The string module provides a string named whitespace, which contains space, tab, newline, etc., and punctuation which contains the punctuation characters. Let’s see if we can make Python swear:
>>> import string
>>> string.punctuation
‘!”#$%&\'()*+,-./:;<=>?@[\\]^_`{|}~’
Also, you might consider using the string methods strip, replace and translate.

Exercise 2: Go to Project Gutenberg (http://gutenberg.org) and download your favorite out-of-copyright book in plain text format. Modify your program from the previous exercise to read the book you downloaded, skip over the header information at the beginning of the file, and process the rest of the words as before. Then modify the program to count the total number of words in the book, and the number of times each word is used. Print the number of different words used in the book. Compare different books by different authors, written in different eras. Which author uses the most extensive vocabulary?

Exercise 3: Modify the program from the previous exercise to print the 20 most frequently used words in the book.

Exercise 4: Modify the previous program to read a word list (see Section 9.1) and then print all the words in the book that are not in the word list. How many of them are typos? How many of them are common words that should be in the word list, and how many of them are really obscure?

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128

import string
fin = open('words.txt')
mydict = {}
for line in fin:
    word = line.strip()
    mydict[word] = ''
file = open('emma.txt', encoding = 'utf-8')
essay = file.read().lower()
essay = essay.replace('-', ' ')
pun = {}
str_all = '“' + '”' + string.punctuation
for x in str_all: # 建立各种标点符号字符的字典
    pun[x] = ''
useless = essay.maketrans(pun) # maketrans必须被替换和替换等长，字典完美解决这个问题
l = essay.translate(useless).split() # 那些含-的单词会死得很惨，但仍然算是个单词
print('this book has', len(l), 'words')
book = {}
for item in l: # 读取文件为字符串，字符串转为单词列表，列表转为计数的字典，单词为键，次数为键值
    book[item] = book.get(item, 0) + 1
list_words1 = sorted(list(zip(book.values(), book.keys())), reverse = True) # 字典转为列表，键与键值换位
print('this book has', len(list_words1), 'different words')
print('times', 'word', sep='\t')
count = 1
word_len = 0 # 限制最小词长
for times, word in list_words1: # 打印大于某长度用得最多的20个词（不限制，3个字母及以下最最简单的会刷屏）
    if len(word) > word_len:
        print(times, word, sep='\t')
        count += 1
    if count > 20:
        break
count = 0
for word in book:
    if word not in mydict:
        # print(word, end=' ')
        count += 1
print(count, 'words in book not in dict') # 结果惨不忍睹，合计590个
# this book has 164065 words
# this book has 7479 different words
# times   word
# 5379    the
# 5322    to
# 4965    and
# 4412    of
# 3191    i
# 3187    a
# 2544    it
# 2483    her
# 2401    was
# 2365    she
# 2246    in
# 2172    not
# 2069    you
# 1995    be
# 1815    that
# 1813    he
# 1626    had
# 1448    as
# 1446    but
# 1373    for
# 590 words in book not in dict
# -----------------------------解法二----------------------------- 其实就是切单词方法有差异
import string
def set_book(fin1):
    useless = string.punctuation + string.whitespace + '“' + '”'
    d = {}
    for line in fin1:
        line = line.replace('-', ' ')
        for word in line.split():
            word = word.strip(useless)
            word = word.lower()
            d[word] = d.get(word, 0) + 1
    return d
def set_dict(fin2):
    d = {}
    for line in fin2:
        word = line.strip()
        d[word] = d.get(word, 0) + 1
    return d
fin1 = open('emma.txt', encoding='utf-8')
fin2 = open('words.txt')
book = set_book(fin1)
mydict = set_dict(fin2)
l = sorted(list(zip(book.values(), book.keys())), reverse=True)
count = 0
for key in book:
    count = count + book[key]
print('this book has', count, 'words')
print('this book has', len(book), 'different words')
num = 20
print(num, 'most common words in this book')
print('times', 'word', sep='\t')
for times, word in l:
    print(times, word, sep='\t')
    num -= 1
    if num < 1:
        break
count = 0
for word in book:
    if word not in mydict:
        # print(word, end=' ')
        count += 1
# print()
print(count, 'words in book not in dict')
# this book has 164120 words
# this book has 7531 different words
# 20 most common words in this book
# times   word
# 5379    the
# 5322    to
# 4965    and
# 4412    of
# 3191    i
# 3187    a
# 2544    it
# 2483    her
# 2401    was
# 2364    she
# 2246    in
# 2172    not
# 2069    you
# 1995    be
# 1815    that
# 1813    he
# 1626    had
# 1448    as
# 1446    but
# 1373    for
# 683 words in book not in dict