Excel恩尼格瑪機簡易模擬器

本文是聶渲南：如何用Excel製作一個恩尼格碼模擬器？？ 這一答案的鏡像，而且多了一些更新內容。

關於恩尼格瑪機的工作原理本身，詳見這位大大的答案：十一點半：《模仿遊戲》中艾倫·圖靈是如何破譯英格瑪的？

簡單來說，就是輸入一個字母（明文），途徑三個轉子，先後改變三次，再經反射器變一次，再從這三個轉子里出來（再改變三次），最後輸出另外一個字母（密文）。當輸入下一個字母時，轉子轉動，使得每個字母的加密方式都不一樣。

以上步驟示意見題圖。

上個月新發現了這本書，我收了一本，感覺很值，主要講的是人類文明的密碼史，圍繞密碼的爾虞我詐、智慧較量，計算機加密解密原理，甚至還從二戰的「風語者」引出了古文字破譯的故事

十一點半：失傳的古文字（象形文字、楔形文字等）最初是如何被破譯的？如何確認譯文的正確性？

還有密碼史上名人的冷門八卦，生平，離奇的死因（霧）

書後也有燒腦作業題。

總之，在這本書的激勵下，我做了如下嘗試。

橙色：輸入字元串 藍色：輸出字元串 粉紅色：錯誤檢查

第一階段：函數

有一些是我前幾天寫在專欄里的，姓名生成器v0.92公式優化，或者Excel實用函數小集錦

這裡我也會把主要用到的函數再解釋一遍。

CHAR(num)函數。當num範圍在1-128之間時，返回的是ASCII碼值，咱們用到的是65-90，對應大寫字母A-Z（畢竟，恩尼格瑪轉子上沒有數字，也不分大小寫）。例如CHAR(65)就是A。

它的逆函數CODE(cell)。例如CODE(A)理應返回65，但直接把字元填進去會出錯，只能以引用單元格的方式用它。

ROW()返回這個單元格的行號；對應地，COLUMN()返回列號。

MID(str,num1,num2)，從字元串中間開始提取字元，從第num1個算起，總共提取出num2個。對應地還有有LEFT和RIGHT。

NUMBERVALUE(str)，如果str是一串文本化了的數字，它能把它變回數字（有奇效，下面會提到）

SMALL(array,k)，返回數組的第k個最小值。需要配合數組計算操作Shift+Ctrl+Enter使用。

例如有一張新的空白表，A1單元格留空，A2里是： =SMALL(IF(COUNTIF(A$1:A1,ROW($1:$26))=0,ROW($1:$26)),INT(RAND()*(27-ROW(A1))+1)) ，解釋起來有點麻煩，我就簡略說說它的用處吧：

生成一串亂序且不重複的數據。注意使用時上面一定留一個空單元格，選中單個單元格按Shift+Ctrl+Enter做數組計算，再向下拖到累計26個格（例子里用的是26）了為止。

此刻，A2:A27之間就是一個打亂的從1到26的序列。

SUBSTITUTE(ADDRESS(1,COLUMN(A1),4),1,)函數組合。在這個例子中，ADDRESS返回了一個行數是1（第一個1）、列數是1（COLUMN(A1)的A列，這裡的1沒用）的坐標：A1（4代表行和列都是相對引用）。SUBSTITUTE是替換函數，這裡，會把返回的A1里的1替換成沒有。

最終返回的是一個孤零零的A，這是獲取列標字母的方式。

INDIRECT(str&num)函數，以列標和行號的方式獲取單元格數據。上一步返回的字母正好就可以用在這裡。

MOD(num1,num2)，取餘數。例如MOD(28,26)的結果就是2。如果我們希望當一串連續數據（比如行號）喂進去後，返回的餘數在3-28之間，就要稍微動點手腳：MOD(ROW()-3,26)+3，這樣，你會得到：27,28,3,4,5……的序列。

INT(num)，當num不是整數時，捨去一切小數部分，向下取整。

第二階段：可以搭建密碼機了

根據恩尼格瑪機的設計原理，我們的工作理應包含：

明文。接線板。三個轉子。反射器。密文逆向驗證。

1. 明文好辦。注意一點，並不支持26個大寫拉丁字母以外的任何明文內容（空格也不支持）。（攤手
2. 接線板……優先順序不高，我暫時沒做。模擬起來也許和反射器類似，以後我再驗證。
3. 字母有序排列的轉子（因為還有亂序的），也不是問題，字母序列上下移位而已，MOD加ROW函數可破。

這裡是測試用的明文，還有模擬三個轉子的初始位置（0代表相同字母對齊），圖例代表轉子分別為+9、+18、+7，累計34個位差（這個和數後面還會用到幾次，暫且放在A5單元格），這時A對應的新字母是+8（+34取26的余）之後的 I。

黃色區塊是在三個轉子初始設置條件下的字母映射，相當於一步凱撒密碼加密。

咱們要實現的是：第一個字母按9-18-7加密，第二個字母則要讓轉子1動一格，按10-18-7（相對值為1-0-0）加密，第三個字母11-18-7……

轉子1，每輸入一字+1，周期是26；

轉子2，每輸入26字+1，周期是26^2=676；

轉子3，每輸入676字+1，周期是26^3=17576。

累計位差的公式，各位可以試著自己寫一下。

參考答案：

=MOD(ROW()-1,26)+MOD(MOD(INT((ROW()-1)/26),676),26)+MOD(MOD(INT((ROW()-1)/676),17576),26)

這個式子以下我以①代替之。

在一個完整的17576周期中，它符合這樣的規律：

0,1,2……25；

1,2,3……26；2,3,4…… …… ……50；

1…… …… …… …… …… …… …… …… ……75。

（每個轉子都是0-25，所以最大位差就到75）

4. 反射器是比較有難度的，卡了我幾個晚上（不！真正原因是你菜！）

我們要實現的目標是：一串亂序排列的1-26，兩兩隨機配對，不能重複或漏掉。

這個公式的設計失敗了好幾次，差點就想撒手不幹去水steam了，奈何最終沒能擺脫《碼書》這本書的誘惑力（並不打廣告或者安利，你們可以不去剁手，233）

這期間隔的幾夜，給了我充分的時間來明白過勁，這問題，大概就算是意想不到地迎刃而解了吧：

既然是初始序列是隨機生成的，那麼相鄰兩個一組，就等效於隨機配對了吧。

由於SMALL亂序必須頂頭空一格，所以這裡Q和R列是錯位對應的。比如：

我讓Q列里隨機出的13和26配對。第13和26個字母，M和Z，code分別為77和90，

也就是說，反射器里M和Z是一條線連著的，輸入M時要輸出Z，輸入77時要輸出90，反之同理……

用&直接連起來即可。需要哪一部分，就用LEFT或RIGHT去提取。

幸好！

大寫字母的code都是兩位數，連起來都是四位數，不用考慮麻煩的補零湊位數問題。

注意&操作，會強制把數字轉化為文本，不能參與計算，這時就需要NUMBERVALUE出馬了。

綠色區塊最右邊那列，是將這些四位數升序排列後的結果（是的，SMALL使用得當的話，除了亂序，還能排序），為的是和前面的黃色字母序列同步，讓A永遠對應65xx，等等。

左邊的綠色區塊字母：按照四位數提供的映射關係，黃色字母（三個轉子加密後）經過反射器處理後的新結果。可以看到，黃色字母和綠色字母，確實做到了兩兩匹配。

右邊的黃色區塊數字：黃色字母變為綠色字母時的位差，比如A是15（變成P）。

最右邊的無底色區塊數字：將位差的數據按照正常序列（最左邊無底色字母）排布後。

這個V列下面還會用到。

第三階段：告訴我！恩尼格瑪醬

完整的加密過程為：明文→接線板（省了）→進轉子→反射器→出轉子→接線板（省了）→密文，

意即，從反射器出來後的字母還要再在三個轉子里走一遭，不過初始位置和位差要負向計算（例如進時是A→B，+1，出時就該是D→C，-1）

獲得密文字母的code，實際上就是把：

明文字母code，轉子初始位置，轉子相對計步（從0-0-0開始那個），反射器映射（V列），負的轉子相對計步，負的轉子初始位置

相加，兩組正負直接抵消。

初始位置和轉子相對計步雖然顯得多餘，但它們絕不是可以隨便捨棄的東西。

因為它們還決定著反射器映射。

中間還有一些MOD運算細節不再贅述，總之，

橙色代表原始文件，藍色是加密後的結果。

恩尼格瑪的特性是，在初始設置相同的前提下，把密文輸進去能直接得到明文。

紅色區塊是警示燈，用到了IF函數，當右邊的輸出不等於左邊的輸入時，顯示FALSE。

看起來我的模擬器運行得很好，一個八哥都沒有！

（ ↑ 這是我故意手動輸入的）

轉子初始位置和反射器也是RAND隨機生成的，F9刷新時也會重置。

這樣的不能用。

所以我還額外做了一個將它們固定住的有效模擬器，固定部分用黑色表示。

——原答案以外的更新——

不好意思，接線板我還是沒做_(:_」∠)_

但我把多餘的列封裝起來了：

這個封裝版里，只有黃色區塊是需要手動輸入的。

第一行模擬的是，先在默認設置下發送三個字母（因為要重複確認一次，實際上是六個）向接收方告知解密時分別要把三個轉子撥到什麼位置再開始。

第二行是明文本身。

下邊的橙色行，即左邊的橙色列，是送進機器的全部內容。

黑色列是固定了的反射器數據。

最左邊的橙色區塊是根據「轉子要撥到的位置」那三個字母算出的附加值（不要手動改掉）。

藍色列是輸出的密文，綠色列是輸入密文後所返回的內容（即明文，這一過程模擬的是解密工作）

注意，這裡我用到了Excel 2016才新增的CONCAT函數（用途是字元串焊接），不建議在2013或更低版本中使用。請更新( ° °)つロ