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

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

<h2><span>Thank you <a href="/@kenchung">@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. <p dir="auto"><br /> <hr /> <span><img src="https://images.hive.blog/768x0/https://steemitimages.com/DQmb33Qwu5vome8xQ2ZLcqFxZmFwV255hPGovzavXSUDtaX/math%26progLOGO-01-01.png" srcset="https://images.hive.blog/768x0/https://steemitimages.com/DQmb33Qwu5vome8xQ2ZLcqFxZmFwV255hPGovzavXSUDtaX/math%26progLOGO-01-01.png 1x, https://images.hive.blog/1536x0/https://steemitimages.com/DQmb33Qwu5vome8xQ2ZLcqFxZmFwV255hPGovzavXSUDtaX/math%26progLOGO-01-01.png 2x" /><p> <h2>中文版 <p dir="auto">建議和<a href="https://docs.google.com/spreadsheets/d/1lLgwkEZp5gY9E1BX93rdR8kDPomu-AJZFbn5X9Auh58/edit?usp=sharing" target="_blank" rel="noreferrer noopener" title="This link will take you away from hive.blog" class="external_link">解題Excel一起觀看，比較容易理解<br /> 這道題主要有幾個方向需要處理:<br /> 1.到底數字能不能反轉?<br /> 2.如果4位數字能反轉，轉出來的是什麼數字?<br /> 3.反轉後的數字有兩個結果<br />    i. 反轉後是相同: 對整體卡紙數沒有影響 (e.g. 0000 -> 0000)<br />   ii. 反轉後不相同: 每對卡紙數可以只留一張 (e.g. 0005 -> 5000)<br /> <hr /> <p dir="auto">具體步驟: <p dir="auto">1.在A列拉下由 0-> 9999所需要的10,000 個數字<br /> 2.把數字拆分為千，百，十和個位<br /> 3.檢查能不能反轉<br /> 4.如果能反轉，對照列表把千，百，十和個位掉換，並檢查轉換後的數字是否相同 <p dir="auto">得到以上幾個數字的組合，你就可以用簡單數學方法把答案算出來了。 <p dir="auto">相信第一步對於大家來講都是十分容易理解，我由第二步開始每點解釋如何操作。 <hr /> <p dir="auto">2.把數字拆分為千，百，十和個位<br /> 主要使用 QUOTIENT function <p dir="auto">拆分千位: A4為原數字格 <pre><code>=QUOTIENT(A4,1000) <p dir="auto">拆分百位: A4為原數字格, B4 為上面算好的千位數 <pre><code>=QUOTIENT(A4,100)-B4*10 <p dir="auto">拆分十位: A4為原數字格, B4 為上面算好的千位數, C4為上面算好的十位數 <pre><code>=QUOTIENT(A4,10)-B4*100-C4*10 <p dir="auto">拆分個位: A4為原數字格, B4 為上面算好的千位數, C4為上面算好的十位數, D4為上面算好的個位數 <pre><code>=A4-B4*1000-C4*100-D4*10 <hr /> <p dir="auto">3. 利用邏輯，檢查能否轉換 <p dir="auto">把數字寫出來，得出以下數字組合可以轉換<br /> 0->0<br /> 1->1<br /> 2->2<br /> 5->5<br /> 6->9<br /> 8->8<br /> 9->6<br /> <p dir="auto">因此檢查個別數字是否0,1,2,5,6,8,9便何 (B4是第一個數字的個位數 <pre><code>=IF(OR(B4=0,B4=1,B4=2,B4=5,B4=6,B4=9,B4=8),"Y","F") <p dir="auto">留意在IF statement中選用了OR, 即只要命中其中一個條件即返回TRUE <p dir="auto">之後再檢查是否全部數字都能轉換(F4,G4,H4,I4 為第一個數字檢查後能否轉換的結果) <pre><code>=IF(AND(F4="Y",G4="Y",H4="Y",I4="Y"),"T","") <p dir="auto">之後再把它們加起來，就得到總共能轉換的結果<br /> 由於上面能轉換的都打上了"T", 所以檢查有沒有T便可 <pre><code>=COUNTIF(J4:J100002,"T") <p dir="auto">最後得到答案<strong>2401 <hr /> <p dir="auto">4.把數字轉換，並檢查是否相同(例子是從舊的個位查新的千位) <pre><code>=IF($J4="T",VLOOKUP(E4,$Q$11:$R$17,2,FALSE),"") <p dir="auto">J4是檢查了能否轉換，如果可以轉換就召喚VLOOKUP查表公式，查詢轉換後的數字<br /> 注:VLOOKUP公式比較複雜，如果大家想了解更多可以在下面留言，我另貼再寫 <p dir="auto">之後把四個數字合起來 <pre><code>=IFERROR(K4*1000+L4*100+M4*10+N4,"") <p dir="auto">IFERROR公式是防止把沒有轉換的數字都轉出來 <p dir="auto">再跟原數字對比 <pre><code>=IF(O4=A4,"T","F") <p dir="auto">對比完把總數算起來 <pre><code>=COUNTIF(P4:P100002,"T") <p dir="auto">得出數字<strong>49 <hr /> <p dir="auto">之後就用簡單四則運算便可 <p dir="auto">1. 2,401(可轉換卡牌) - 49(轉換後相同的卡牌) =2,352對轉換後<strong>不相同的卡牌<br /> 2.2,352/2 = 1176 (可節省的卡牌)<br /> 3.10,000-1,176 = 8,824 <strong>(總卡牌數)<br /> <h2>因此8,824就是我們想要的答案 <hr /> <p dir="auto"><br /> <p dir="auto">English version <p dir="auto">Suggest work with my own  <a href="https://docs.google.com/spreadsheets/d/1lLgwkEZp5gY9E1BX93rdR8kDPomu-AJZFbn5X9Auh58/edit?usp=sharing" target="_blank" rel="noreferrer noopener" title="This link will take you away from hive.blog" class="external_link">Excel WorkSheet <p dir="auto">Problems to encounter:<br />   1. Could figures convertible after rotation?<br /> 2.If it could be rotated, what should be the figures look like?<br /> 3. We got two sets of outputs if the cards can be rotated:<br />      i. Same figures after rotation: Would not change the total rotation figures. (e.g. 0000 -> 0000)<br />    ii. Different figures after rotation: Keep one card per pair (e.g. 0005 -> 5000) <hr /> <p dir="auto">Step: <p dir="auto">1. Arranging  0->9999<br /> 2. Split those figures into thousandth, hundredth, tenth and single digit.<br /> 3. Check if the figures rotatable<br /> 4. If rotatable, rotate it and check if the figures are same before/after rotation <p dir="auto">It would be very straight forward to obtain the answer after those calculation  <p dir="auto">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 <hr /> <p dir="auto"><br /> <p dir="auto">2. Split those figures into thousandth, hundredth, tenth and single digit.<br /> Mainly by QUOTIENT function <p dir="auto">Thousandth digit: Notices that A4 is the target figure <pre><code>=QUOTIENT(A4,1000) <p dir="auto">Hundredth digit: Notices that A4 is the target figure and B4 is the calculated thousandth digit <pre><code>=QUOTIENT(A4,100)-B4*10 <p dir="auto">Tenth digit: Similar pattern as Hundredth digit <pre><code>=QUOTIENT(A4,10)-B4*100-C4*10 <p dir="auto">Single Digit: Simple deduction from extracted figure <pre><code>=A4-B4*1000-C4*100-D4*10 <hr /> <p dir="auto">3. Check if it is convertible by some logic <p dir="auto">You could check the convertible pair easily by write it down<br /> 0->0<br /> 1->1<br /> 2->2<br /> 5->5<br /> 6->9<br /> 8->8<br /> 9->6<br /> <p dir="auto">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 <pre><code>=IF(OR(B4=0,B4=1,B4=2,B4=5,B4=6,B4=9,B4=8),"Y","F") <p dir="auto">Notices we use OR inside IF statement, which means when we hit any one of correct condition, function will return TRUE <p dir="auto">Then we could check if the figure is convertible or not (F4,G4,H4,I4 is the result from procedure above) <pre><code>=IF(AND(F4="Y",G4="Y",H4="Y",I4="Y"),"T","") <p dir="auto">We could check the total number by sum up all of the "T" found above <pre><code>=COUNTIF(J4:J100002,"T") <p dir="auto">And we could found figures <strong>2401 in this step <hr /> <p dir="auto">4.If rotatable, rotate it and check if the figures are same before/after rotation <pre><code>=IF($J4="T",VLOOKUP(E4,$Q$11:$R$17,2,FALSE),"") <p dir="auto">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.<br /> Notes: While VLOOKUP function is quite complicated, please leave comment if you want further introduction of it. <p dir="auto">Then combining four digit by following formula <pre><code>=IFERROR(K4*1000+L4*100+M4*10+N4,"") <p dir="auto">IFERROR function is used to check if any error or not <p dir="auto">Then compare it with origin figure <pre><code>=IF(O4=A4,"T","F") <p dir="auto">Count it all <pre><code>=COUNTIF(P4:P100002,"T") <p dir="auto">And obtain answer <strong>49 <hr /> <p dir="auto">Then Simple mathematics could cater following calculation <p dir="auto">1. 2,401(convertible card) - 49(convertible card(same figures after rotation)) =2,352(convertible card(different figures after rotation)<br /> 2.2,352/2 = 1176 (Cards could save)<br /> 3.10,000-1,176 = 8,824 <strong>(Total card required) <h2> Thus 8,824 is the Final Answer