Yahoo Answers is shutting down on May 4th, 2021 (Eastern Time) and beginning April 20th, 2021 (Eastern Time) the Yahoo Answers website will be in read-only mode. There will be no changes to other Yahoo properties or services, or your Yahoo account. You can find more information about the Yahoo Answers shutdown and how to download your data on this help page.

小黃 asked in 電腦與網際網路軟體 · 1 decade ago

excel 文字定義

請問excel 如b表格之中a1儲存格文字要同a表格a1儲存格,可以如下的函數

=MID(a表格!a1,1,3)

但如果b表格之中a2儲存格文字要同a表格a4儲存格,請問要如何修改變數,謝謝

4 Answers

Rating
  • 顯栓
    Lv 7
    1 decade ago
    Favorite Answer

    將A1公式修改為:

    =MID(INDIRECT("a表格!A"&((ROW()-1)*3)+1),1,3)

    再將公式下拉[填滿] 其他儲存格,則A2=a表格A4

    INDIRECT

    傳回一文字串所指定的參照位址。

    ROW()在A1=1則(1-1)*3+1=1

    ROW()在A2=2則(2-1)*3+1=4

    2007-09-21 13:09:38 補充:

    =MID(INDIRECT("[檔名.xls]a表格!A"&((ROW()-1)*3)+1),1,3)

    檔名自行修改。

  • 1 decade ago

    如果是在不同的檔案時,又如何去修改參數呢??

    謝謝

  • 1 decade ago

    安安..

    你說明的需求和採用的函數..怪怪的耶..

    因為mid是字串函數..

    如你式子所列..

    =MID(a表格!a1,1,3)

    是說..從a表格的a1欄..的第一位起抓回來三個字..

    和你說的..要欄位同那個表格的欄位..好像沒關係說..

    要相同..

    不是直接用..=Sheeta!a4

    就可以了..

    還是誤會了你的需求..??

    請參考囉..

    Source(s):
  • 繁禮
    Lv 6
    1 decade ago

    照上面的話,就改成=MID(a表格!a4,1,3)

    看看這樣是否可行?

Still have questions? Get your answers by asking now.