函数使用通配符

你可以在某些函数的搜索或者匹配的时候在比较条件中使用通配符 (?, *, ~) 。

<table border="1" style="border-collapse: collapse;width:90%;"> <tbody> <tr> <th style="width: 40%;">通配符</th> <th>Match</th> </tr> <tr> <td>?</td> <td>任意单个字符</td> </tr> <tr> <td>*</td> <td>任意字符</td> </tr> <tr> <td>~ 后跟 ?, *, 或者 ~ </td> <td>?, *, 或者 ~ 的字符</td> </tr> </tbody> </table> <p><strong>注意:</strong> 通配符只能在匹配条件为等于 (=) 的时候使用; 它不能在其它比较条件中使用 (&gt;,<,!=,<=,>=)。</p> <p>通配符能够在以下函数的条件参数中使用:</p> <ul> <li><strong>AVERAGEIF</strong></li> <li><strong>AVERAGEIFS</strong></li> <li><strong>COUNTIF</strong></li> <li><strong>COUNTIFS</strong></li> <li><strong>SUMIF</strong></li> <li><strong>SUMIFS</strong></li> <li><strong>MATCH</strong></li> <li><strong>SEARCH</strong></li> <li><strong>VLOOKUP</strong></li> <li><strong>HLOOKUP</strong></li> <li><strong>DAVERAGE</strong></li> <li><strong>DGET</strong></li> <li><strong>DMAX</strong></li> <li><strong>DMIN</strong></li> <li><strong>DPRODUCT</strong></li> <li><strong>DSTDEV</strong></li> <li><strong>DSTDEVP</strong></li> <li><strong>DSUM</strong></li> <li><strong>DVAR</strong></li> <li><strong>DVARP</strong></li> </ul>
window.onload = function () { var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 1 }); initSpread(spread); }; function initSpread(spread) { var spreadNS = GC.Spread.Sheets; var sheet = spread.sheets[0]; spread.suspendPaint(); sheet.options.allowCellOverflow = true; var array = [ ["Tree", "Height", "Age", "Yield", "Profit", "Height"], ["=App?e", ">10", null, null, null, "<16"], ["=P*"], ["Tree", "Height", "Age", "Yield", "Profit"], ["Apple", 18, 20, 14, 105], ["Pear", 12, 12, 10, 96], ["Cherry", 13, 14, 9, 105], ["AppLe", 14, 15, 10, 75], ["Pear", 9, 8, 8, 76.8], ["Apple", 8, 9, 6, 45] ]; sheet.setArray(0, 0, array); sheet.setColumnWidth(0, 100); var table = sheet.tables.add("Table1", 0, 0, 3, 6, spreadNS.Tables.TableThemes.medium9); table.rowFilter().filterButtonVisible(false); table = sheet.tables.add("Table2", 4, 0, 7, 5, spreadNS.Tables.TableThemes.medium9); table.rowFilter().filterButtonVisible(false); table = sheet.tables.add("Table3", 12, 0, 5, 6, spreadNS.Tables.TableThemes.medium9); table.rowFilter().filterButtonVisible(false); sheet.addSpan(12, 1, 1, 5); sheet.setValue(12, 0, 'Formula Result'); sheet.setValue(12, 1, 'Formula with wildcard'); sheet.addSpan(13, 1, 1, 5); sheet.setValue(13, 1, '=DCOUNT(A4:E10,"Age",A1:F3)'); sheet.setFormula(13, 0, 'DCOUNT(A4:E10,"Age",A1:F3)'); sheet.addSpan(14, 1, 1, 5); sheet.setValue(14, 1, '=SEARCH("?t", B1)'); sheet.setFormula(14, 0, 'SEARCH("?t", B1)'); sheet.addSpan(15, 1, 1, 5); sheet.setValue(15, 1, '=VLOOKUP("Ch*",A5:B10,2,0)'); sheet.setFormula(15, 0, 'VLOOKUP("Ch*",A5:B10,2,0)'); sheet.addSpan(17, 1, 1, 5); sheet.setValue(17, 1, '=COUNTIF(A5:B11,"App?e")'); sheet.setFormula(17, 0, 'COUNTIF(A5:B11,"App?e")'); sheet.setValue(18, 1, '=SEARCH("=P~*",A3)'); sheet.setFormula(18, 0, 'SEARCH("=P~*",A3)'); spread.resumePaint(); };
<!doctype html> <html style="height:100%;font-size:14px;"> <head> <meta name="spreadjs culture" content="zh-cn" /> <meta charset="utf-8" /> <meta name="viewport" content="width=device-width, initial-scale=1.0" /> <link rel="stylesheet" type="text/css" href="$DEMOROOT$/zh/purejs/node_modules/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity/spread-sheets-resources-zh/dist/gc.spread.sheets.resources.zh.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/spread/source/js/license.js" type="text/javascript"></script> <script src="app.js" type="text/javascript"></script> <link rel="stylesheet" type="text/css" href="styles.css"> </head> <body> <div class="sample-tutorial"> <div id="ss" class="sample-spreadsheets"></div> </div> </body> </html>
.sample-tutorial { position: relative; height: 100%; overflow: hidden; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; } .sample-spreadsheets { width: 100%; height: 100%; overflow: hidden; float: left; border: 1px solid gray; }