[]
수식을 사용하여 열, 꺾은선, 승패스파크라인을 생성할 수 있습니다.
수식에는 다음과 같은 옵션이 있습니다:
옵션 | 설명 |
---|---|
Data | 스파크라인 데이터로 사용할 범위 참조입니다. 예: |
DataOrientation | 스파크라인 데이터 방향을 나타내는 숫자입니다. |
DateAxisData | 스파크라인 날짜 축 데이터로 사용할 범위 참조입니다. 예: |
DateAxisOrientation | 날짜 축 방향을 나타내는 숫자입니다. |
Setting | JSON 형식의 문자열입니다. 예: |
다음 표는 JSON 형식 문자열과 해당하는 스파크라인 설정을 보여줍니다.
Ask ChatGPT
JSON 속성 | SparklineSetting.Options 속성 |
---|---|
AXISCOLOR | axisColor |
FIRSTMARKERCOLOR | firstMarkerColor |
HIGHMARKERCOLOR | highMarkerColor |
LASTMARKERCOLOR | lastMarkerColor |
LOWMARKERCOLOR | lowMarkerColor |
MARKERSCOLOR | markersColor |
NEGATIVECOLOR | negativeColor |
SERIESCOLOR | seriesColor |
DISPLAYEMPTYCELLSAS | displayEmptyCellsAs |
RIGHTTOLEFT | rightToLeft |
DISPLAYHIDDEN | displayHidden |
DISPLAYXAXIS | displayXAxis |
SHOWFIRST | showFirst |
SHOWHIGH | showHigh |
SHOWLAST | showLast |
SHOWLOW | showLow |
SHOWNEGATIVE | showNegative |
SHOWMARKERS | showMarkers |
MANUALMAX | manualMax |
MANUALMIN | manualMin |
MAXAXISTYPE | maxAxisType |
MINAXISTYPE | minAxisType |
LINEWEIGHT | lineWeight |
꺾은선, 열, 승패 수식은 다음과 같은 형식을 가집니다:
Ask ChatGPT
=LINESPARKLINE(data, dataOrientation, dateAxisData, dateAxisOrientation, setting)
=COLUMNSPARKLINE(data, dataOrientation, dateAxisData, dateAxisOrientation, setting)
=WINLOSSSPARKLINE(data, dataOrientation, dateAxisData, dateAxisOrientation, setting)
다음 코드 샘플은 수식을 사용하여 열, 꺾은선, 승패 스파크라인을 생성합니다.
Ask ChatGPT
activeSheet.addSpan(0, 0, 1, 5);
activeSheet.getCell(0, 0, GC.Spread.Sheets.SheetArea.viewport).value("The company revenue in 2014").font("20px Arial").hAlign(GC.Spread.Sheets.HorizontalAlign.center).vAlign(GC.Spread.Sheets.VerticalAlign.center);
var table1 = activeSheet.tables.add("table1", 1, 0, 13, 5, GC.Spread.Sheets.Tables.TableThemes.medium4);
table1.filterButtonVisible(false);
activeSheet.setValue(1, 0, "Month");
activeSheet.setValue(1, 1, "Revenue");
activeSheet.setValue(1, 2, "Diagram 1");
activeSheet.setValue(1, 3, "Diagram 2");
activeSheet.setValue(1, 4, "Diagram 3");
for (var i = 2; i < 14; i++) {
activeSheet.setValue(i, 0, new Date(2014, i - 1, 1));
}
activeSheet.getRange(-1, 0, -1, 1).formatter("mm/dd/yyyy");
activeSheet.setValue(2, 1, 10);
activeSheet.setValue(3, 1, 20);
activeSheet.setValue(4, 1, 50);
activeSheet.setValue(5, 1, 100);
activeSheet.setValue(6, 1, 30);
activeSheet.setValue(7, 1, - 10);
activeSheet.setValue(8, 1, -25);
activeSheet.setValue(9, 1, 60);
activeSheet.setValue(10, 1, 50);
activeSheet.setValue(11, 1, 30);
activeSheet.setValue(12, 1, 80);
activeSheet.setValue(13, 1, 88);
activeSheet.addSpan(2, 2, 10, 1);
activeSheet.setFormula(2, 2, '=LINESPARKLINE(B3:B14,0,A3:A14,0,"{axisColor:rgb(255,255,0),firstMarkerColor:brown,highMarkerColor:red,lastMarkerColor:blue,lowMarkerColor:green,markersColor:purple,negativeColor:yellowgreen,seriesColor:pink,displayXAxis:true,showFirst:true,showHigh:true,showLast:true,showLow:true,showNegative:true,showMarkers:true,lineWeight:3,displayHidden:false,displayEmptyCellsAs:1,rightToLeft:false,minAxisType:1,maxAxisType:1,manualMax:5,manualMin:-3}")');
activeSheet.addSpan(2, 3, 10, 1);
activeSheet.setFormula(2, 3, '=COLUMNSPARKLINE(B3:B14,0,A3:A14,0,"{axisColor:rgb(255,255,0),firstMarkerColor:brown,highMarkerColor:red,lastMarkerColor:blue,lowMarkerColor:green,markersColor:purple,negativeColor:yellowgreen,seriesColor:pink,displayXAxis:true,showFirst:true,showHigh:true,showLast:true,showLow:true,showNegative:true,showMarkers:true,lineWeight:3,displayHidden:false,displayEmptyCellsAs:1,rightToLeft:false,minAxisType:1,maxAxisType:1,manualMax:5,manualMin:-3}")');
activeSheet.addSpan(2, 4, 10, 1);
activeSheet.setFormula(2, 4, '=WINLOSSSPARKLINE(B3:B14,0,A3:A14,0)');
activeSheet.setRowHeight(0, 50);
for (var i = 1; i < 14; i++) {
activeSheet.setRowHeight(i, 25);
}
activeSheet.setColumnWidth(0, 80);
activeSheet.setColumnWidth(2, 200);
activeSheet.setColumnWidth(3, 200);
activeSheet.setColumnWidth(4, 200);