EXCEL問題大全（3）

A列 B列 C列1 a2 a3 a1 b4 b我想當B2=B3，而且A2=1時，C2的值為OK，請問該如何寫這個公式？解答：=IF(AND(B2=B3,A2=1),"OK","")1解答：可使用 Workbook 的 Before_Print 事件.按 [Alt + F11] 進入 VB Editor,左側會有一個 [ThisWorkbook],(如果沒有, 按 [Ctrl+R] 叫出)在 [ThisWorkbook] 快按兩下, 貼上程序 --Private Sub Workbook_BeforePrint(Cancel As Boolean)[A1] = [A1] + 1End SubVBA，用EXCEL語句的IF函數，如何使用其條件關係假如，現在A1，A2，A3;B1,B2,B3隻要B1 不為空B1＜＞「」，那麼B2＝A2　B3＝A3如何使用其IF語句來實現？？解答：B2=IF($B$1<>"",$A2)B3=IF($B$1<>"",$A3)對於工作表中的資料庫,隨便舉一例:編 號 數 量1 數 量201 180 8002 190 9001 170 8001 160 80我如何計算(數量1-數量2)值的總和.並且必須滿足編號相同時, 只能減一次數量2. 即要計算成: (180+170+160-80)+(190-90). 注: 編號相同時,數量2的值一定相同.解答：設表格在A1:C5中=SUMIF(A2:A5）-INDEX(C1:C5,MATCH("01",A1:A5,0))-INDEX(C1:C5,MATCH("02",A1:A5,0))在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分的有兩人,不要出現第二名與第三名都是"明明"的結果.解答：定義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名的情況了。該公式可以無限排列。另註：這是數組公式，不需要輸入「{}」，在輸完公式後，請按ctrl+shift+enter，應將後面的100改成100000，比較好。A B C427 8 427/612 8 612 /924 8 924 /22 16 409 /94 16 /620 16 /955 16 /409 8 /請問：假定有如上兩列數據A和B，現在想要統計滿足條件B＝8的 ，並且在C列自動生成數據，我不懂公式怎麼寫，我知道篩選能夠做到，但是由於數據量比較大，想做一個模板，免去一些重複勞動。解答：=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)))爭議：B欄有100個選取條件，公式要修改100次，有甚麼靈活性給欄位名稱 ，插入頭兩列 ，B1=SUBTOTAL(9,B4:B65536)。解決掉所有問題 。控制項找自動篩選要辯解：不需要修改公式，直接複製就行了。如果是100個，開始時就將範圍修改好就行了。我的工作是在一個2000多個員工的紀錄（包括各種資料，如解答：如下表sheet1的內容a b c姓名 工號 部門張三 100 mm /李四 101 mm2 /.......表sheet2a b c姓名 部門 /李四 /。。。要在sheet2表中將sheet1表的相同人員資料複製過來可在b表 單元格b2中輸入」=vlookup(a2,sheet1!a1:gg2000,3,false)最好將sheet1的數據區先定義一下(1) 何解在公式內，運用 "比較運算符號" 時，要把數據連同比較運算符號用 "雙引號" 括住，而其它算術符號就不需要？例如：=Countif(A1:A30,">=10")是可以接受，但不加雙引號時，便遭拒絕。(2) 又請問：如上例，要比較的數值是要參照某單元格時，公式應怎樣做？解答：這是CountIF()的規定。第二個參數為字元串，必須用雙引號擴起來，除非條件是等於一個數值。比如：=COUNTIF(A1:A30,">=4")=COUNTIF(A1:A30,"condition")=COUNTIF(A1:A30,220)用引用作參數（假設B1值為4）：=COUNTIF(A1:A30,">=" & B1)解答：視圖－－批註 然後在批註上單擊右鍵 選擇設置批註格式records序號 姓名 性別 年齡1 李1 男 30 /2 李2 女 25 /3 李3 女 22/4 李4 男 31 /：： ： ： /99 李99 男 28解答：在「序列」與「姓名」之間插入一個空列，在其第一格輸入「條件」。在「條件」下面輸入公式「=IF(MOD(A5,2)=1,"單數","雙數")」，用「自動篩選」。到m2) 、n3=sum(b3到m3) 、有沒有辦法讓n那一列可以自動填充就是說每增加一行，就會自動計算n那一列的值 、多謝！解答：1：我有個笨方法，須用VBA，不知和你意否。Private Sub Worksheet_Change(ByVal Target As Excel.Range)If Target.Column < 14 And Cells(Target.Row, 14).Formula = "" Then Cells(Target.Row, 14).Formula = "=SUM(RC[-12]:RC[-1])"End Sub2：假設需要被SUM的數據存放在B列(第一個數據存放在單元格B2),將以下公式COPY到你希望顯示結果的單元格中:=IF(LEN(B2)>0,SUM(B$2:INDEX(B:B, ROW(), 1)), "")切記如果B列中有空單元格隔開了需要計算的數據, 必須以零填充該單元格.3：這個問題不用想得很複雜，就是要把n2單元格輸入公式後，拖曳向下複製公式。也許是我想得太簡單了。10行數據, 現在要每行間格2行, 要如何做解答：1：如sheet1！$A$1:$D$10中有連續10行資料，在sheet2中把sheet1中的數據每行間隔2行sheet2！A1中公式可用=IF(ROW()=1,Sheet1!A1,IF(MOD(ROW(),3)=1,INDEX(Sheet1!$A$1:$D$10,INT((ROW()-1)/2)+1,COLUMN()),""))然後填充公式（注意公式在SHEET2中的填充範圍，超過範圍會出錯！）2：小修改=IF(MOD(ROW(),3)=1,INDEX(Sheet1!$A$1:$Z$500,INT(ROW()/3)+1,COLUMN()),"")解答：[數據]-[有效性]-[序列]又問：好像選取數據只能在本頁面，有無高招選取另外頁面的數據源，甚至是另外工作薄的？解答：先在「插入-》名稱」中定義好其它頁面上需要引用的數據源的名稱（AA）。然後 [數據]-[有效性]-[序列] ，在「數據源」欄輸入「=AA」。或：若改其它活頁薄要如何修改下列式子( 假設活頁薄名稱為book1)=Sheet1!$A$2:$A$14(book2)有一個表格：承兌行 天數 實際天數深圳工行 98 101廣州交行 105 108鞍山工行 100 100如果「承兌行」一欄含有鞍山字樣，則「實際天數」等於「天數」；如果「承兌行」一欄不含有鞍山字樣，則「實際天數」等於「天數」加上3，請問：如何設置？解答：這個問題的重點在於如何判斷是否含有「鞍山」字樣。如果「鞍山」在字元串中的位置是固定的，則很容易用MID函數找到並進行判斷；如果不是，則需要用FIND或Search函數來找到再判斷。或：如果A列中是承兌行，B列是天數，C是....可以試用一下以下的公式：=IF(ISERROR(FIND("鞍山",A2)),B2+3,B2)D2>20那E2就顯示$200、如果D2>30那E2就顯示$300依此類推解答：=INT(D2/10)*100 ，當然，你的單元格格式設置成$格式就可以了。否則用，="$"&INT(D2/10)*100則該單元格成字元型 。當然，你也可以用IF函數，但它有7層的限制。if (D2>30, "300",IF(D2>20,"200"))有兩張工作表，內容都是電話號碼、用戶名稱，怎樣對兩張工作表進行比較？（把第一張工作表和第二張工作表中電話號碼相同的項目複製到新工作表中或加上標記）解答：先備份工作簿。假設SHEET1、SHEET2 兩張表的結構相同，A 列為用戶名，數據由第二行開始。新建SHEET3 並在其A2 單元格中輸入 =IF(ISNA(MATCH(Sheet2!$A2,Sheet1!$A:$A,0))," DELET THIS ROW ",Sheet2!A2) 並向下、向右填充。完成後SHEET3中多了SHEET2 和SHEET1 共有的用戶名，以及由"DELET THIS ROW"填充的數據行。剩下的就簡單了！解答：統計F4到F62的偶數單元格合計數值 。公式一｛=SUM(IF(MOD(ROW(F4:F62),2)=0,F4:F62))｝公式二 (要有安裝"分析工具箱")｛=SUM(IF(ISEVEN(ROW(F4:F62)),F4:F62))｝
推薦閱讀：