INDIRECT는 텍스트 문자열로 지정된 참조를 반환합니다. 참조가 즉시 계산되어 해당 내용을 표시합니다. 수식 자체를 변경하지 않고 수식 내부에서 셀에 대한 참조를 변경하려면 INDIRECT를 사용합니다.
INDIRECT는 a1 스타일 참조, r1c1 스타일 참조, 명명된 참조 또는 셀 참조를 텍스트 문자열로 지원합니다. 잘못된 셀 참조의 경우 INDIRECT는 #REF! 오류 값을 반환합니다.
window.onload = function () {
var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 2 });
initSpread(spread);
};
function initSpread(spread) {
spread.suspendPaint();
var sheet = spread.sheets[0], sheet2 = spread.sheets[1];
sheet.setArray(0, 0, [["b1", 1], ["A", 2], ["B", 3]]);
sheet2.setArray(0, 0, [["b3", 1], ["A", 3], ["B", 5]]);
sheet.setValue(3, 0, "Random");
sheet.setFormula(3, 1, "=RANDBETWEEN(1,100)");
sheet.setValue(4, 0, "Now");
sheet.setFormula(4, 1, "=NOW()");
sheet.getRange(4, 1, 1, 1).formatter("mm-dd-yyyy");
sheet.setArray(8, 0, [["Formula", "Description", "Result"]]);
sheet.setColumnWidth(0, 165);
sheet.setColumnWidth(1, 285);
var sampleData = [
['=INDIRECT("A1")', 'Value of A1 => "b1"'],
['=INDIRECT(A1)', 'Value of the reference of A1 => value of b1 => 1'],
['=INDIRECT("A"&(1+2))', 'Value of A3 => "B"'],
['=INDIRECT(A3&B2)', 'Value of B2 => 2'],
['=INDIRECT("Sheet2!"&A1)', 'Value of Sheet2\'s B1'],
['=INDIRECT("Sheet2!A1")', 'Value of Sheet2\'s A1']
];
for (var i = 0, len = sampleData.length; i < len; i++) {
var data = sampleData[i], row = 6 + i;
sheet.setArray(row, 0, [data]);
sheet.setFormula(row, 2, data[0]);
}
spread.resumePaint();
document.getElementById("btnAddCustomName").addEventListener('click',function () {
var name = document.getElementById("customName").value,
ref = document.getElementById("customReference").value,
row = 15;
if (name) {
try {
sheet.addCustomName(name, ref, 0, 0);
sheet.setArray(row, 0, [[`=INDIRECT(${name})`, `${name} is a custom name, if a valid cell reference is defined by it then use the value otherwise #REF!`]]);
sheet.setFormula(row, 2, '=INDIRECT(' + name + ')');
} catch (e) {
alert("invalid custom name");
}
}
});
}
<!doctype html>
<html style="height:100%;font-size:14px;">
<head>
<meta name="spreadjs culture" content="ko-kr"/>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css" href="$DEMOROOT$/ko/purejs/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/ko/purejs/node_modules/@mescius/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/ko/purejs/node_modules/@mescius/spread-sheets-resources-ko/dist/gc.spread.sheets.resources.ko.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 class="options-container">
<div class="option-row">
<label class="colorLabel">Enter a custom name below and set what it references. Then click the "Add custom name" button to add it to Spread for use in the cells.</label>
</div>
<div class="option-row">
<label class="sizedLabel">Custom name:</label>
<input type="text" value="name1" id="customName" />
</div>
<div class="option-row">
<label class="sizedLabel">Reference to:</label>
<input type="text" value="$A$1" id="customReference" />
</div>
<div class="option-row">
<input type="button" id="btnAddCustomName" value="Add custom name" title="Add a custom name for sample" />
</div>
</div>
</div>
</body>
</html>
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
.sample-spreadsheets {
width: calc(100% - 280px);
height:100%;
overflow: hidden;
float: left;
}
.options-container {
float: right;
width: 280px;
overflow: auto;
padding: 12px;
height: 100%;
box-sizing: border-box;
background: #fbfbfb;
}
.option-row {
margin-bottom: 12px;
}
input {
width: 100%;
padding: 4px 6px;
margin-bottom: 6px;
box-sizing: border-box;
}
input[type=button] {
width: auto;
}
label {
display: block;
margin-bottom: 6px;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}