我正在嘗試遵循兩種方式...
僅洗掉 * ...
=QUERY(ARRAYFORMULA(IFERROR(SUBSTITUTE(IMPORTHTML("https://niftyinvest.com/option-chain/"&M5&"?expiry="&$N$5,"table",1),"*",)*1,SUBSTITUTE(IMPORTHTML("https://niftyinvest.com/option-chain/"&M5&"?expiry="&$N$5,"table",1),"*",))),"Select Col1, Col2, Col4, Col5, Col3, Col6, Col9, Col7, Col8, Col10, Col11 where "&TEXTJOIN(" and ", 1, "Col" &SEQUENCE(11) &" <> 0"))要同時洗掉 * 并用零替換“-”...
=QUERY(ARRAYFORMULA(IFERROR(SUBSTITUTE(SUBSTITUTE(IMPORTHTML("https://niftyinvest.com/option-chain/"&M5&"?expiry="&$N$5,"table",1),"*",""),"-","0")*1,SUBSTITUTE(SUBSTITUTE(IMPORTHTML("https://niftyinvest.com/option-chain/"&M5&"?expiry="&$N$5,"table",1),"*",""),"-","0"))),"Select Col1, Col2, Col4, Col5, Col3, Col6, Col9, Col7, Col8, Col10, Col11 where "&TEXTJOIN(" and ", 1, "Col" &SEQUENCE(11) &" <> 0"))
但結果是第二種情況下的資料較少。如何在第二種情況下獲取所有資料。考慮 Col6 資料來比較 plz。假設單元格值..... M5 = BAJFINANCE N5 = 30June2022
uj5u.com熱心網友回復：
利用：
=ARRAYFORMULA(SPLIT(FLATTEN(QUERY(TRANSPOSE(QUERY({REGEXREPLACE(SUBSTITUTE(
IMPORTHTML("https://niftyinvest.com/option-chain/"&A1&"?expiry="&B1, "table", 1)&"", "*", ), "^-$", "0"),
FLATTEN(QUERY(TRANSPOSE(QUERY(REGEXREPLACE(SUBSTITUTE(
IMPORTHTML("https://niftyinvest.com/option-chain/"&A1&"?expiry="&B1, "table", 1)&"", "*", ), "^-$", "0"),
"select Col1,Col2,Col3,Col4,Col5,Col7,Col8,Col9,Col10,Col11")*1),,9^9))},
"select Col1, Col2,Col4,Col5,Col3,Col6,Col9,Col7,Col8,Col10,Col11
where Col12 <> '0 0 0 0 0 0 0 0 0 0'", 1)&"?"),,9^9)), "?", 1, 0))
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/496017.html