構文
引数
説明
lookup_value
(必須) 参照する値、セル
lookup_array
(必須) 検索する配列または範囲
return_array
(必須) 返す配列または範囲
[if_not_found]
(オプション) 有効な一致が見つからない場合は、[ifnotfound]に指定したテキストを返します。有効な一致が見つからず、[ifnotfound]も見つからない場合は、#N/Aが返されます。
[match_mode]
(オプション) 一致の種類を指定します。0 - 完全一致。 見つからない場合は、#N/A が返されます。 これが既定の設定です。-1 - 完全一致。 見つからない場合は、次の小さなアイテムが返されます。1 - 完全一致。 見つからない場合は、次の大きなアイテムが返されます。2 - *、?、および ～ が特別な意味を持つワイルドカードの一致。
[search_mode]
(オプション) 使用する検索モードを指定します。1 - 先頭の項目から検索を実行します。 これが既定の設定です。-1 - 末尾の項目から逆方向に検索を実行します。2 - 昇順で並べ替えられた検索範囲を使用してバイナリ検索を実行します。 並べ替えられていない場合、無効な結果が返されます。-2 - 降順で並べ替えられた検索範囲を使用してバイナリ検索を実行します。 並べ替えられていない場合、無効な結果が返されます。
見つからない場合のメッセージ
XLOOKUPは、一致するものが見つからない場合、Excelの他の一致関数と同様に#N/Aエラーを返します。他のマッチ関数とは異なり、XLOOKUPは[ifnotfound]というオプションの引数をサポートしており、これを使用することで、#N/Aエラーが表示される場合に、#N/Aエラーを上書きすることができます。notfoundの典型的な値は、"Not found"、"No match"、"No result "などです。notfound に値を指定する場合は、テキストを二重引用符 ("") で囲みます。
注意: [ifnotfound]に空文字列("")を指定した場合は注意が必要です。一致するものが見つからなかった場合、XLOOKUPは#N/Aの代わりに何も表示しません。一致しないときに#N/Aエラーを表示したい場合は、引数を完全に省略してください。
XLOOKUPのメリット
XLOOKUPは、VLOOKUPと比較して、いくつかの重要な利点があります。
XLOOKUPは、検索値の右または左のどちらのデータも検索することができます。
XLOOKUP は複数の結果を返すことができます。 (上記デモの例 3)
XLOOKUP の一致モードのデフォルトは完全一致です。 (VLOOKUP のデフォルトは近似)
XLOOKUP は垂直方向と水平方向のデータを扱うことができます。
XLOOKUPは、逆引き検索が可能です（最後から最初まで）。
XLOOKUPは、1つの値だけでなく、行や列全体を返すことができます。
<template>
<div class="sample-tutorial">
<gc-spread-sheets class="sample-spreadsheets" @workbookInitialized="initSpread">
<gc-worksheet>
</gc-worksheet>
</gc-spread-sheets>
</div>
</template>
<script setup>
import '@mescius/spread-sheets-vue';
import { ref } from "vue";
import GC from '@mescius/spread-sheets';
import '@mescius/spread-sheets-resources-ja';
GC.Spread.Common.CultureManager.culture("ja-jp");
const spreadRef = ref(null);
let initSpread = function (spread) {
spreadRef.value = spread;
spread.options.allowDynamicArray = true;
initStyles(spread);
spread.setSheetCount(9);
spread.suspendPaint();
spread.suspendCalcService();
initSheet1(spread.getSheet(0));
initSheet2(spread.getSheet(1));
initSheet3(spread.getSheet(2));
initSheet4(spread.getSheet(3));
initSheet5(spread.getSheet(4));
initSheet6(spread.getSheet(5));
initSheet7(spread.getSheet(6));
initSheet8(spread.getSheet(7));
initSheet9(spread.getSheet(8));
spread.resumeCalcService();
spread.resumePaint();
}
let initStyles = function (spread) {
let introStyle = new GC.Spread.Sheets.Style();
introStyle.name = 'intro';
introStyle.font = 'normal bold 16px Segoe UI';
introStyle.foreColor = "#172b4d";
spread.addNamedStyle(introStyle);
let introStyle1 = new GC.Spread.Sheets.Style();
introStyle1.name = 'intro1';
introStyle1.font = 'normal bold 14px Calibri';
introStyle1.hAlign = 0;
introStyle1.vAlign = 1;
introStyle1.foreColor = "#172b4d";
spread.addNamedStyle(introStyle1);
let formulaStyle = new GC.Spread.Sheets.Style();
formulaStyle.name = 'formula';
formulaStyle.font = 'normal bold 12px Consolas';
formulaStyle.foreColor = "#c00000";
introStyle1.vAlign = 1;
spread.addNamedStyle(formulaStyle);
let tableHeaderStyle = new GC.Spread.Sheets.Style();
tableHeaderStyle.name = 'tableHeader';
tableHeaderStyle.font = "normal bold 14.7px Calibri";
tableHeaderStyle.hAlign = 1;
tableHeaderStyle.backColor = "#d9e1f2";
spread.addNamedStyle(tableHeaderStyle);
let tableContentStyle = new GC.Spread.Sheets.Style();
tableContentStyle.name = 'tableContent';
tableContentStyle.font = "normal normal 14.7px Calibri";
tableContentStyle.hAlign = 1;
spread.addNamedStyle(tableContentStyle);
let sourceStyle = new GC.Spread.Sheets.Style();
sourceStyle.name = 'source';
sourceStyle.hAlign = 0;
sourceStyle.backColor = "#fce8ce";
spread.addNamedStyle(sourceStyle);
let resultStyle = new GC.Spread.Sheets.Style();
resultStyle.name = 'result';
resultStyle.hAlign = 0;
resultStyle.backColor = "#e2efda";
spread.addNamedStyle(resultStyle);
}
let initSheet1 = function (sheet) {
sheet.name('Use Case');
let table1Source = {
name: 'Quarterly Employee Commissions',
data: [
{ salesRap: 'Jim', quarter: 'Q1', revenue: 351 },
{ salesRap: 'Jim', quarter: 'Q2', revenue: 210 },
{ salesRap: 'Kevin', quarter: 'Q1', revenue: 687 },
{ salesRap: 'Sarah', quarter: 'Q1', revenue: 300 },
{ salesRap: 'Sarah', quarter: 'Q2', revenue: 809 },
{ salesRap: 'Kevin', quarter: 'Q2', revenue: 285 },
{ salesRap: 'Bob', quarter: 'Q1', revenue: 110 }
]
};
sheet.addSpan(1, 1, 1, 6);
sheet.setValue(1, 1, table1Source.name);
sheet.getCell(1, 1).hAlign(1).font("normal bold 15px Calibri");
sheet.setColumnWidth(1, 83);
sheet.setColumnWidth(2, 73);
sheet.setColumnWidth(3, 77);
sheet.setColumnWidth(4, 122);
sheet.setColumnWidth(5, 134);
sheet.setColumnWidth(6, 98);
let table1 = sheet.tables.add('Table1', 2, 1, 7, 6);
table1.style(GC.Spread.Sheets.Tables.TableThemes.medium2);
let table1Column1 = new GC.Spread.Sheets.Tables.TableColumn(1, "salesRap", "Sales Rap");
let table1Column2 = new GC.Spread.Sheets.Tables.TableColumn(2, "quarter", "Quarter");
let table1Column3 = new GC.Spread.Sheets.Tables.TableColumn(3, "revenue", "Revenue");
let table1Column4 = new GC.Spread.Sheets.Tables.TableColumn(4, null, "Comm Category");
let table1Column5 = new GC.Spread.Sheets.Tables.TableColumn(5, null, "Comm Percentage", "0%");
let table1Column6 = new GC.Spread.Sheets.Tables.TableColumn(6, null, "Commission");
table1.autoGenerateColumns(false);
table1.bind([table1Column1, table1Column2, table1Column3, table1Column4, table1Column5, table1Column6], 'data', table1Source);
let table2Source = {
name: "Commissions Table",
data: [
{ category: 1, sales: 100, percentage: 0.05 },
{ category: 2, sales: 200, percentage: 0.1 },
{ category: 3, sales: 400, percentage: 0.15 },
{ category: 4, sales: 800, percentage: 0.20 }
]
};
sheet.addSpan(1, 8, 1, 3);
sheet.setValue(1, 8, table2Source.name);
sheet.getCell(1, 8).hAlign(1).font("normal bold 15px Calibri");
sheet.setColumnWidth(8, 88);
sheet.setColumnWidth(9, 57);
sheet.setColumnWidth(10, 91);
let table2 = sheet.tables.add('Table2', 2, 8, 4, 3);
table2.style(GC.Spread.Sheets.Tables.TableThemes.medium2);
let table2Column1 = new GC.Spread.Sheets.Tables.TableColumn(1, "category", "Category");
let table2Column2 = new GC.Spread.Sheets.Tables.TableColumn(2, "sales", "Sales");
let table2Column3 = new GC.Spread.Sheets.Tables.TableColumn(3, "percentage", "Percentage", "0%");
table2.autoGenerateColumns(false);
table2.bind([table2Column1, table2Column2, table2Column3 ], 'data', table2Source);
table1.setColumnDataFormula(3, '=XMATCH([@Revenue],Table2[Sales],-1,1)');
table1.setColumnDataFormula(4, '=XLOOKUP([@[Comm Category]],Table2[Category],Table2[Percentage],0,0,1)');
table1.setColumnDataFormula(5, '=[@Revenue]*[@[Comm Percentage]]');
}
let initSheet2 = function (sheet) {
sheet.name('basic exact match');
let intro = '#1 - basic exact match';
let formula = '=XLOOKUP(H5,B6:B10,D6:D10)';
sheet.setValue(1, 1, intro);
sheet.setStyle(1, 1, 'intro');
sheet.setValue(2, 1, formula);
sheet.setStyle(2, 1, 'formula');
let data = [
["Movie","Year","Rank","Sales"],
["Fargo",1996,5,61],
["L.A. Confidential",1997,4,126],
["The Sixth Sense",1999,1,673],
["Toy Story",1995,2,362],
["Unforgiven",1992,3,159]
];
sheet.setArray(4, 1, data);
for (let i = 0; i < data.length; i++) {
for (let j = 0; j < data[i].length; j ++) {
let styleName;
if (i === 0) {
styleName = 'tableHeader';
} else {
styleName = 'tableContent';
}
sheet.setStyle(4 + i, 1 + j, styleName);
}
}
sheet.setColumnWidth(1, 126);
sheet.setValue(4, 6, 'Movie');
sheet.setStyle(4, 6, 'source');
sheet.setValue(5, 6, 'Rank');
sheet.setStyle(5, 6, 'result');
sheet.setValue(4, 7, 'Toy Story');
sheet.setFormula(5, 7, formula);
}
let initSheet3 = function (sheet) {
sheet.name('basic approximate match');
let intro = '#2 - basic approximate match';
let formula = '=XLOOKUP(E6,B6:B10,C6:C10,,-1)';
sheet.setValue(1, 1, intro);
sheet.setStyle(1, 1, 'intro');
sheet.setValue(2, 1, formula);
sheet.setStyle(2, 1, 'formula');
let data = [
["Quantily","Discount"],
[0,0],
[10,0.05],
[25,0.1],
[50,0.2],
[100,0.25]
];
sheet.setArray(4, 1, data);
for (let i = 0; i < data.length; i++) {
for (let j = 0; j < data[i].length; j ++) {
let styleName;
if (i === 0) {
styleName = 'tableHeader';
} else {
styleName = 'tableContent';
}
sheet.setStyle(4 + i, 1 + j, styleName);
}
}
sheet.setValue(4, 4, 'Quantily');
sheet.setStyle(4, 4, 'source');
sheet.setValue(4, 5, 'Result');
sheet.setStyle(4, 5, 'result');
sheet.setValue(5, 4, 28);
sheet.setFormula(5, 5, formula);
}
let initSheet4 = function (sheet) {
sheet.name('multiple value');
let intro = '#3 - multiple values';
let formula = '=XLOOKUP(B6,B9:B16,C9:E16)';
sheet.setValue(1, 1, intro);
sheet.setStyle(1, 1, 'intro');
sheet.setValue(2, 1, formula);
sheet.setStyle(2, 1, 'formula');
let data = [
["ID","First","Last","Department"],
[610,"Janet","Farley","Fulfillment"],
[798,"Steven","Batista","Sales"],
[841,"Evelyn","Monet","Fulfillment"],
[886,"Marilyn","Bradley","Fulfillment"],
[622,"Jonathan","Adder","Marketing"],
[601,"Adrian","Birt","Engineering"],
[869,"Julie","Irons","Sales"],
[867,"Erica","Tan","Fulfillment"]
];
sheet.setArray(7, 1, data);
for (let i = 0; i < data.length; i++) {
for (let j = 0; j < data[i].length; j ++) {
let styleName;
if (i === 0) {
styleName = 'tableHeader';
} else {
styleName = 'tableContent';
}
sheet.setStyle(7 + i, 1 + j, styleName);
}
}
sheet.setColumnWidth(4, 100);
sheet.setValue(4, 1, 'ID');
sheet.setStyle(4, 1, 'source');
sheet.setValue(4, 2, 'First');
sheet.setStyle(4, 2, 'result');
sheet.setValue(4, 3, 'Last');
sheet.setStyle(4, 3, 'result');
sheet.setValue(4, 4, 'Department');
sheet.setStyle(4, 4, 'result');
sheet.setValue(5, 1, 841);
sheet.setFormula(5, 2, formula);
}
let initSheet5 = function (sheet) {
sheet.name('two-way lookup');
let intro = '#4 - two-way lookup';
let formula = '=XLOOKUP(I6,C5:F5,XLOOKUP(I5,B6:B10,C6:F10))';
sheet.setValue(1, 1, intro);
sheet.setStyle(1, 1, 'intro');
sheet.setValue(2, 1, formula);
sheet.setStyle(2, 1, 'formula');
let data = [
[null,"A","B","C","D"],
["Vinyl",10,11.5,13.23,15.21],
["Wood",12,13.8,15.87,18.25],
["Glass",15,17.25,19.84,22.81],
["Steel",18,20.7,23.81,27.38],
["Titanium",23,26.45,30.42,34.98]
];
sheet.setArray(4, 1, data);
for (let i = 0; i < data.length; i++) {
for (let j = 0; j < data[i].length; j ++) {
let styleName;
if (i === 0) {
styleName = 'tableHeader';
} else {
styleName = 'tableContent';
}
sheet.setStyle(4 + i, 1 + j, styleName);
}
}
sheet.setValue(4, 7, 'Material');
sheet.setStyle(4, 7, 'source');
sheet.setValue(4, 8, 'Glass');
sheet.setValue(5, 7, 'Group');
sheet.setStyle(5, 7, 'source');
sheet.setValue(5, 8, 'B');
sheet.setValue(6, 7, 'Result');
sheet.setStyle(6, 7, 'result');
sheet.setFormula(6, 8, formula);
}
let initSheet6 = function (sheet) {
sheet.name('not found message');
let intro = '#5 - not found message';
let formula = '=XLOOKUP(H4,B5:B9,E5:E9,"Not found")';
sheet.setValue(1, 1, intro);
sheet.setStyle(1, 1, 'intro');
sheet.setValue(2, 1, formula);
sheet.setStyle(2, 1, 'formula');
let data = [
["Movie","Year","Rank","Sales"],
["Fargo",1996,5,61],
["L.A. Confidential",1997,4,126],
["The Sixth Sense",1999,1,673],
["Toy Story",1995,2,362],
["Unforgiven",1992,3,159]
];
sheet.setArray(4, 1, data);
for (let i = 0; i < data.length; i++) {
for (let j = 0; j < data[i].length; j ++) {
let styleName;
if (i === 0) {
styleName = 'tableHeader';
} else {
styleName = 'tableContent';
}
sheet.setStyle(4 + i, 1 + j, styleName);
}
}
sheet.setColumnWidth(1, 126);
sheet.setValue(4, 6, 'Movie');
sheet.setStyle(4, 6, 'source');
sheet.setValue(5, 6, 'Rank');
sheet.setStyle(5, 6, 'result');
sheet.setValue(4, 7, 'Godzilla');
sheet.setFormula(5, 7, formula);
}
let initSheet7 = function (sheet) {
sheet.name('array arguments');
let intro = '#6 - array arguments';
sheet.setValue(1, 1, intro);
sheet.setStyle(1, 1, 'intro');
sheet.setValue(4, 1, 'Years');
sheet.setStyle(4, 1, 'tableHeader');
sheet.setValue(5, 1, 'Sales');
sheet.setStyle(5, 1, 'tableHeader');
sheet.setValue(6, 1, 'Year');
sheet.setStyle(6, 1, 'source');
sheet.setValue(7, 1, 'Result');
sheet.setStyle(7, 1, 'result');
sheet.setValue(4, 2, '{1996,1997,1999,1995,1992}');
sheet.setValue(5, 2, '{61,126,673,362,159}');
sheet.setValue(6, 2, 1997);
sheet.setColumnWidth(1, 126);
let formula1 = '=XLOOKUP(C7,{1996,1997,1999,1995,1992},{61,126,673,362,159})';
sheet.setValue(2, 1, formula1);
sheet.setStyle(2, 1, 'formula');
sheet.setFormula(7, 2, formula1);
}
let initSheet8 = function (sheet) {
sheet.name('return reference');
let intro = '#7 - return reference';
let formula = '=SUM(XLOOKUP(B6,B9:B13,E9:E13):XLOOKUP(C6,B9:B13,E9:E13))';
sheet.setValue(1, 1, intro);
sheet.setStyle(1, 1, 'intro');
sheet.setValue(2, 1, formula);
sheet.setStyle(2, 1, 'formula');
let data = [
["Product","Qty","Price","Total"],
["Apple",23,0.52,11.9],
["Grape",98,0.77,75.28],
["Pear",75,0.24,18.16],
["Banana",95,0.18,17.25],
["Cherry",42,0.16,6.8]
];
sheet.setArray(7, 1, data);
for (let i = 0; i < data.length; i++) {
for (let j = 0; j < data[i].length; j ++) {
let styleName;
if (i === 0) {
styleName = 'tableHeader';
} else {
styleName = 'tableContent';
}
sheet.setStyle(7 + i, 1 + j, styleName);
}
}
// sheet.setColumnWidth(4, 100);
sheet.setValue(4, 1, 'Start');
sheet.setStyle(4, 1, 'source');
sheet.setValue(4, 2, 'End');
sheet.setStyle(4, 2, 'source');
sheet.setValue(4, 3, 'Total');
sheet.setStyle(4, 3, 'result');
sheet.setValue(5, 1, 'Grape');
sheet.setValue(5, 2, 'Banana');
sheet.setFormula(5, 3, formula);
}
let initSheet9 = function (sheet) {
sheet.name('special cases');
let intro = '#test - special cases';
sheet.setValue(1, 1, intro);
sheet.setStyle(1, 1, 'intro');
let data = [
["Movie","Year","Rank","Sales"],
["Fargo",1996,5,61],
["L.A. Confidential",1997,4,126],
["The Sixth Sense",1999,1,673],
["Toy Story",1995,2,362],
["Unforgiven",1992,3,159]
];
sheet.setArray(1, 1, data);
for (let i = 0; i < data.length; i++) {
for (let j = 0; j < data[i].length; j ++) {
let styleName;
if (i === 0) {
styleName = 'tableHeader';
} else {
styleName = 'tableContent';
}
sheet.setStyle(1 + i, 1 + j, styleName);
}
}
sheet.setColumnWidth(1, 126);
let formula1 = '=XLOOKUP(C2,C2:E2,C3:E3)';
sheet.setValue(8, 1, 'Row search');
sheet.setStyle(8, 1, 'intro1');
sheet.setValue(8, 2, formula1);
sheet.setStyle(8, 2, 'formula');
sheet.setFormula(8, 5, formula1);
let formula2 = '=XLOOKUP(C4,C2:C7,D2:D7)';
sheet.setValue(9, 1, 'Column Search');
sheet.setStyle(9, 1, 'intro1');
sheet.setValue(9, 2, formula2);
sheet.setStyle(9, 2, 'formula');
sheet.setFormula(9, 5, formula2);
let formula3 = '=XLOOKUP(B5,B2:B7,C2:E2)';
sheet.setValue(10, 1, 'Not same direction');
sheet.setStyle(10, 1, 'intro1');
sheet.setValue(10, 2, formula3);
sheet.setStyle(10, 2, 'formula');
sheet.setFormula(10, 5, formula3);
let formula4 = '=XLOOKUP(B3,B3,E5)';
sheet.setValue(11, 1, 'Single Search');
sheet.setStyle(11, 1, 'intro1');
sheet.setValue(11, 2, formula4);
sheet.setStyle(11, 2, 'formula');
sheet.setFormula(11, 5, formula4);
let formula5 = '=XLOOKUP(B5,C4,D6)';
sheet.setValue(12, 1, 'Single Search');
sheet.setStyle(12, 1, 'intro1');
sheet.setValue(12, 2, formula5);
sheet.setStyle(12, 2, 'formula');
sheet.setFormula(12, 5, formula5);
let formula6 = '=XLOOKUP(D2,B2:E2,B3:E7)';
sheet.setValue(13, 1, 'Spill Column');
sheet.setStyle(13, 1, 'intro1');
sheet.setValue(13, 2, formula6);
sheet.setStyle(13, 2, 'formula');
sheet.setFormula(13, 5, formula6);
}
</script>
<style scoped>
#app {
height: 100%;
}
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
.sample-spreadsheets {
width: 100%;
height: 100%;
overflow: hidden;
float: left;
}
.options-container {
float: right;
width: 280px;
padding: 12px;
height: 100%;
box-sizing: border-box;
background: #fbfbfb;
overflow: auto;
}
.option-row {
font-size: 14px;
padding: 5px;
margin-top: 10px;
}
#switchAutoMergeMode {
margin: 10px 0px;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
</style>
<!DOCTYPE html>
<html style="height:100%;font-size:14px;">
<head>
<meta charset="utf-8" />
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
<title>SpreadJS VUE</title>
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css"
href="$DEMOROOT$/ja/vue3/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/ja/vue3/node_modules/systemjs/dist/system.src.js"></script>
<script src="./systemjs.config.js"></script>
<script src="./compiler.js" type="module"></script>
<script>
var System = SystemJS;
System.import("./src/app.js");
System.import('$DEMOROOT$/ja/lib/vue3/license.js');
</script>
</head>
<body>
<div id="app"></div>
</body>
</html>
(function (global) {
SystemJS.config({
transpiler: 'plugin-babel',
babelOptions: {
es2015: true
},
paths: {
// paths serve as alias
'npm:': 'node_modules/'
},
packageConfigPaths: [
'./node_modules/*/package.json',
"./node_modules/@mescius/*/package.json",
"./node_modules/@babel/*/package.json",
"./node_modules/@vue/*/package.json"
],
map: {
'vue': "npm:vue/dist/vue.esm-browser.js",
'tiny-emitter': 'npm:tiny-emitter/index.js',
'plugin-babel': 'npm:systemjs-plugin-babel/plugin-babel.js',
"systemjs-babel-build": "npm:systemjs-plugin-babel/systemjs-babel-browser.js",
'@mescius/spread-sheets': 'npm:@mescius/spread-sheets/index.js',
'@mescius/spread-sheets-resources-ja': 'npm:@mescius/spread-sheets-resources-ja/index.js',
'@mescius/spread-sheets-vue': 'npm:@mescius/spread-sheets-vue/index.js'
},
meta: {
'*.css': { loader: 'systemjs-plugin-css' },
'*.vue': { loader: "../plugin-vue/index.js" }
}
});
})(this);
