[Answer by Excel] Mathematics × Programming Competition #8 [答案(使用excel)] 數學 × 程式編寫比賽 (第八回)

這次參加了由 @kenchung 舉辦的「數學 × 程式編寫比賽 (第八回)」，有想過用不同的方法去做，最後選了簡單易明的Excel來處理這道題。 帖文在此：@kenchung/question-mathematics-programming-competition-8

Thank you @kenchung for hosting Mathematics × Programming Competition #8, I've try to solve the question by some simple excel function. English version below Chinese one, thanks.

---

中文版

建議和解題Excel一起觀看，比較容易理解
這道題主要有幾個方向需要處理:
1.到底數字能不能反轉?
2.如果4位數字能反轉，轉出來的是什麼數字?
3.反轉後的數字有兩個結果
   i. 反轉後是相同: 對整體卡紙數沒有影響 (e.g. 0000 -> 0000)
  ii. 反轉後不相同: 每對卡紙數可以只留一張 (e.g. 0005 -> 5000)

---

具體步驟:

1.在A列拉下由 0-> 9999所需要的10,000 個數字
2.把數字拆分為千，百，十和個位
3.檢查能不能反轉
4.如果能反轉，對照列表把千，百，十和個位掉換，並檢查轉換後的數字是否相同

得到以上幾個數字的組合，你就可以用簡單數學方法把答案算出來了。

相信第一步對於大家來講都是十分容易理解，我由第二步開始每點解釋如何操作。

---

2.把數字拆分為千，百，十和個位
主要使用 QUOTIENT function

拆分千位: A4為原數字格

=QUOTIENT(A4,1000)

拆分百位: A4為原數字格, B4 為上面算好的千位數

=QUOTIENT(A4,100)-B4*10

拆分十位: A4為原數字格, B4 為上面算好的千位數, C4為上面算好的十位數

=QUOTIENT(A4,10)-B4*100-C4*10

拆分個位: A4為原數字格, B4 為上面算好的千位數, C4為上面算好的十位數, D4為上面算好的個位數

=A4-B4*1000-C4*100-D4*10

---

3. 利用邏輯，檢查能否轉換

把數字寫出來，得出以下數字組合可以轉換
0->0
1->1
2->2
5->5
6->9
8->8
9->6

因此檢查個別數字是否0,1,2,5,6,8,9便何 (B4是第一個數字的個位數

=IF(OR(B4=0,B4=1,B4=2,B4=5,B4=6,B4=9,B4=8),"Y","F")

留意在IF statement中選用了OR, 即只要命中其中一個條件即返回TRUE

之後再檢查是否全部數字都能轉換(F4,G4,H4,I4 為第一個數字檢查後能否轉換的結果)

=IF(AND(F4="Y",G4="Y",H4="Y",I4="Y"),"T","")

之後再把它們加起來，就得到總共能轉換的結果
由於上面能轉換的都打上了"T", 所以檢查有沒有T便可

=COUNTIF(J4:J100002,"T")

最後得到答案2401

---

4.把數字轉換，並檢查是否相同(例子是從舊的個位查新的千位)

=IF($J4="T",VLOOKUP(E4,$Q$11:$R$17,2,FALSE),"")

J4是檢查了能否轉換，如果可以轉換就召喚VLOOKUP查表公式，查詢轉換後的數字
注:VLOOKUP公式比較複雜，如果大家想了解更多可以在下面留言，我另貼再寫

之後把四個數字合起來

=IFERROR(K4*1000+L4*100+M4*10+N4,"")

IFERROR公式是防止把沒有轉換的數字都轉出來

再跟原數字對比

=IF(O4=A4,"T","F")

對比完把總數算起來

=COUNTIF(P4:P100002,"T")

得出數字49

---

之後就用簡單四則運算便可

1. 2,401(可轉換卡牌) - 49(轉換後相同的卡牌) =2,352對轉換後不相同的卡牌
2.2,352/2 = 1176 (可節省的卡牌)
3.10,000-1,176 = 8,824 (總卡牌數)

因此8,824就是我們想要的答案

---

English version

Suggest work with my own  Excel WorkSheet

Problems to encounter:
  1. Could figures convertible after rotation?
2.If it could be rotated, what should be the figures look like?
3. We got two sets of outputs if the cards can be rotated:
     i. Same figures after rotation: Would not change the total rotation figures. (e.g. 0000 -> 0000)
   ii. Different figures after rotation: Keep one card per pair (e.g. 0005 -> 5000)

---

Step:

1. Arranging  0->9999
2. Split those figures into thousandth, hundredth, tenth and single digit.
3. Check if the figures rotatable
4. If rotatable, rotate it and check if the figures are same before/after rotation

It would be very straight forward to obtain the answer after those calculation 

I'd like to skip step 1 as it is too simple to all of us, let me explain my work starting from step 2

---

2. Split those figures into thousandth, hundredth, tenth and single digit.
Mainly by QUOTIENT function

Thousandth digit: Notices that A4 is the target figure

=QUOTIENT(A4,1000)

Hundredth digit: Notices that A4 is the target figure and B4 is the calculated thousandth digit

=QUOTIENT(A4,100)-B4*10

Tenth digit: Similar pattern as Hundredth digit

=QUOTIENT(A4,10)-B4*100-C4*10

Single Digit: Simple deduction from extracted figure

=A4-B4*1000-C4*100-D4*10

---

3. Check if it is convertible by some logic

You could check the convertible pair easily by write it down
0->0
1->1
2->2
5->5
6->9
8->8
9->6

So we need to check it the figures contain 0,1,2,5,6,8,9 or not (B4 is 1st digit of the 1st number

=IF(OR(B4=0,B4=1,B4=2,B4=5,B4=6,B4=9,B4=8),"Y","F")

Notices we use OR inside IF statement, which means when we hit any one of correct condition, function will return TRUE

Then we could check if the figure is convertible or not (F4,G4,H4,I4 is the result from procedure above)

=IF(AND(F4="Y",G4="Y",H4="Y",I4="Y"),"T","")

We could check the total number by sum up all of the "T" found above

=COUNTIF(J4:J100002,"T")

And we could found figures 2401 in this step

---

4.If rotatable, rotate it and check if the figures are same before/after rotation

=IF($J4="T",VLOOKUP(E4,$Q$11:$R$17,2,FALSE),"")

If J4(checker of convertible or not) is true, then we can convert the digit via VLOOKUP function, if not then we can do nothing in this stage.
Notes: While VLOOKUP function is quite complicated, please leave comment if you want further introduction of it.

Then combining four digit by following formula

=IFERROR(K4*1000+L4*100+M4*10+N4,"")

IFERROR function is used to check if any error or not

Then compare it with origin figure

=IF(O4=A4,"T","F")

Count it all

=COUNTIF(P4:P100002,"T")

And obtain answer 49

---

Then Simple mathematics could cater following calculation

1. 2,401(convertible card) - 49(convertible card(same figures after rotation)) =2,352(convertible card(different figures after rotation)
2.2,352/2 = 1176 (Cards could save)
3.10,000-1,176 = 8,824 (Total card required)

Thus 8,824 is the Final Answer