Excel的一些使用技巧（3）

06-04

如何將名稱相同的欄位合併匯總，且只顯示同一個名稱如：公交公司 50 60，交通集團 60 40，公交公司 100 600，大學 60 10將以上數據表進行合併匯總，即兩個公交公司合計為一個單位，後面的數據進行相應求和匯總。結果：，公交公司 150 660，交通集團 60 40，大學 60 10解答：用SUMIF同樣能夠實現，按照上次的公式＋公式＋公式＋……，例：=SUMIF($A$1:$A$4,公交公司,$B$1:$B$4)+SUMIF(Sheet2!$A$1:$A$4,公交公司,Sheet2!$B$1:$B$4)+SUMIF(Sheet3!$A$1:$A$4,公交公司,Sheet3!$B$1:$B$4)+SUMIF(Sheet4!$A$1:$A$4,公交公司,Sheet4!$B$1:$B$4)。也可以這樣：=IF(Sheet1!$A$1:$A$4=公交公司,IF(Sheet2!$A$1:$A$4=公交公司,IF(Sheet3!$A$1:$A$4=公交公司,IF(Sheet4!$A$1:$A$4=公交公司,SUM(Sheet1!B1:B4+Sheet2!B1:B4+Sheet3!B1:B4+Sheet4!B1:B4)))))中的內容根據自己的條件需要改變。或者：用菜單「數據》合併計算」功能。按工號大小排列表1工號成績月份/01 20 1月/06 30 1月/100 60 1月/102 80 1月表2工號成績月份/01 80 2月/07 90 2月/100 89 2月/102 90 2月/130 100 2月要求產生的表：工號成績/1月 2月 /01 20 80/06 30/07 90/100 60 89/102 80 90/130 100請問如何實現按工號大小排列？解答：記錄的自動篩選我已經解決，請仔細體會例子中公式的含義。多條件數據的求和，用數組公式： =SUM((條件1所在區域=條件1)*(條件2所在區域=條件2)*(要求和的區域))。文本用「」兩日期間的天數Q:在一個表中有兩列日期型數字，請問如何在第三列中得到其差(兩日期間的天數)A:=IF(A1>B1,DATEDIF(B1,A1,d),DATEDIF(A1,B1,d))序列問題我用EXCEL做了一個模板，其中一些數據（文本數據）是經常重複的，但我每次只好重新輸入，作了很多重複勞動，請告知如何做一個選擇對話框，把以往輸入的數據（文本數據）顯示以供選擇，更好是能按字母查詢。謝謝！！解答：在B列設置數據驗證，首先定義「名稱」為ABC在引用位置設置為A1：A1000然後將B列數據有效性設置為：」序列「」=ABC「在A列輸入的數據在B列就會列出來條件乘積的求和A列為部門名稱，B列為姓名，C列為日工資額(如20.00)，D列為月出勤天數，我想在另一匯總表中匯總出各部門員工月工資總額(即：相應部門對應的C*D之和)。請問如何解決？解答1：直接寫公式有點困難，我的想法是用vba編程實現。思路如下：編寫一個循環，遍歷A列，並進行檢測，設置幾個變數，分別代表幾個部門和相應的工資總額，每當部門變數和單元格的值相等時，就把該值加到相應的工資總額變數中，直到遍歷結束。解答2：採用數組公式：{sum((Aarray=部門名稱)*(Carray)*(Darray)*1}其中array為對應的區域名稱！解答3：1、使用數據透視表的功能。2、或者使用SUMPRODUCT函數。如何判斷如果 c2為空，則如果b2含有「1」，或者「2」，或者「3」的話，d2=b2*4-5，請問各位高手，以上如何在excel內實現判斷. 解答1：D2=IF(AND(C2=,OR(B2=1,B2=2,B2=3)),B2*4-5,)解答2：(上面的解答和題意不符，含有字元1、2、或3。應該寫成d2=IF(C2=,IF(ISERROR(FIND(1,B2,1)>0),IF(ISERROR(FIND(2,B2,1)>0),IF(ISERROR(FIND(3,B2,1)),,B2*4-5),B2*4-5),B2*4-5),)用字母在單元格裡面填入平方米解答1：在編輯狀態下，選中「2」，按滑鼠右鍵，選擇「設置單元格格式」，選「上標」解答2：按[ALT]+數字鍵178[ENTER]在Excel中列印條形碼解答：在Office中都可以通過在控制項工具箱單擊其它控制項，選取Microsoft BarCode Control 9.0 ，然後進行賦值操作即可。求工齡=DATEDIF(B2,TODAY(),y) =DATEDIF(B2,TODAY(),ym) =DATEDIF(B2,TODAY(),md) =DATEDIF(B2,TODAY(),y)&年&DATEDIF(B2,TODAY(),ym)&月&DATEDIF(B2,TODAY(),md)&日把26個表格匯總求和成一個匯總表解答：假設匯總的工作表叫Sheet1，而存放數據的工作表叫Sheet2, Sheet3, ..., Sheet27，你可以這樣設置公式，把各工作表A1的數值加起來：　　=SUM(Sheet2:Sheet27!A1) 要注意的是，Sheet1不能在Sheet2和Sheet27中間，Excel不會管工作表的名字，只會把Sheet2和Sheet27中間的＊所有＊工作表中相關的儲存格（哪怕中間有一個工作表叫Sheet99）加起來。 1、在匯總表單元格內選擇「Σ」 2、選擇需要疊加的第一個工作表 3、按住shift同時用滑鼠選擇需要疊加的最後一個工作表 4、選擇需要疊加的單元格 5、Enter重複數據得到唯一的排位序列想得到數據的出現總數嗎（{1,2,2,3,4,4,5} 數據的出現總數為5）？解答：不需要插列,不需要很多的函數就行了. =RANK(B3,B$3:B$12)+COUNTIF(B$3:B3,B3)-1 從身份證號碼中提取性別Q： A1單元格中是15位的身份證號碼，要在B1中顯示性別(這裡忽略15位和18位身份證號碼的判別） B1=if(mod(right(A1,1),2)>0,male,female)請問這個公式有無問題，我試過沒發現問題。但在某個網站看到作者所用的是如下公式： B1=if(mid(A1,15,1)/2=trunc(mid(A1,15,1)/2）,female,male) 答：道理都是一樣的，不過你的公式比那個公式優質。如何從文本文件中取數Q: 1.如何從文本文件中取2,4,6,8......行的數據到EXCEL中? 2.在EXCEL中如何實現產生一個ACCESS的表,並把指定的數據存入ACCESS的表中. A: 1.（暫時不知道你問的目的是什麼，如果僅僅要結果，就往下看）如果要導入一文本文件（過程略），導入的結果在SHEET1的A1：100（如果是100行的話）中，此時不管它奇還是偶行；然後在SHEET2的A1中輸入公式=INDEX(Sheet1!$A$1:$A$81,2*ROW(),COLUMN())，拖公式，就可以截取到文本文件的2，4，6，8，10......行了。 2.關於EXCEL轉為ACCESS，MS專門為用戶設計了一個這樣的ADD-IN，你可到http://office.microsoft.com/downloads/2002/acclnk.aspx 當一個怎樣讀取數字Q: 怎樣在一數字元串分解為單獨的數字,比如將31234的值分解為31234,讀出後將相應的結果填充到固定的單元格 A:1用RIGHT() 或LEFT() 2.假設A1單元格為：31234，結果分別放入B1—B5， B1=MID（a1,1,1）;B2=MID（a1,2,1);B3=MID（a1,3,1）;B4=MID（a1,4,1）;B5=MID（a1,5,1） 3.用數據->分列->固定寬度...試試. 4.RIGHT(RIGHT(B2,1),1) 假設A1單元格為：31234，結果分別放入B1—B5， B1=LEFT（LEFT(a1,1),1）;B2=RIGHT（LEFT(a1,2),1);B3=RIGHT（LEFT(a1,3),1）;B4=RIGHT（LEFT(a1,4),1）;B5=RIGHT（LEFT(a1,5),1）怎樣才能快速算出有這樣一個問題：年終結帳：要將12個月的賬分成二筆來結，（假定數據如下）假定一筆為300或最接近300，剩餘的為另一筆。怎樣才能快速算出？ 1月 56.6 、2月 12.1 、3月 54.3 、4月 87.8 、5月 43.2 、6月 61.4 、7月 32.7 、8月 27.5 、9月 38.2 、10月 11.1 、11月 36.9 、12月 29.4 、總數 491.2 A: 我給出將12個月的賬分成若干份（即是未知的）結，條件還一樣。這個弄明白了，你想要的自然就會做出了。 E F G 1 1月 56.6 =IF(F1>=300,F1,$F$1) 2 2月 12.1 =IF(ISNUMBER(G1),$F$ & ROW(G1),IF(SUM(INDIRECT(G1 & :F & ROW(G2)))>300,SUM(INDIRECT(G1 & :F & ROW(G1))),G1)) 3 3月 54.3 =IF(ISNUMBER(G2),$F$ & ROW(G2),IF(SUM(INDIRECT(G2 & :F & ROW(G3)))>300,SUM(INDIRECT(G2 & :F & ROW(G2))),G2)) 4 4月 87.8 =IF(ISNUMBER(G3),$F$ & ROW(G3),IF(SUM(INDIRECT(G3 & :F & ROW(G4)))>300,SUM(INDIRECT(G3 & :F & ROW(G3))),G3)) 5 5月 43.2 =IF(ISNUMBER(G4),$F$ & ROW(G4),IF(SUM(INDIRECT(G4 & :F & ROW(G5)))>300,SUM(INDIRECT(G4 & :F & ROW(G4))),G4)) 6 6月 61.4 =IF(ISNUMBER(G5),$F$ & ROW(G5),IF(SUM(INDIRECT(G5 & :F & ROW(G6)))>300,SUM(INDIRECT(G5 & :F & ROW(G5))),G5)) 7 7月 32.7 =IF(ISNUMBER(G6),$F$ & ROW(G6),IF(SUM(INDIRECT(G6 & :F & ROW(G7)))>300,SUM(INDIRECT(G6 & :F & ROW(G6))),G6)) 8 8月 27.5 =IF(ISNUMBER(G7),$F$ & ROW(G7),IF(SUM(INDIRECT(G7 & :F & ROW(G8)))>300,SUM(INDIRECT(G7 & :F & ROW(G7))),G7)) 9 9月 38.2 =IF(ISNUMBER(G8),$F$ & ROW(G8),IF(SUM(INDIRECT(G8 & :F & ROW(G9)))>300,SUM(INDIRECT(G8 & :F & ROW(G8))),G8)) 10 10月 200 =IF(ISNUMBER(G9),$F$ & ROW(G9),IF(SUM(INDIRECT(G9 & :F & ROW(G10)))>300,SUM(INDIRECT(G9 & :F & ROW(G9))),G9)) 11 11月 36.9 =IF(ISNUMBER(G10),$F$ & ROW(G10),IF(SUM(INDIRECT(G10 & :F & ROW(G11)))>300,SUM(INDIRECT(G10 & :F & ROW(G10))),G10)) 12 12月 29.4 =IF(ISNUMBER(G11),$F$ & ROW(G11),IF(SUM(INDIRECT(G11 & :F & ROW(G12)))>300,SUM(INDIRECT(G11 & :F & ROW(G11))),G11)) 13 =IF(ISNUMBER(G12),,SUM(INDIRECT(G12 & :F & ROW(G13)))) 14 總數 491.2 最後結果為： E F G 1 1月 56.6 $F$1/2 2月 12.1 $F$1 /3 3月 54.3 $F$1/4 4月 87.8 $F$1/5 5月 43.2 $F$1 /6 6月 61.4 254 /7 7月 32.7 $F$6 /8 8月 27.5 $F$6 /9 9月 38.2 $F$6 /10 10月 200 159.8 /11 11月 36.9 $F$10 /12 12月 29.4 $F$10 /13 266.3 /14 總數 491.2 /說明：G6 為 F1:F5 之和、G10 為 F6:F9 之和、G1３為 F10:F12 之和。如何實現對日期格式的要求條件Q：在條件語句中如何實現符合某個時期的條件的記錄進行統計,比如有1-12月份的記錄單,需要實現對每個月里些數據的統計匯總/(格式如何?) A： dongmu 以下公式,A列為日期列,B列為數據,要求計算1月份的累計: =SUM(IF(MONTH(A:A)=1,B:B,0)) 此為數組公式,在輸完公式後,不要ENTER,而要CTRL+SHIFT+ENTER.(好象不足呀!程香宙)截取單元格里某個字元後的字元A： tof ：使用RIGET()或LEFT()函數就可以了,詳細可以參考EXCEL的幫助 liberty：比如這樣： a1內容為dfgsd2163.com,g2er.g 我需要提取出，號以後的字元，g2er.g 午餐：先使用SERACH或FIND函數找出「，」的位置，再用MID來取數，如A1＝123，456，我想取出「，」以右的數，可以＝MID（SEARCH（，），8）。可能我用的函數不行，不過思路一定行。　　你多看看幫助文件，我在網吧，此機無EXCEL無法試，不過我以前用過，用MID加SEARCH一定行的通，多試試吧。　　實在不行再給我郵箱發信了，願意幫忙！祝你好運。　　我認為能用函數解決的最好不用VBA，你說呢？ markxg ：假設A1中有dfgsd2163.com,g2er.g =RIGHT(A1,LEN(A1)-SEARCH(,,A1,1)) missle：我今天也試著用公式解決了你的問題，思路與MARK的一樣，只是他用的是SEARCH，我用的是FIND。但不是很清楚你的問題，是否要把「，」號一起給截取下來，如果是的話：公式應該是=RIGHT(A1,LEN(A1)-SEARCH(,,A1,1)+1)，否則「，」號是無法截取到的。把問題給縱深一下，如果單元格內有多個「，」的話，如A1中是：aa,bb,cc,dd 我想截取 cc,dd 該如何作呢？或者想知道這個單元格中含有多少個「，」又應該如何呢？（VBA的解決方法除外）午餐：Missle，你看仔細了，在Search函數中是有參數的，它允許你指定在第幾個出現顯出位置，如「，」你可以通過指定search參數選定第幾次出現時的位置，對於截取我更認為MID比LEFT和RIGHT好用if函數判斷請問:如何用if函數判斷,如果a1單元格大於0,b1單元格為0是錯誤,為非0是正確? A： dongmu if(and(a<=0,b=0),錯誤,正確) 葡萄：=if(a1>0,if(b1=0,錯誤,正確),條件一不滿足) 分別顯示總分最高的同學的班級、姓名、總分Q:問題1是這樣的: 1、在A1:A30單元格區域中輸入500至600之間的數值； 2、在B1單元格中輸入500； 3、在C1單元格中插入公式： COUNTIF(A1:A30,>=INDEX(B1,1)*0.9) 回車後C1單元格中顯示的結果是0，為何不能把A1:A30中輸入的數大於或等於500*0.9的單元格個數統計出來？錯在哪裡，該如何更正？問題2有下面一個表格：班級姓名政治語文數學總分 1 小東 90 90 90 270 /2 明明 95 92 90 277 /3 小英 96 89 91 276 /要在K1:K3的單元格中分別顯示總分最高的同學的班級、姓名、總分 .該如何設置公式？答案1：建議你好好看看COUNTIF的幫助，真是搞不明白，為什麼在B1里輸入500而不是450呢？ COUNTIF不支持一個以上的條件，你應該用DCOUNT。答案2： K1=INDEX(A2:F4,MATCH(K3,F2:F4,0),1) K2=INDEX(A2:F4,MATCH(K3,F2:F4,0),2) K3=MAX(F2:F4) 解答3：問題一：公式改為：=COUNTIF(A1:A30,>= & INDEX(B1,1)*0.9) 問題二：在K1:K3中分別輸入如下公式(假設A1為班級)： =LOOKUP($K$3,$F$2:$F$4,A2:A4) =LOOKUP($K$3,$F$2:$F$4,B2:B4) =LARGE(F2:F4,1) 解答4第一題:=COUNTIF(A1:A30,>=&B1*0.9) 第二題:K1==INDIRECT(A&MATCH(MAX(F1:F4),F1:F4)) K2=INDIRECT(B&MATCH(MAX(F1:F4),F1:F4)) K3=MAX(F2:F4)定有如上兩列數據A和B 現在想要統計滿足條件B＝8的並且在C列自動生成數據Q: A B C 427 8 427 /612 8 612 /924 8, 924 /22 16 409 /94 16 /620 16 /955 16 /409 8 請問 :假定有如上兩列數據A和B ,現在想要統計滿足條件B＝8的 ,並且在C列自動生成數據,我不懂公式怎麼寫?我知道篩選能夠做到 ,但是由於數據量比較大 ,想做一個模板，免去一些重複勞動 .A: dongmu =IF(ROW($A1)>COUNTIF($B$1:$B$8,8),,INDEX($A$1:$A$8,MATCH(SMALL(IF($B$1:$B$8=8,0,1)+ROW($B$1:$B$8)/100000,ROW ($A1)),IF($B$1:$B$8=8,0,1)+ROW($B$1:$B$8)/100000,0))) 老夏 L:B欄有100個選取條件,公式要修改100次 ,有甚麼靈活性?,給欄位名稱,插入頭兩列.B1=SUBTOTAL(9,B4:B65536) .解決掉所有問題 ,控制項找自動篩選.排名問題在A1:F6區域有下面一個表格：班級姓名政治語文數學總分 1 小東 90 90 90 270 /2 明明 95 92 90 277 /3 小英 96 89 91 276 /4 小劉 95 90 92 277 /5 小紅 95 91 92 278 /要在K1:K3的單元格中分別顯示總分最高的同學的班級、姓名、總分/在L1:L3的單元格中分別顯示總分第二的同學的班級、姓名、總分 /在M1:M3的單元格中分別顯示總分第三的同學的班級、姓名、總分 /注意期中277分的有兩人,不要出現第二名與第三名都是明明的結果. A: dongmu 定義A2:A6區域為班級；定義B2:B6區域為姓名；定義F2:F6區域為總分 K1={INDEX(班級,MATCH(LARGE(總分+1-ROW(總分)/100,ROW($A1)),總分+1-ROW(總分)/100,0))} L1={INDEX(姓名,MATCH(LARGE(總分+1-ROW(總分)/100,ROW($A1)),總分+1-ROW(總分)/100,0))} M1={INDEX(總分,MATCH(LARGE(總分+1-ROW(總分)/100,ROW($A1)),總分+1-ROW(總分)/100,0))}註：只要向下複製，便可得到1~6名的情況了。該公式可以無限排列。