| <!DOCTYPE html> |
| <html lang="ja"> |
| <head> |
| <meta charset="UTF-8"> |
| <title>fableCalc v1.0</title> |
| <script src="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/4.4.1/chart.umd.min.js"></script> |
| <script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.18.5/xlsx.full.min.js"></script> |
| <style> |
| :root{--green:#1D6F42;--green2:#2E8B57;--bd:#d0d7de;--grid:#e3e8ee;--hd:#f6f8fa;--hd2:#eaf4ee;--sel:rgba(29,111,66,.12);--text:#24292f} |
| *{box-sizing:border-box;margin:0;padding:0} |
| html{height:100%;background:#f1f5f3} |
| body{font-family:"Segoe UI","Yu Gothic UI",system-ui,sans-serif;font-size:13px;height:100vh;display:flex;flex-direction:column;overflow:hidden;background:#fff;color:var(--text)} |
| button,input,select,textarea{font:inherit} |
| button{color:inherit} |
| #titlebar{background:linear-gradient(180deg,#217a49 0%,var(--green) 100%);color:#fff;padding:6px 12px;display:flex;align-items:center;gap:10px;min-height:34px} |
| #titlebar .name{font-weight:600;font-size:14px} |
| #titlebar .file{font-size:12px;opacity:.85} |
| #qat{display:flex;gap:2px} |
| #qat button{background:none;border:1px solid transparent;cursor:pointer;padding:3px 6px;border-radius:4px} |
| #qat button:hover{background:rgba(255,255,255,.2)} |
| #qat svg{stroke:#fff} |
| #tabstrip{display:flex;background:var(--green);padding:0 8px;gap:2px} |
| #tabstrip .rtab{color:#fff;font-size:12.5px;padding:6px 16px;cursor:pointer;border-radius:4px 4px 0 0} |
| #tabstrip .rtab.on{background:#fff;color:var(--green);font-weight:600} |
| #tabstrip .rtab:hover:not(.on){background:rgba(255,255,255,.15)} |
| #ribbon{display:flex;padding:6px 8px 3px;border-bottom:1px solid var(--bd);background:#fff;min-height:80px;box-shadow:0 1px 2px rgba(27,31,36,.05)} |
| .rgrp{display:flex;flex-direction:column;padding:0 10px;border-right:1px solid #e3e3e3} |
| .rgrp .tools{display:flex;gap:2px;align-items:flex-start;flex:1} |
| .rgrp .cap{font-size:10.5px;color:#777;text-align:center;padding:2px 0 1px} |
| .rbtn{display:flex;flex-direction:column;align-items:center;gap:2px;border:1px solid transparent;background:none;padding:4px 7px;border-radius:3px;cursor:pointer;font-size:11px;color:#333;min-width:44px} |
| .rbtn:hover{border-color:#c8c8c8;background:#f3f9f5} |
| .rbtn.big svg{width:26px;height:26px} |
| .rbtn svg{width:18px;height:18px;stroke:#333;fill:none;stroke-width:1.5;stroke-linecap:round;stroke-linejoin:round} |
| .sbtn{border:1px solid transparent;background:none;padding:3px 6px;border-radius:3px;cursor:pointer;display:flex;align-items:center;gap:3px;font-size:12px} |
| .sbtn:hover{border-color:#c8c8c8;background:#f3f9f5} |
| .sbtn svg{width:16px;height:16px;stroke:#333;fill:none;stroke-width:1.5;stroke-linecap:round} |
| .scol{display:flex;flex-direction:column;gap:1px;justify-content:center} |
| .fbtn{font-family:Georgia,serif;font-size:14px;width:26px;height:24px;border:1px solid transparent;background:none;border-radius:3px;cursor:pointer} |
| .fbtn:hover{border-color:#c8c8c8;background:#f3f9f5} |
| .colorbtn{position:relative;width:28px;height:24px;border:1px solid transparent;border-radius:3px;background:none;cursor:pointer;font-family:Georgia,serif;font-size:13px;padding:0;display:inline-flex;align-items:center;justify-content:center;line-height:1} |
| .colorbtn:hover{border-color:#c8c8c8;background:#f3f9f5} |
| .colorbtn .bar{position:absolute;left:5px;right:5px;bottom:3px;height:4px;border:1px solid #999} |
| .colorbtn input{display:none} |
| #colorPalette{position:fixed;z-index:100;display:none;background:#fff;border:1px solid var(--bd);border-radius:6px;box-shadow:0 8px 24px rgba(27,31,36,.18);padding:8px;width:204px} |
| #colorPalette .ptitle{font-size:11px;color:#666;margin:0 0 6px} |
| #colorPalette .pgrid{display:grid;grid-template-columns:repeat(8,20px);gap:4px} |
| #colorPalette button.sw{width:20px;height:20px;border:1px solid #9ca3af;border-radius:3px;background:var(--c);cursor:pointer} |
| #colorPalette button.sw:hover{outline:2px solid var(--green);outline-offset:1px} |
| #colorPalette .pactions{display:flex;gap:6px;margin-top:8px} |
| #colorPalette .pactions button{font-size:11px;border:1px solid var(--bd);background:#fff;border-radius:4px;padding:3px 6px;cursor:pointer} |
| #fxbar{display:flex;align-items:center;gap:6px;padding:6px 10px;border-bottom:1px solid var(--bd);background:#fbfcfd} |
| #cellname{width:70px;border:1px solid var(--bd);border-radius:4px;padding:4px 6px;font-family:Consolas,monospace;font-size:12px;text-align:center;background:#fff} |
| #fx{flex:1;border:1px solid var(--bd);border-radius:4px;padding:4px 8px;font-family:Consolas,monospace;font-size:13px;background:#fff;min-height:27px} |
| #cellname:focus,#fx:focus,#sidepanel select:focus,#sidepanel textarea:focus{outline:none;border-color:var(--green);box-shadow:0 0 0 2px rgba(29,111,66,.15)} |
| #formulaHelp{position:absolute;left:198px;top:0;transform:translateY(100%);z-index:40;display:none;min-width:260px;max-width:520px;background:#fff;border:1px solid var(--bd);box-shadow:0 6px 18px rgba(27,31,36,.16);border-radius:6px;padding:6px 8px;font-size:12px;color:#4b5563} |
| #formulaHelp .fname{font-weight:700;color:#1f2328} |
| #formulaHelp .args{font-family:Consolas,monospace;color:#57606a;margin-top:2px} |
| #formulaHelp .suggest{display:inline-block;margin:3px 4px 0 0;padding:2px 6px;border-radius:4px;background:#eef6f0;color:var(--green);font-weight:600} |
| #main{flex:1;display:flex;overflow:hidden} |
| #gridwrap{flex:1;overflow:auto;position:relative;background:#fff} |
| #grid{border-collapse:separate;border-spacing:0;table-layout:fixed;user-select:none;-webkit-user-select:none;background:#fff} |
| th{background:linear-gradient(#f9fafb,var(--hd));border:1px solid var(--bd);border-width:0 1px 1px 0;font-weight:400;color:#4b5563;font-size:var(--head-font,12px);position:sticky;z-index:3;user-select:none} |
| thead th{top:0;height:var(--head-height,24px);position:sticky} |
| tbody th{left:0;width:42px;text-align:center;position:sticky;z-index:2} |
| thead th:first-child{left:0;z-index:5} |
| thead th[data-c],tbody th[data-r]{cursor:pointer} |
| thead th[data-c]:hover,tbody th[data-r]:hover{background:var(--hd2);color:var(--green)} |
| thead th.hsel,tbody th.hsel{background:#dcefe5;color:var(--green);font-weight:600} |
| td{border:1px solid var(--grid);border-width:0 1px 1px 0;padding:0 var(--cell-pad-x,8px);width:auto;max-width:none;height:auto;line-height:var(--cell-line,20px);overflow:hidden;white-space:nowrap;cursor:cell;background:#fff;color:#1f2328;font-size:var(--grid-font,13px)} |
| td.sel{background:transparent;border-color:#d8e3dc} |
| td.formula-ref{background:rgba(55,138,221,.055)} |
| td.formula-ref.ref2{background:rgba(239,159,39,.065)} |
| td.formula-ref.ref3{background:rgba(127,119,221,.06)} |
| td.formula-ref.ref4{background:rgba(216,90,48,.055)} |
| td.cur{box-shadow:inset 0 0 0 1px rgba(29,111,66,.22)} |
| #selbox{position:absolute;border:2px solid var(--green);pointer-events:none;z-index:4;display:none;box-sizing:border-box;box-shadow:0 0 0 1px rgba(29,111,66,.18)} |
| #refboxes{position:absolute;inset:0;pointer-events:none;z-index:3} |
| .refbox{position:absolute;box-sizing:border-box;border:2px solid #378ADD;background:rgba(55,138,221,.035)} |
| .refbox.ref2{border-color:#EF9F27;background:rgba(239,159,39,.04)} |
| .refbox.ref3{border-color:#7F77DD;background:rgba(127,119,221,.04)} |
| .refbox.ref4{border-color:#D85A30;background:rgba(216,90,48,.035)} |
| .refbox::after{content:"";position:absolute;width:6px;height:6px;right:-4px;bottom:-4px;background:currentColor;border:1px solid #fff} |
| #grid{user-select:none;-webkit-user-select:none} |
| td input{user-select:text;-webkit-user-select:text} |
| #fillh{position:absolute;width:7px;height:7px;background:var(--green);border:1px solid #fff;right:-5px;bottom:-5px;cursor:crosshair;pointer-events:auto} |
| #ctx{position:fixed;background:#fff;border:1px solid #bbb;box-shadow:2px 3px 8px rgba(0,0,0,.2);z-index:99;display:none;min-width:180px;padding:3px 0;font-size:13px} |
| #ctx div{padding:5px 14px;cursor:pointer} |
| #ctx div:hover{background:var(--sel)} |
| #ctx hr{border:none;border-top:1px solid #e0e0e0;margin:3px 0} |
| thead th{top:0} |
| .cres{position:absolute;right:-3px;top:0;width:6px;height:100%;cursor:col-resize;z-index:5} |
| .rres{position:absolute;left:0;bottom:-3px;height:6px;width:100%;cursor:row-resize;z-index:5} |
| tbody th{position:sticky;left:0} |
| td input{width:100%;height:100%;border:none;outline:none;font:inherit;background:#fff;padding:0 1px;line-height:var(--cell-line,20px)} |
| #sidepanel{width:0;border-left:1px solid var(--bd);overflow:auto;transition:width .15s;background:#fafafa} |
| #sidepanel.open{width:380px;padding:12px} |
| #sheetbar{display:flex;align-items:center;gap:2px;border-top:1px solid var(--bd);background:var(--hd);padding:2px 8px} |
| #sheetbar .tab{padding:4px 14px;font-size:12px;cursor:pointer;border:1px solid transparent;border-bottom:none;border-radius:4px 4px 0 0} |
| #sheetbar .tab.on{background:#fff;border-color:var(--bd);font-weight:600;color:var(--green)} |
| #status{margin-left:auto;font-size:12px;color:#555;display:flex;gap:16px} |
| #zoomctl{display:flex;align-items:center;gap:6px;color:#555} |
| #zoomctl button{width:22px;height:22px;border:1px solid var(--bd);border-radius:4px;background:#fff;cursor:pointer;line-height:18px} |
| #zoomctl button:hover{border-color:var(--green);color:var(--green)} |
| #zoomctl input{width:96px;accent-color:var(--green)} |
| #zoompct{min-width:42px;text-align:right;font-variant-numeric:tabular-nums} |
| .mini{font-size:11px;color:#777} |
| #sidepanel h3{font-size:13px;margin-bottom:8px;color:var(--green)} |
| #sidepanel textarea{width:100%;height:160px;font-family:Consolas,monospace;font-size:12px;border:1px solid var(--bd);border-radius:6px;padding:8px;background:#fff} |
| #sidepanel select,#sidepanel button{font-size:12px;padding:5px 9px;margin:2px 0;border:1px solid var(--bd);border-radius:5px;background:#fff;cursor:pointer} |
| #sidepanel button.go{background:var(--green);color:#fff;border-color:var(--green)} |
| .pvt td,.pvt th{position:static;font-size:12px;padding:3px 8px} |
| </style> |
| </head> |
| <body> |
| <div id="titlebar"> |
| <div id="qat"> |
| <button data-cmd="undo" title="元に戻す (Ctrl+Z)"><svg width="15" height="15" viewBox="0 0 24 24" fill="none" stroke-width="2"><path d="M9 14 4 9l5-5"/><path d="M4 9h10a6 6 0 0 1 0 12h-3"/></svg></button> |
| <button data-cmd="redo" title="やり直す (Ctrl+Y)"><svg width="15" height="15" viewBox="0 0 24 24" fill="none" stroke-width="2"><path d="m15 14 5-5-5-5"/><path d="M20 9H10a6 6 0 0 0 0 12h3"/></svg></button> |
| </div> |
| <span class="name">fableCalc</span><span class="file" id="fname">Book1</span> |
| <span style="flex:1"></span><span class="mini" style="color:#cde">v1.4 — Linux native (browser runtime)</span> |
| </div> |
| <div id="tabstrip"> |
| <span class="rtab" data-tab="file" style="background:rgba(255,255,255,.12)">ファイル</span> |
| <span class="rtab on" data-tab="home">ホーム</span> |
| <span class="rtab" data-tab="insert">挿入</span> |
| </div> |
| <div id="ribbon"> |
| <div id="rb-home" style="display:flex"> |
| <div class="rgrp"> |
| <div class="tools"> |
| <button class="rbtn big" data-cmd="paste" title="貼り付け (Ctrl+V)"><svg viewBox="0 0 24 24"><rect x="5" y="4" width="14" height="17" rx="1"/><rect x="9" y="2" width="6" height="4" rx="1" fill="#fff"/><path d="M9 11h6M9 14h6M9 17h4"/></svg>貼り付け</button> |
| <div class="scol"> |
| <button class="sbtn" data-cmd="cut" title="切り取り (Ctrl+X)"><svg viewBox="0 0 24 24"><circle cx="6" cy="18" r="2.5"/><circle cx="18" cy="18" r="2.5"/><path d="M7.8 16 18 4M16.2 16 6 4"/></svg>切り取り</button> |
| <button class="sbtn" data-cmd="copy" title="コピー (Ctrl+C)"><svg viewBox="0 0 24 24"><rect x="8" y="8" width="12" height="13" rx="1"/><path d="M5 16H4a1 1 0 0 1-1-1V4a1 1 0 0 1 1-1h11a1 1 0 0 1 1 1v1"/></svg>コピー</button> |
| </div> |
| </div> |
| <div class="cap">クリップボード</div> |
| </div> |
| <div class="rgrp"> |
| <div class="tools" style="align-items:center"> |
| <select id="fsize" title="フォントサイズ" style="height:24px;font-size:12px;border:1px solid #c8c8c8;border-radius:3px"><option>10</option><option selected>13</option><option>16</option><option>20</option><option>26</option></select> |
| <button class="fbtn" data-cmd="bold" title="太字 (Ctrl+B)" style="font-weight:700">B</button> |
| <button class="fbtn" data-cmd="italic" title="斜体 (Ctrl+I)" style="font-style:italic">I</button> |
| <button class="fbtn" data-cmd="border" title="罫線"><svg viewBox="0 0 24 24" width="16" height="16" style="stroke:#333;fill:none;stroke-width:1.5"><rect x="4" y="4" width="16" height="16"/><path d="M12 4v16M4 12h16"/></svg></button> |
| <span class="colorbtn" title="塗りつぶしの色"><svg viewBox="0 0 24 24" width="17" height="17" style="position:absolute;left:5px;top:0;stroke:#333;fill:none;stroke-width:1.5"><path d="m7 11 6-6 6 6-6 6a4.2 4.2 0 0 1-6-6z"/><path d="M13 5 11 3"/><path d="M20 16s1.5 2 1.5 3.2a1.5 1.5 0 0 1-3 0C18.5 18 20 16 20 16z" fill="#333"/></svg><span class="bar" id="bgbar" style="background:#ffff00"></span><input type="color" id="bgcol" value="#ffff00"></span> |
| <span class="colorbtn" title="フォントの色">A<span class="bar" id="fgbar" style="background:#c00000"></span><input type="color" id="fgcol" value="#c00000"></span> |
| </div> |
| <div class="cap">フォント</div> |
| </div> |
| <div class="rgrp"> |
| <div class="tools" style="align-items:center"> |
| <button class="fbtn" data-cmd="alleft" title="左揃え"><svg viewBox="0 0 24 24" width="15" height="15" style="stroke:#333;fill:none;stroke-width:1.8"><path d="M4 6h16M4 10h10M4 14h16M4 18h10"/></svg></button> |
| <button class="fbtn" data-cmd="alcenter" title="中央揃え"><svg viewBox="0 0 24 24" width="15" height="15" style="stroke:#333;fill:none;stroke-width:1.8"><path d="M4 6h16M7 10h10M4 14h16M7 18h10"/></svg></button> |
| <button class="fbtn" data-cmd="alright" title="右揃え"><svg viewBox="0 0 24 24" width="15" height="15" style="stroke:#333;fill:none;stroke-width:1.8"><path d="M4 6h16M10 10h10M4 14h16M10 18h10"/></svg></button> |
| </div> |
| <div class="cap">配置</div> |
| </div> |
| <div class="rgrp"> |
| <div class="tools" style="align-items:center"> |
| <button class="fbtn" data-cmd="comma" title="桁区切りスタイル" style="width:30px">,</button> |
| <button class="fbtn" data-cmd="percent" title="パーセント スタイル" style="width:30px;font-family:inherit">%</button> |
| <button class="fbtn" data-cmd="decdec" title="小数点以下の表示桁数を減らす" style="width:34px;font-size:11px;font-family:inherit">.0</button> |
| <button class="fbtn" data-cmd="decinc" title="小数点以下の表示桁数を増やす" style="width:34px;font-size:11px;font-family:inherit">.00</button> |
| <button class="fbtn" data-cmd="clearfmt" title="書式のクリア" style="width:34px;font-size:11px;font-family:inherit">消去</button> |
| </div> |
| <div class="cap">数値</div> |
| </div> |
| <div class="rgrp" style="border-right:none"> |
| <div class="tools"> |
| <button class="rbtn" data-cmd="autosum" title="オートSUM"><svg viewBox="0 0 24 24"><path d="M18 5H7l6 7-6 7h11"/></svg>合計</button> |
| <button class="rbtn" data-cmd="sortd" title="降順で並べ替え"><svg viewBox="0 0 24 24"><path d="M4 6h9M4 12h6M4 18h3"/><path d="M18 6v12m0 0 3-3m-3 3-3-3"/></svg>並べ替え</button> |
| </div> |
| <div class="cap">編集</div> |
| </div> |
| </div> |
| <div id="rb-insert" style="display:none"> |
| <div class="rgrp"> |
| <div class="tools"> |
| <button class="rbtn big" data-cmd="chart" title="グラフの挿入"><svg viewBox="0 0 24 24"><path d="M4 20V4"/><path d="M4 20h16"/><rect x="7" y="12" width="3" height="8"/><rect x="12" y="7" width="3" height="13"/><rect x="17" y="10" width="3" height="10"/></svg>グラフ</button> |
| <button class="rbtn big" data-cmd="pivot" title="ピボットテーブル"><svg viewBox="0 0 24 24"><rect x="4" y="4" width="16" height="16"/><path d="M4 9h16M9 4v16"/></svg>ピボット</button> |
| </div> |
| <div class="cap">テーブル・グラフ</div> |
| </div> |
| <div class="rgrp" style="border-right:none"> |
| <div class="tools"> |
| <button class="rbtn big" data-cmd="macro" title="マクロ"><svg viewBox="0 0 24 24"><circle cx="12" cy="12" r="3"/><path d="M12 2v3M12 19v3M2 12h3M19 12h3M4.9 4.9l2.1 2.1M17 17l2.1 2.1M19.1 4.9 17 7M7 17l-2.1 2.1"/></svg>マクロ</button> |
| </div> |
| <div class="cap">コード</div> |
| </div> |
| </div> |
| <div id="rb-file" style="display:none"> |
| <div class="rgrp" style="border-right:none"> |
| <div class="tools"> |
| <button class="rbtn big" data-cmd="open" title="開く"><svg viewBox="0 0 24 24"><path d="M3 7V5a1 1 0 0 1 1-1h5l2 2h9a1 1 0 0 1 1 1v2"/><path d="M3 7h18l-2 12H5L3 7z"/></svg>開く</button> |
| <button class="rbtn big" data-cmd="savex" title="xlsx形式で保存 (Ctrl+S)"><svg viewBox="0 0 24 24"><path d="M5 3h12l4 4v14H5V3z"/><rect x="8" y="3" width="7" height="5"/><rect x="8" y="13" width="8" height="8"/></svg>xlsx保存</button> |
| <button class="rbtn big" data-cmd="savec" title="CSV形式で保存"><svg viewBox="0 0 24 24"><path d="M6 2h9l5 5v15H6V2z"/><path d="M15 2v5h5"/><path d="M9 13h6M9 17h6"/></svg>CSV保存</button> |
| </div> |
| <div class="cap">ファイル</div> |
| </div> |
| </div> |
| <input type="file" id="filein" accept=".xlsx,.csv" style="display:none"> |
| </div> |
| <div id="fxbar" style="position:relative"> |
| <input id="cellname" value="A1" readonly><span class="mini">fx</span> |
| <input id="fx" placeholder="=VLOOKUP(A2,D:F,2,FALSE)" spellcheck="false"> |
| <div id="formulaHelp"></div> |
| </div> |
| <div id="main"> |
| <div id="gridwrap"><table id="grid"><colgroup id="cols"></colgroup><thead></thead><tbody></tbody></table><div id="refboxes"></div><div id="selbox"><div id="fillh"></div></div></div> |
| <div id="ctx"></div> |
| <div id="sidepanel"></div> |
| </div> |
| <div id="sheetbar"> |
| <div id="tabs"></div> |
| <button id="addsheet" style="border:none;background:none;cursor:pointer;font-size:14px">＋</button> |
| <div id="status"><span id="aggr"></span><span id="msg">準備完了</span><span id="zoomctl"><button id="zminus" title="縮小">−</button><input id="zoomrange" type="range" min="25" max="200" step="5" value="100" title="ズーム"><button id="zplus" title="拡大">＋</button><span id="zoompct">100%</span></span></div> |
| </div> |
| <div id="colorPalette"></div> |
| <script> |
| "use strict"; |
| const COLS=52, ROWS=512; |
| const colL=i=>{let s="",n=i+1;while(n>0){const r=(n-1)%26;s=String.fromCharCode(65+r)+s;n=Math.floor((n-1)/26);}return s;}; |
| const colN=s=>String(s).toUpperCase().split("").reduce((n,ch)=>n*26+ch.charCodeAt(0)-64,0)-1; |
| const A=(c,r)=>colL(c)+r; |
| const parseA=a=>{const m=a.match(/^\$?([A-Z]+)\$?(\d+)$/);return m?[colN(m[1]),+m[2]]:null;}; |
| |
| |
| let wb={sheets:{Sheet1:{cells:{},fmt:{}}},order:["Sheet1"],active:"Sheet1"}; |
| const sh=()=>wb.sheets[wb.active]; |
| let cur=[0,1], anchor=[0,1], selEnd=[0,1]; |
| let undoStack=[],redoStack=[]; |
| let zoom=1; |
| let formulaRefs=[]; |
| const snap=()=>JSON.stringify(sh()); |
| function pushUndo(){undoStack.push(snap());if(undoStack.length>100)undoStack.shift();redoStack=[];} |
| function undo(){if(!undoStack.length)return;redoStack.push(snap());wb.sheets[wb.active]=JSON.parse(undoStack.pop());renderAll();msg("元に戻しました");} |
| function redo(){if(!redoStack.length)return;undoStack.push(snap());wb.sheets[wb.active]=JSON.parse(redoStack.pop());renderAll();msg("やり直しました");} |
| |
| const msg=t=>document.getElementById("msg").textContent=t; |
| function selRect(){const[c1,r1]=anchor,[c2,r2]=selEnd;return[Math.min(c1,c2),Math.min(r1,r2),Math.max(c1,c2),Math.max(r1,r2)];} |
| function inSel(c,r){const[a,b,x,y]=selRect();return c>=a&&c<=x&&r>=b&&r<=y;} |
| function refClass(c,r){ |
| for(let i=0;i<formulaRefs.length;i++){ |
| const q=formulaRefs[i]; |
| if(c>=q.a&&c<=q.x&&r>=q.b&&r<=q.y)return " formula-ref ref"+((i%4)+1); |
| } |
| return ""; |
| } |
| function updateHeaders(){ |
| const[a,b,x,y]=selRect(); |
| thead.querySelectorAll("th[data-c]").forEach(th=>{ |
| const c=+th.dataset.c; |
| th.classList.toggle("hsel",c>=a&&c<=x); |
| }); |
| tbody.querySelectorAll("th[data-r]").forEach(th=>{ |
| const r=+th.dataset.r; |
| th.classList.toggle("hsel",r>=b&&r<=y); |
| }); |
| } |
| |
| |
| const tbody=document.querySelector("#grid tbody"), thead=document.querySelector("#grid thead"), colg=document.getElementById("cols"); |
| function colw(){sh().colw=sh().colw||{};return sh().colw;} |
| function rowh(){sh().rowh=sh().rowh||{};return sh().rowh;} |
| function z(v){return Math.max(1,Math.round(v*zoom));} |
| function buildGrid(){ |
| let cg='<col id="rowheadcol" style="width:'+z(42)+'px">'; |
| for(let c=0;c<COLS;c++)cg+='<col id="col'+c+'" style="width:'+z(colw()[c]||88)+'px">'; |
| colg.innerHTML=cg; |
| let h="<tr><th></th>"; |
| for(let c=0;c<COLS;c++)h+='<th data-c="'+c+'">'+colL(c)+'<span class="cres" data-c="'+c+'"></span></th>'; |
| thead.innerHTML=h+"</tr>"; |
| let b=""; |
| for(let r=1;r<=ROWS;r++){ |
| b+='<tr id="row'+r+'" style="height:'+z(rowh()[r]||22)+'px"><th data-r="'+r+'">'+r+'<span class="rres" data-r="'+r+'"></span></th>'; |
| for(let c=0;c<COLS;c++)b+='<td id="c'+c+'_'+r+'"></td>';b+="</tr>"; |
| } |
| tbody.innerHTML=b; |
| } |
| function applySizes(){ |
| const rowHead=document.getElementById("rowheadcol");if(rowHead)rowHead.style.width=z(42)+"px"; |
| document.documentElement.style.setProperty("--grid-font",Math.max(6,Math.round(13*zoom))+"px"); |
| document.documentElement.style.setProperty("--head-font",Math.max(6,Math.round(12*zoom))+"px"); |
| document.documentElement.style.setProperty("--cell-pad-x",Math.max(1,Math.round(8*zoom))+"px"); |
| document.documentElement.style.setProperty("--cell-line",Math.max(6,Math.round(20*zoom))+"px"); |
| document.documentElement.style.setProperty("--head-height",Math.max(8,Math.round(24*zoom))+"px"); |
| for(let c=0;c<COLS;c++){const el=document.getElementById("col"+c);if(el)el.style.width=z(colw()[c]||88)+"px";} |
| for(let r=1;r<=ROWS;r++){const el=document.getElementById("row"+r);if(el)el.style.height=z(rowh()[r]||22)+"px";} |
| } |
| |
| let resizing=null; |
| document.getElementById("grid").addEventListener("mousedown",e=>{ |
| const cr=e.target.closest(".cres"), rr=e.target.closest(".rres"); |
| if(cr){resizing={type:"c",i:+cr.dataset.c,start:e.clientX,base:colw()[+cr.dataset.c]||88};e.preventDefault();e.stopPropagation();} |
| else if(rr){resizing={type:"r",i:+rr.dataset.r,start:e.clientY,base:rowh()[+rr.dataset.r]||22};e.preventDefault();e.stopPropagation();} |
| },true); |
| document.addEventListener("mousemove",e=>{ |
| if(!resizing)return; |
| if(resizing.type==="c"){colw()[resizing.i]=Math.max(28,resizing.base+e.clientX-resizing.start);} |
| else{rowh()[resizing.i]=Math.max(14,resizing.base+e.clientY-resizing.start);} |
| applySizes();positionSelbox(); |
| }); |
| document.addEventListener("mouseup",()=>{if(resizing){resizing=null;msg("サイズを変更しました");}}); |
| function fmtNum(v,f){ |
| if(typeof v!=="number")return v; |
| const dec=f&&f.dec!==undefined?f.dec:undefined; |
| let n=f&&f.percent?v*100:v; |
| let s; |
| if(dec!==undefined){ |
| s=(f&&f.comma)?n.toLocaleString(undefined,{minimumFractionDigits:dec,maximumFractionDigits:dec}):n.toFixed(dec); |
| }else{ |
| s=(f&&f.comma)?n.toLocaleString(undefined,{maximumFractionDigits:2}):String(Math.round(n*10000)/10000); |
| } |
| if(f&&f.percent)s+="%"; |
| return s; |
| } |
| function paintCell(c,r){ |
| const td=document.getElementById("c"+c+"_"+r);if(!td)return; |
| const a=A(c,r), f=sh().fmt[a]||{}, raw=sh().cells[a]; |
| let v=""; |
| if(raw!==undefined&&raw!==""){CALC.depth=0;v=String(raw)[0]==="="?CALC.evalCell(a):coerce(raw);} |
| td.textContent=fmtNum(v,f); |
| td.style.fontWeight=f.b?"700":"";td.style.fontStyle=f.i?"italic":""; |
| td.style.color=f.fg||"";td.style.backgroundColor=f.bg||""; |
| td.style.border=f.bd?"1px solid #666":""; |
| td.style.fontSize=f.fs?Math.max(6,Math.round(f.fs*zoom))+"px":""; |
| td.style.textAlign=f.al||((typeof v==="number")?"right":"left"); |
| td.className=(inSel(c,r)?"sel":"")+(c===cur[0]&&r===cur[1]?" cur":"")+refClass(c,r); |
| } |
| function positionSelbox(){ |
| const box=document.getElementById("selbox"); |
| const[a,b,x,y]=selRect(); |
| const t1=document.getElementById("c"+a+"_"+b),t2=document.getElementById("c"+x+"_"+y); |
| if(!t1||!t2){box.style.display="none";return;} |
| box.style.display="block"; |
| box.style.left=(t1.offsetLeft-1)+"px"; |
| box.style.top=(t1.offsetTop-1)+"px"; |
| box.style.width=(t2.offsetLeft+t2.offsetWidth-t1.offsetLeft+1)+"px"; |
| box.style.height=(t2.offsetTop+t2.offsetHeight-t1.offsetTop+1)+"px"; |
| } |
| function positionRefBoxes(){ |
| const wrap=document.getElementById("refboxes"); |
| if(!wrap)return; |
| if(!formulaRefs.length){wrap.innerHTML="";return;} |
| wrap.innerHTML=formulaRefs.map((q,i)=>{ |
| const t1=document.getElementById("c"+q.a+"_"+q.b),t2=document.getElementById("c"+q.x+"_"+q.y); |
| if(!t1||!t2)return ""; |
| const left=t1.offsetLeft-1, top=t1.offsetTop-1; |
| const width=t2.offsetLeft+t2.offsetWidth-t1.offsetLeft+1; |
| const height=t2.offsetTop+t2.offsetHeight-t1.offsetTop+1; |
| return '<div class="refbox ref'+((i%4)+1)+'" style="left:'+left+'px;top:'+top+'px;width:'+width+'px;height:'+height+'px;color:'+["#378ADD","#EF9F27","#7F77DD","#D85A30"][i%4]+'"></div>'; |
| }).join(""); |
| } |
| let paintQueued=false; |
| function renderAll(){ |
| if(paintQueued)return;paintQueued=true; |
| requestAnimationFrame(()=>{ |
| paintQueued=false; |
| for(let r=1;r<=ROWS;r++)for(let c=0;c<COLS;c++)paintCell(c,r); |
| document.getElementById("cellname").value=A(...cur); |
| const fx=document.getElementById("fx"); |
| if(document.activeElement!==fx)fx.value=sh().cells[A(...cur)]||""; |
| applySizes();positionSelbox();positionRefBoxes();updateHeaders();renderTabs();aggregate(); |
| }); |
| } |
| document.getElementById("gridwrap").addEventListener("scroll",()=>{positionSelbox();positionRefBoxes();}); |
| function aggregate(){ |
| const[a,b,x,y]=selRect();let n=0,s=0; |
| for(let c=a;c<=x;c++)for(let r=b;r<=y;r++){CALC.depth=0;const v=CALC.val(A(c,r));if(typeof v==="number"&&v!==0||sh().cells[A(c,r)]!==undefined&&typeof v==="number"){n++;s+=v;}} |
| document.getElementById("aggr").textContent=n>1?("合計:"+(Math.round(s*100)/100).toLocaleString()+" 平均:"+(Math.round(s/n*100)/100).toLocaleString()+" 件数:"+n):""; |
| } |
| function coerce(raw){const n=parseFloat(raw);return(!isNaN(n)&&String(raw).trim()!==""&&/^-?[\d.,]+%?$/.test(String(raw).trim().replace(/,/g,"")))?parseFloat(String(raw).replace(/,/g,"")):raw;} |
| function finishPreview(repaint){ |
| if(repaint)renderAll();else positionRefBoxes(); |
| } |
| const FORMULA_SIG={ |
| SUM:"SUM(number1, [number2], ...)", AVERAGE:"AVERAGE(number1, [number2], ...)", COUNT:"COUNT(value1, [value2], ...)", |
| COUNTA:"COUNTA(value1, [value2], ...)", MIN:"MIN(number1, ...)", MAX:"MAX(number1, ...)", MEDIAN:"MEDIAN(number1, ...)", |
| IF:"IF(logical_test, value_if_true, value_if_false)", IFERROR:"IFERROR(value, value_if_error)", AND:"AND(logical1, ...)", OR:"OR(logical1, ...)", |
| ROUND:"ROUND(number, num_digits)", ROUNDUP:"ROUNDUP(number, num_digits)", ROUNDDOWN:"ROUNDDOWN(number, num_digits)", |
| COUNTIF:"COUNTIF(range, criteria)", SUMIF:"SUMIF(range, criteria, [sum_range])", AVERAGEIF:"AVERAGEIF(range, criteria, [average_range])", |
| SUMIFS:"SUMIFS(sum_range, criteria_range1, criteria1, ...)", COUNTIFS:"COUNTIFS(criteria_range1, criteria1, ...)", |
| VLOOKUP:"VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])", HLOOKUP:"HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])", |
| XLOOKUP:"XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])", INDEX:"INDEX(array, row_num, [column_num])", MATCH:"MATCH(lookup_value, lookup_array, [match_type])", |
| LEFT:"LEFT(text, [num_chars])", RIGHT:"RIGHT(text, [num_chars])", MID:"MID(text, start_num, num_chars)", LEN:"LEN(text)", TEXTJOIN:"TEXTJOIN(delimiter, ignore_empty, text1, ...)", |
| FIND:"FIND(find_text, within_text, [start_num])", SEARCH:"SEARCH(find_text, within_text, [start_num])", VALUE:"VALUE(text)", EXACT:"EXACT(text1, text2)", |
| PRODUCT:"PRODUCT(number1, [number2], ...)", RAND:"RAND()", RANDBETWEEN:"RANDBETWEEN(bottom, top)", FLOOR:"FLOOR(number, significance)", CEILING:"CEILING(number, significance)", |
| TODAY:"TODAY()", NOW:"NOW()", DATE:"DATE(year, month, day)", EDATE:"EDATE(start_date, months)", EOMONTH:"EOMONTH(start_date, months)", DAYS:"DAYS(end_date, start_date)", WEEKDAY:"WEEKDAY(serial_number)" |
| }; |
| function updateFormulaHelp(text){ |
| const box=document.getElementById("formulaHelp"); |
| if(!box)return; |
| text=String(text||""); |
| if(!text.startsWith("=")){box.style.display="none";return;} |
| const before=text.slice(1); |
| const active=[...before.matchAll(/([A-Z][A-Z0-9]*)\(/gi)].pop(); |
| const tail=before.match(/(?:^|[^A-Z0-9_])([A-Z][A-Z0-9]*)$/i); |
| let html=""; |
| if(active){ |
| const name=active[1].toUpperCase(); |
| html='<div><span class="fname">'+name+'</span></div><div class="args">'+(FORMULA_SIG[name]||"未対応または未知の関数")+'</div>'; |
| }else if(tail){ |
| const q=tail[1].toUpperCase(); |
| const names=Object.keys(FORMULA_SIG).filter(n=>n.startsWith(q)).slice(0,8); |
| if(names.length)html='<div>関数候補</div>'+names.map(n=>'<span class="suggest">'+n+'</span>').join(""); |
| } |
| box.innerHTML=html; |
| box.style.display=html?"block":"none"; |
| } |
| function previewFormula(text,repaint=true){ |
| formulaRefs=[]; |
| updateFormulaHelp(text); |
| if(!String(text).startsWith("=")){ |
| msg(text===""?"入力プレビュー: 空白":"入力プレビュー: "+String(coerce(text))); |
| finishPreview(repaint); |
| return; |
| } |
| const seen=new Set(); |
| const addRef=(a,b)=>{ |
| const p1=parseA(a.replace(/\$/g,"")),p2=parseA((b||a).replace(/\$/g,"")); |
| if(!p1||!p2)return; |
| const q={a:Math.min(p1[0],p2[0]),b:Math.min(p1[1],p2[1]),x:Math.max(p1[0],p2[0]),y:Math.max(p1[1],p2[1])}; |
| if(q.a>=COLS||q.x<0||q.b>ROWS||q.y<1)return; |
| q.a=Math.max(0,q.a);q.x=Math.min(COLS-1,q.x);q.b=Math.max(1,q.b);q.y=Math.min(ROWS,q.y); |
| const key=[q.a,q.b,q.x,q.y].join(":"); |
| if(!seen.has(key)){seen.add(key);formulaRefs.push(q);} |
| }; |
| const addPartialRef=token=>{ |
| token=String(token||"").replace(/\$/g,"").toUpperCase(); |
| if(!token)return; |
| const toCol=part=>{ |
| const m=String(part||"").match(/^([A-Z]+)(\d*)$/); |
| if(!m)return null; |
| const c=colN(m[1]); |
| if(c<0||c>=COLS)return null; |
| return {c,r:m[2]?+m[2]:null}; |
| }; |
| const parts=token.split(":"); |
| const p1=toCol(parts[0]),p2=toCol(parts[1]||parts[0]); |
| if(!p1)return; |
| if(parts.length>1&&!p2){ |
| formulaRefs.push({a:p1.c,b:p1.r||1,x:p1.c,y:p1.r||ROWS}); |
| return; |
| } |
| const q={ |
| a:Math.min(p1.c,p2.c), |
| b:Math.min(p1.r||1,p2.r||ROWS), |
| x:Math.max(p1.c,p2.c), |
| y:Math.max(p1.r||ROWS,p2.r||ROWS) |
| }; |
| const key=[q.a,q.b,q.x,q.y].join(":"); |
| if(!seen.has(key)){seen.add(key);formulaRefs.push(q);} |
| }; |
| const body=String(text).slice(1); |
| body.replace(/(\$?[A-Z]+\$?\d+):(\$?[A-Z]+\$?\d+)/g,(_,a,b)=>{addRef(a,b);return _;}); |
| body.replace(/(^|[^:A-Z0-9_$])(\$?[A-Z]+\$?\d+)(?!\s*:)/g,(_,pre,a)=>{addRef(a);return _;}); |
| const tail=body.match(/(?:^|[\(,+\-*/^<>=])\s*(\$?[A-Z]+(?:\$?\d*)?(?::\$?[A-Z]*(?:\$?\d*)?)?)$/i); |
| if(tail)addPartialRef(tail[1]); |
| CALC.depth=0; |
| const old=sh().cells[A(...cur)]; |
| sh().cells[A(...cur)]=text; |
| const v=CALC.evalCell(A(...cur)); |
| if(old===undefined)delete sh().cells[A(...cur)];else sh().cells[A(...cur)]=old; |
| const refs=formulaRefs.length?(" / 参照:"+formulaRefs.map(q=>A(q.a,q.b)+(q.a!==q.x||q.b!==q.y?":"+A(q.x,q.y):"")).join(", ")):""; |
| msg("プレビュー: "+String(v)+refs); |
| finishPreview(repaint); |
| } |
| function clearFormulaPreview(repaint=true){ |
| const had=formulaRefs.length; |
| formulaRefs=[]; |
| updateFormulaHelp(""); |
| if(had)finishPreview(repaint); |
| msg("準備完了"); |
| } |
| |
| |
| let dragging=false; |
| let headerDragging=null; |
| let formulaPick=null; |
| function activeFormulaInput(){ |
| const ae=document.activeElement; |
| if(ae&&ae.id==="fx"&&String(ae.value).startsWith("="))return ae; |
| const inp=editTd&&editTd.querySelector("input"); |
| if(inp&&document.activeElement===inp&&String(inp.value).startsWith("="))return inp; |
| return null; |
| } |
| function refTextFromRect(a,b,x,y){ |
| return A(a,b)+(a!==x||b!==y?":"+A(x,y):""); |
| } |
| function replaceFormulaToken(input,ref){ |
| const pos=input.selectionStart??input.value.length; |
| const before=input.value.slice(0,pos), after=input.value.slice(pos); |
| const m=before.match(/([\(,+\-*/^<>=]\s*)(\$?[A-Z]{0,3}\$?\d*(?::\$?[A-Z]{0,3}\$?\d*)?)$/i); |
| let next, caret; |
| if(m&&m[2]!==undefined){ |
| next=before.slice(0,before.length-m[2].length)+ref+after; |
| caret=before.length-m[2].length+ref.length; |
| }else{ |
| next=before+ref+after; |
| caret=before.length+ref.length; |
| } |
| input.value=next; |
| input.focus(); |
| input.setSelectionRange(caret,caret); |
| previewFormula(input.value,input.id==="fx"); |
| } |
| function startFormulaPick(td,e){ |
| const input=activeFormulaInput();if(!input)return false; |
| const[,c,r]=td.id.match(/c(\d+)_(\d+)/).map(Number); |
| formulaPick={input,start:[c,r],end:[c,r]}; |
| formulaRefs=[{a:c,b:r,x:c,y:r}]; |
| replaceFormulaToken(input,A(c,r)); |
| positionRefBoxes(); |
| e.preventDefault();e.stopPropagation(); |
| return true; |
| } |
| function updateFormulaPick(td){ |
| if(!formulaPick)return; |
| const[,c,r]=td.id.match(/c(\d+)_(\d+)/).map(Number); |
| formulaPick.end=[c,r]; |
| const a=Math.min(formulaPick.start[0],c),b=Math.min(formulaPick.start[1],r),x=Math.max(formulaPick.start[0],c),y=Math.max(formulaPick.start[1],r); |
| formulaRefs=[{a,b,x,y}]; |
| replaceFormulaToken(formulaPick.input,refTextFromRect(a,b,x,y)); |
| positionRefBoxes(); |
| } |
| thead.addEventListener("mousedown",e=>{ |
| const th=e.target.closest("th[data-c]");if(!th||e.target.closest(".cres"))return; |
| const c=+th.dataset.c; |
| if(e.shiftKey){selEnd=[c,ROWS];} |
| else{cur=[c,1];anchor=[c,1];selEnd=[c,ROWS];} |
| headerDragging="col";commitEdit();renderAll();e.preventDefault(); |
| }); |
| thead.addEventListener("mouseover",e=>{ |
| if(headerDragging!=="col")return; |
| const th=e.target.closest("th[data-c]");if(!th)return; |
| selEnd=[+th.dataset.c,ROWS];renderAll(); |
| }); |
| tbody.addEventListener("mousedown",e=>{ |
| const th=e.target.closest("th[data-r]");if(!th||e.target.closest(".rres"))return; |
| const r=+th.dataset.r; |
| if(e.shiftKey){selEnd=[COLS-1,r];} |
| else{cur=[0,r];anchor=[0,r];selEnd=[COLS-1,r];} |
| headerDragging="row";commitEdit();renderAll();e.preventDefault(); |
| }); |
| tbody.addEventListener("mouseover",e=>{ |
| if(headerDragging!=="row")return; |
| const th=e.target.closest("th[data-r]");if(!th)return; |
| selEnd=[COLS-1,+th.dataset.r];renderAll(); |
| }); |
| tbody.addEventListener("mousedown",e=>{ |
| const td=e.target.closest("td");if(!td)return; |
| if(startFormulaPick(td,e))return; |
| const[,c,r]=td.id.match(/c(\d+)_(\d+)/).map(Number); |
| if(e.shiftKey){selEnd=[c,r];}else{cur=[c,r];anchor=[c,r];selEnd=[c,r];} |
| dragging=true;commitEdit();renderAll(); |
| }); |
| tbody.addEventListener("mouseover",e=>{ |
| if(formulaPick){const td=e.target.closest("td");if(td)updateFormulaPick(td);return;} |
| if(!dragging)return;const td=e.target.closest("td");if(!td)return; |
| const[,c,r]=td.id.match(/c(\d+)_(\d+)/).map(Number);selEnd=[c,r];renderAll(); |
| }); |
| document.addEventListener("mousemove",e=>{ |
| if(!dragging&&!formulaPick&&!filling)return; |
| const td=document.elementFromPoint(e.clientX,e.clientY)?.closest?.("td"); |
| if(!td||!td.id||!td.id.startsWith("c"))return; |
| const[,c,r]=td.id.match(/c(\d+)_(\d+)/).map(Number); |
| if(formulaPick){updateFormulaPick(td);return;} |
| if(filling){ |
| filling.end=[c,r]; |
| const[a,b,x,y]=filling.rect; |
| anchor=[Math.min(a,c),Math.min(b,r)];selEnd=[Math.max(x,c),Math.max(y,r)];renderAll(); |
| return; |
| } |
| selEnd=[c,r];renderAll(); |
| }); |
| document.addEventListener("mouseup",()=>dragging=false); |
| document.addEventListener("mouseup",()=>headerDragging=null); |
| document.addEventListener("mouseup",()=>formulaPick=null); |
| tbody.addEventListener("dblclick",e=>{const td=e.target.closest("td");if(td)startEdit();}); |
| |
| |
| let filling=null; |
| document.getElementById("fillh").addEventListener("mousedown",e=>{ |
| e.preventDefault();e.stopPropagation(); |
| filling={rect:selRect(),end:null}; |
| }); |
| tbody.addEventListener("mouseover",e=>{ |
| if(!filling)return;const td=e.target.closest("td");if(!td)return; |
| const[,c,r]=td.id.match(/c(\d+)_(\d+)/).map(Number); |
| filling.end=[c,r]; |
| const[a,b,x,y]=filling.rect; |
| anchor=[Math.min(a,c),Math.min(b,r)];selEnd=[Math.max(x,c),Math.max(y,r)];renderAll(); |
| }); |
| document.addEventListener("mouseup",()=>{ |
| if(!filling)return; |
| const[a,b,x,y]=filling.rect,end=filling.end;filling=null; |
| if(!end)return; |
| const[ec,er]=end; |
| if(er>=b&&er<=y&&ec>=a&&ec<=x)return; |
| pushUndo(); |
| const down=er>y, up=er<b, right=ec>x, left=ec<a; |
| if(down){ |
| for(let c=a;c<=x;c++){ |
| const src=[];for(let r=b;r<=y;r++)src.push(sh().cells[A(c,r)]); |
| const nsrc=src.map(v=>coerce(v??"")); |
| const linear=nsrc.length>=2&&nsrc.every(v=>typeof v==="number"); |
| const step=linear?nsrc[1]-nsrc[0]:1; |
| const singleNum=nsrc.length===1&&typeof nsrc[0]==="number"; |
| for(let r=y+1;r<=er;r++){ |
| const i=(r-b)%src.length, off=r-(b+i); |
| let v=src[i]; |
| if(v===undefined)continue; |
| if(String(v)[0]==="=")v=shiftRefs(v,0,off); |
| else if(linear)v=String(nsrc[0]+step*(r-b)); |
| else if(singleNum)v=String(nsrc[0]+(r-y)); |
| sh().cells[A(c,r)]=v; |
| } |
| } |
| }else if(right){ |
| for(let r=b;r<=y;r++){ |
| for(let c=x+1;c<=ec;c++){ |
| const i=(c-a)%(x-a+1), off=c-(a+i); |
| let v=sh().cells[A(a+i,r)]; |
| if(v===undefined)continue; |
| if(String(v)[0]==="=")v=shiftRefs(v,off,0); |
| sh().cells[A(c,r)]=v; |
| } |
| } |
| }else if(up){ |
| for(let c=a;c<=x;c++){ |
| const src=[];for(let r=b;r<=y;r++)src.push(sh().cells[A(c,r)]); |
| for(let r=b-1;r>=er;r--){ |
| const i=((b-1-r)%src.length), off=r-(b+i); |
| let v=src[i];if(v===undefined)continue; |
| if(String(v)[0]==="=")v=shiftRefs(v,0,off); |
| sh().cells[A(c,r)]=v; |
| } |
| } |
| }else if(left){ |
| for(let r=b;r<=y;r++){ |
| const src=[];for(let c=a;c<=x;c++)src.push(sh().cells[A(c,r)]); |
| for(let c=a-1;c>=ec;c--){ |
| const i=((a-1-c)%src.length), off=c-(a+i); |
| let v=src[i];if(v===undefined)continue; |
| if(String(v)[0]==="=")v=shiftRefs(v,off,0); |
| sh().cells[A(c,r)]=v; |
| } |
| } |
| } |
| renderAll();msg("オートフィルしました"); |
| }); |
| |
| |
| const ctx=document.getElementById("ctx"); |
| function showCtx(x,y){ |
| ctx.innerHTML=` |
| <div data-m="cut">切り取り</div><div data-m="copy">コピー</div><div data-m="paste">貼り付け</div><hr> |
| <div data-m="insrow">行の挿入</div><div data-m="delrow">行の削除</div> |
| <div data-m="inscol">列の挿入</div><div data-m="delcol">列の削除</div><hr> |
| <div data-m="clear">数式と値のクリア</div><div data-m="clearf">書式のクリア</div>`; |
| ctx.style.display="block"; |
| ctx.style.left=Math.min(x,innerWidth-200)+"px";ctx.style.top=Math.min(y,innerHeight-260)+"px"; |
| } |
| document.addEventListener("click",()=>ctx.style.display="none"); |
| tbody.addEventListener("contextmenu",e=>{ |
| const td=e.target.closest("td");if(!td)return; |
| e.preventDefault(); |
| const[,c,r]=td.id.match(/c(\d+)_(\d+)/).map(Number); |
| if(!inSel(c,r)){cur=[c,r];anchor=[c,r];selEnd=[c,r];renderAll();} |
| showCtx(e.clientX,e.clientY); |
| }); |
| thead.addEventListener("contextmenu",e=>{ |
| const th=e.target.closest("th[data-c]");if(!th)return; |
| e.preventDefault(); |
| const c=+th.dataset.c; |
| cur=[c,1];anchor=[c,1];selEnd=[c,ROWS];renderAll(); |
| showCtx(e.clientX,e.clientY); |
| }); |
| tbody.addEventListener("contextmenu",e=>{ |
| const th=e.target.closest("th[data-r]");if(!th)return; |
| e.preventDefault(); |
| const r=+th.dataset.r; |
| cur=[0,r];anchor=[0,r];selEnd=[COLS-1,r];renderAll(); |
| showCtx(e.clientX,e.clientY); |
| }); |
| ctx.addEventListener("click",e=>{ |
| const m=e.target.dataset.m;if(!m)return; |
| if(m==="cut"){copySel();delSel();} |
| else if(m==="copy")copySel(); |
| else if(m==="paste")pasteClip(); |
| else if(m==="clear")delSel(); |
| else if(m==="clearf"){pushUndo();eachSel(a=>delete sh().fmt[a]);renderAll();} |
| else if(m==="insrow")insRow(cur[1]); |
| else if(m==="delrow")delRow(cur[1]); |
| else if(m==="inscol")insCol(cur[0]); |
| else if(m==="delcol")delCol(cur[0]); |
| }); |
| |
| |
| function remapSheet(mapAddr,mapRef){ |
| const s=sh(),nc={},nf={}; |
| Object.entries(s.cells).forEach(([a,v])=>{ |
| const na=mapAddr(a);if(na===null)return; |
| if(String(v)[0]==="=")v="="+String(v).slice(1).replace(/(\$?)([A-Z]+)(\$?)(\d+)/g,(mm,d1,col,d2,row)=>{ |
| const nr=mapRef(colN(col),+row);return nr===null?"#REF!":d1+colL(nr[0])+d2+nr[1]; |
| }); |
| nc[na]=v; |
| }); |
| Object.entries(s.fmt).forEach(([a,v])=>{const na=mapAddr(a);if(na!==null)nf[na]=v;}); |
| s.cells=nc;s.fmt=nf; |
| } |
| function insRow(at){pushUndo(); |
| remapSheet(a=>{const p=parseA(a);return p[1]>=at?A(p[0],p[1]+1):a;},(c,r)=>r>=at?[c,r+1]:[c,r]); |
| const rh=rowh();for(let r=ROWS;r>at;r--){if(rh[r-1])rh[r]=rh[r-1];else delete rh[r];}delete rh[at]; |
| renderAll();msg(at+"行目に挿入しました");} |
| function delRow(at){pushUndo(); |
| remapSheet(a=>{const p=parseA(a);return p[1]===at?null:p[1]>at?A(p[0],p[1]-1):a;},(c,r)=>r===at?null:r>at?[c,r-1]:[c,r]); |
| const rh=rowh();for(let r=at;r<ROWS;r++){if(rh[r+1])rh[r]=rh[r+1];else delete rh[r];} |
| renderAll();msg(at+"行目を削除しました");} |
| function insCol(at){pushUndo(); |
| remapSheet(a=>{const p=parseA(a);return p[0]>=at?A(p[0]+1,p[1]):a;},(c,r)=>c>=at?[c+1,r]:[c,r]); |
| const cw=colw();for(let c=COLS-1;c>at;c--){if(cw[c-1]!==undefined)cw[c]=cw[c-1];else delete cw[c];}delete cw[at]; |
| renderAll();msg(colL(at)+"列に挿入しました");} |
| function delCol(at){pushUndo(); |
| remapSheet(a=>{const p=parseA(a);return p[0]===at?null:p[0]>at?A(p[0]-1,p[1]):a;},(c,r)=>c===at?null:c>at?[c-1,r]:[c,r]); |
| const cw=colw();for(let c=at;c<COLS-1;c++){if(cw[c+1]!==undefined)cw[c]=cw[c+1];else delete cw[c];} |
| renderAll();msg(colL(at)+"列を削除しました");} |
| |
| |
| let editTd=null, editAddr=null; |
| function startEdit(initial){ |
| const[c,r]=cur, td=document.getElementById("c"+c+"_"+r); |
| const inp=document.createElement("input"); |
| inp.value=initial!==undefined?initial:(sh().cells[A(c,r)]||""); |
| td.textContent="";td.appendChild(inp);inp.focus(); |
| if(initial!==undefined)inp.setSelectionRange(inp.value.length,inp.value.length); |
| editTd=td;editAddr=A(c,r); |
| inp.addEventListener("keydown",e=>{ |
| if(e.key==="Enter"){e.preventDefault();commitEdit();move(0,1);} |
| else if(e.key==="Tab"){e.preventDefault();commitEdit();move(1,0);} |
| else if(e.key==="Escape"){editTd=null;editAddr=null;clearFormulaPreview(false);renderAll();} |
| e.stopPropagation(); |
| }); |
| inp.addEventListener("input",e=>previewFormula(e.target.value,false)); |
| previewFormula(inp.value,false); |
| } |
| function commitEdit(){ |
| if(!editTd)return;const inp=editTd.querySelector("input");if(!inp)return; |
| pushUndo();sh().cells[editAddr]=inp.value;if(inp.value==="")delete sh().cells[editAddr]; |
| editTd=null;editAddr=null;formulaRefs=[];renderAll(); |
| } |
| function move(dc,dr){ |
| cur=[Math.max(0,Math.min(COLS-1,cur[0]+dc)),Math.max(1,Math.min(ROWS,cur[1]+dr))]; |
| anchor=[...cur];selEnd=[...cur];renderAll();scrollToCur(); |
| } |
| function scrollToCur(){const td=document.getElementById("c"+cur[0]+"_"+cur[1]);if(td)td.scrollIntoView({block:"nearest",inline:"nearest"});} |
| |
| |
| document.addEventListener("keydown",e=>{ |
| const k=e.key; |
| const key=k.toLowerCase(); |
| if((e.ctrlKey||e.metaKey)&&!e.altKey){ |
| if(key==="z"){ |
| e.preventDefault(); |
| if(editTd)commitEdit(); |
| e.shiftKey?redo():undo(); |
| return; |
| } |
| if(key==="y"){ |
| e.preventDefault(); |
| if(editTd)commitEdit(); |
| redo(); |
| return; |
| } |
| } |
| if(editTd||["INPUT","TEXTAREA","SELECT"].includes(document.activeElement.tagName)){ |
| if(document.activeElement.id==="fx"&&e.key==="Enter"){ |
| pushUndo();const v=document.activeElement.value; |
| sh().cells[A(...cur)]=v;if(v==="")delete sh().cells[A(...cur)]; |
| move(0,1);document.getElementById("gridwrap").focus(); |
| } |
| return; |
| } |
| if(e.ctrlKey||e.metaKey){ |
| if(key==="c"){e.preventDefault();copySel();} |
| else if(key==="v"){e.preventDefault();pasteClip();} |
| else if(key==="x"){e.preventDefault();copySel();delSel();} |
| else if(key==="a"){e.preventDefault();selectUsedRange();} |
| else if(key==="s"){e.preventDefault();saveXlsx();} |
| else if(key==="b"){e.preventDefault();toggleFmt("b");} |
| else if(key==="i"){e.preventDefault();toggleFmt("i");} |
| else if(k==="Home"){e.preventDefault();cur=[0,1];anchor=[...cur];selEnd=[...cur];renderAll();scrollToCur();} |
| else if(k==="ArrowDown"){e.preventDefault();jump(0,1);} |
| else if(k==="ArrowUp"){e.preventDefault();jump(0,-1);} |
| else if(k==="ArrowRight"){e.preventDefault();jump(1,0);} |
| else if(k==="ArrowLeft"){e.preventDefault();jump(-1,0);} |
| return; |
| } |
| if(k==="Home"){e.preventDefault();cur=[0,cur[1]];anchor=[...cur];selEnd=[...cur];renderAll();scrollToCur();return;} |
| if(k.startsWith("Arrow")){ |
| e.preventDefault(); |
| const d={ArrowDown:[0,1],ArrowUp:[0,-1],ArrowRight:[1,0],ArrowLeft:[-1,0]}[k]; |
| if(e.shiftKey){selEnd=[Math.max(0,Math.min(COLS-1,selEnd[0]+d[0])),Math.max(1,Math.min(ROWS,selEnd[1]+d[1]))];renderAll();} |
| else move(...d); |
| } |
| else if(k==="Enter"){e.preventDefault();move(0,1);} |
| else if(k==="Tab"){e.preventDefault();move(1,0);} |
| else if(k==="Delete"||k==="Backspace"){e.preventDefault();delSel();} |
| else if(k==="F2"){e.preventDefault();startEdit();} |
| else if(k.length===1){startEdit(k);e.preventDefault?e.preventDefault():0;} |
| }); |
| function jump(dc,dr){ |
| let[c,r]=cur; |
| const has=(c,r)=>sh().cells[A(c,r)]!==undefined; |
| const step=()=>{c+=dc;r+=dr;}; |
| if(has(c,r)&&has(c+dc,r+dr)){while(c+dc>=0&&c+dc<COLS&&r+dr>=1&&r+dr<=ROWS&&has(c+dc,r+dr))step();} |
| else{while(c+dc>=0&&c+dc<COLS&&r+dr>=1&&r+dr<=ROWS&&!has(c+dc,r+dr))step();if(has(c,r)===false&&c+dc>=0&&c+dc<COLS&&r+dr>=1&&r+dr<=ROWS)step();} |
| cur=[Math.max(0,Math.min(COLS-1,c)),Math.max(1,Math.min(ROWS,r))];anchor=[...cur];selEnd=[...cur];renderAll();scrollToCur(); |
| } |
| function delSel(){pushUndo();const[a,b,x,y]=selRect();for(let c=a;c<=x;c++)for(let r=b;r<=y;r++)delete sh().cells[A(c,r)];renderAll();} |
| function selectUsedRange(){ |
| let maxC=0,maxR=1,found=false; |
| Object.keys(sh().cells).forEach(a=>{const p=parseA(a);if(p){found=true;maxC=Math.max(maxC,p[0]);maxR=Math.max(maxR,p[1]);}}); |
| anchor=[0,1];selEnd=found?[maxC,maxR]:[COLS-1,ROWS];cur=[0,1];renderAll(); |
| msg("全選択 A1:"+A(selEnd[0],selEnd[1])); |
| } |
| |
| |
| let clip=null; |
| function parseClipboardText(text){ |
| text=String(text||"").replace(/\r\n/g,"\n").replace(/\r/g,"\n"); |
| const rows=[], row=[];let cur="",q=false; |
| for(let i=0;i<text.length;i++){ |
| const ch=text[i], next=text[i+1]; |
| if(q){ |
| if(ch==='"'&&next==='"'){cur+='"';i++;} |
| else if(ch==='"')q=false; |
| else cur+=ch; |
| }else if(ch==='"')q=true; |
| else if(ch==="\t"||ch===","){row.push(cur);cur="";} |
| else if(ch==="\n"){row.push(cur);rows.push(row.splice(0));cur="";} |
| else cur+=ch; |
| } |
| if(cur!==""||row.length){row.push(cur);rows.push(row);} |
| return rows; |
| } |
| function pasteMatrix(rows){ |
| if(!rows||!rows.length)return false; |
| pushUndo(); |
| const[c0,r0]=cur; |
| rows.forEach((row,dr)=>row.forEach((v,dc)=>{ |
| const c=c0+dc,r=r0+dr;if(c>=COLS||r>ROWS)return; |
| if(v==="")delete sh().cells[A(c,r)];else sh().cells[A(c,r)]=String(v); |
| })); |
| anchor=[c0,r0];selEnd=[Math.min(COLS-1,c0+Math.max(...rows.map(r=>r.length),1)-1),Math.min(ROWS,r0+rows.length-1)]; |
| renderAll();msg("貼り付けました ("+rows.length+"×"+Math.max(...rows.map(r=>r.length))+")"); |
| return true; |
| } |
| function copySel(){ |
| const[a,b,x,y]=selRect();clip={w:x-a+1,h:y-b+1,data:[]};let txt=[]; |
| for(let r=b;r<=y;r++){let row=[],trow=[]; |
| for(let c=a;c<=x;c++){row.push(sh().cells[A(c,r)]??"");CALC.depth=0;trow.push(String(CALC.val(A(c,r))??""));} |
| clip.data.push(row);txt.push(trow.join("\t"));} |
| navigator.clipboard&&navigator.clipboard.writeText(txt.join("\n")).catch(()=>{}); |
| msg("コピーしました ("+clip.w+"×"+clip.h+")"); |
| } |
| function pasteClip(){ |
| if(!clip){ |
| if(navigator.clipboard&&navigator.clipboard.readText){ |
| navigator.clipboard.readText().then(t=>pasteMatrix(parseClipboardText(t))).catch(()=>msg("貼り付ける内容がありません")); |
| } |
| return; |
| } |
| pushUndo(); |
| const[c0,r0]=cur; |
| for(let dr=0;dr<clip.h;dr++)for(let dc=0;dc<clip.w;dc++){ |
| const c=c0+dc,r=r0+dr;if(c>=COLS||r>ROWS)continue; |
| let v=clip.data[dr][dc]; |
| if(String(v)[0]==="=")v=shiftRefs(v,dc,dr); |
| if(v==="")delete sh().cells[A(c,r)];else sh().cells[A(c,r)]=v; |
| } |
| renderAll();msg("貼り付けました"); |
| } |
| function shiftRefs(f,dc,dr){ |
| return f.replace(/(\$?)([A-Z]+)(\$?)(\d+)/g,(m,d1,col,d2,row)=>{ |
| let c=colN(col),r=+row; |
| if(!d1)c+=dc;if(!d2)r+=dr; |
| if(c<0||c>=COLS||r<1||r>ROWS)return"#REF!"; |
| return d1+colL(c)+d2+r; |
| }); |
| } |
| |
| |
| function eachSel(fn){const[a,b,x,y]=selRect();for(let c=a;c<=x;c++)for(let r=b;r<=y;r++)fn(A(c,r));} |
| function toggleFmt(key){pushUndo();const first=sh().fmt[A(...cur)]||{};const on=!first[key];eachSel(a=>{sh().fmt[a]=sh().fmt[a]||{};sh().fmt[a][key]=on;});renderAll();} |
| function setFmt(key,val){pushUndo();eachSel(a=>{sh().fmt[a]=sh().fmt[a]||{};sh().fmt[a][key]=val;});renderAll();} |
| function adjustDecimals(delta){ |
| pushUndo(); |
| const first=sh().fmt[A(...cur)]||{}; |
| const base=first.dec!==undefined?first.dec:(first.percent?0:2); |
| const next=Math.max(0,Math.min(8,base+delta)); |
| eachSel(a=>{sh().fmt[a]=sh().fmt[a]||{};sh().fmt[a].dec=next;}); |
| renderAll();msg("小数点以下 "+next+" 桁"); |
| } |
| |
| |
| function renderTabs(){ |
| const t=document.getElementById("tabs");t.innerHTML=""; |
| wb.order.forEach(n=>{ |
| const d=document.createElement("span");d.className="tab"+(n===wb.active?" on":"");d.textContent=n; |
| d.onclick=()=>{wb.active=n;renderAll();}; |
| d.ondblclick=()=>{const nn=prompt("シート名",n);if(nn&&!wb.sheets[nn]){wb.sheets[nn]=wb.sheets[n];delete wb.sheets[n];wb.order[wb.order.indexOf(n)]=nn;if(wb.active===n)wb.active=nn;renderAll();}}; |
| t.appendChild(d); |
| }); |
| } |
| document.getElementById("addsheet").onclick=()=>{ |
| let i=wb.order.length+1;while(wb.sheets["Sheet"+i])i++; |
| wb.sheets["Sheet"+i]={cells:{},fmt:{}};wb.order.push("Sheet"+i);wb.active="Sheet"+i;renderAll(); |
| }; |
| </script> |
| <script> |
| "use strict"; |
| |
| const CALC={depth:0}; |
| CALC.val=function(addr){ |
| const raw=sh().cells[addr]; |
| if(raw===undefined||raw==="")return 0; |
| if(String(raw)[0]==="="){ |
| if(CALC.depth>64)return"#CIRC!"; |
| CALC.depth++;const r=CALC.evalStr(String(raw).slice(1));CALC.depth--;return r; |
| } |
| return coerce(raw); |
| }; |
| CALC.evalCell=a=>CALC.evalStr(String(sh().cells[a]).slice(1)); |
| CALC.range=function(a,b){ |
| const p1=parseA(a),p2=parseA(b);if(!p1||!p2)return[]; |
| const out=[]; |
| for(let c=Math.min(p1[0],p2[0]);c<=Math.max(p1[0],p2[0]);c++) |
| for(let r=Math.min(p1[1],p2[1]);r<=Math.max(p1[1],p2[1]);r++) |
| out.push(CALC.val(A(c,r))); |
| return out; |
| }; |
| CALC.rangeCells=function(a,b){ |
| const p1=parseA(a),p2=parseA(b);if(!p1||!p2)return[]; |
| const rows=[]; |
| for(let r=Math.min(p1[1],p2[1]);r<=Math.max(p1[1],p2[1]);r++){ |
| const row=[];for(let c=Math.min(p1[0],p2[0]);c<=Math.max(p1[0],p2[0]);c++)row.push(CALC.val(A(c,r))); |
| rows.push(row); |
| } |
| return rows; |
| }; |
| const nums=v=>v.filter(x=>typeof x==="number"); |
| function crit(v,c){ |
| c=String(c); |
| const m=c.match(/^(<=|>=|<>|<|>|=)?(.*)$/); |
| const op=m[1]||"=",t=m[2];const tn=parseFloat(t); |
| const cmpV=isNaN(tn)?String(v):Number(v), cmpT=isNaN(tn)?t:tn; |
| switch(op){case"=":return cmpV==cmpT;case"<>":return cmpV!=cmpT;case"<":return cmpV<cmpT;case">":return cmpV>cmpT;case"<=":return cmpV<=cmpT;case">=":return cmpV>=cmpT;} |
| } |
| const FN={ |
| SUM:a=>nums(a.flat(Infinity)).reduce((x,y)=>x+y,0), |
| AVERAGE:a=>{const n=nums(a.flat(Infinity));return n.length?FN.SUM([n])/n.length:0;}, |
| COUNT:a=>nums(a.flat(Infinity)).length, |
| COUNTA:a=>a.flat(Infinity).filter(x=>x!==""&&x!==0||typeof x==="number").length, |
| MIN:a=>{const n=nums(a.flat(Infinity));return n.length?Math.min(...n):0;}, |
| MAX:a=>{const n=nums(a.flat(Infinity));return n.length?Math.max(...n):0;}, |
| MEDIAN:a=>{const n=nums(a.flat(Infinity)).sort((x,y)=>x-y);return n.length?(n.length%2?n[(n.length-1)/2]:(n[n.length/2-1]+n[n.length/2])/2):0;}, |
| STDEV:a=>{const n=nums(a.flat(Infinity));if(n.length<2)return 0;const m=FN.SUM([n])/n.length;return Math.sqrt(n.reduce((s,x)=>s+(x-m)**2,0)/(n.length-1));}, |
| IF:(a)=>a[0]?a[1]:a[2], |
| IFERROR:(a)=>String(a[0]).startsWith("#")?a[1]:a[0], |
| AND:a=>a.flat(Infinity).every(Boolean), |
| OR:a=>a.flat(Infinity).some(Boolean), |
| NOT:a=>!a[0], |
| ROUND:a=>{const p=10**(a[1]||0);return Math.round(a[0]*p)/p;}, |
| ROUNDUP:a=>{const p=10**(a[1]||0);return Math.ceil(a[0]*p)/p;}, |
| ROUNDDOWN:a=>{const p=10**(a[1]||0);return Math.floor(a[0]*p)/p;}, |
| ABS:a=>Math.abs(a[0]), INT:a=>Math.floor(a[0]), MOD:a=>a[0]%a[1], POWER:a=>a[0]**a[1], SQRT:a=>Math.sqrt(a[0]), |
| PRODUCT:a=>nums(a.flat(Infinity)).reduce((x,y)=>x*y,1), |
| RAND:()=>Math.random(), RANDBETWEEN:a=>Math.floor(Math.random()*(a[1]-a[0]+1))+a[0], |
| FLOOR:a=>Math.floor(a[0]/(a[1]||1))*(a[1]||1), CEILING:a=>Math.ceil(a[0]/(a[1]||1))*(a[1]||1), |
| CONCATENATE:a=>a.flat(Infinity).join(""), CONCAT:a=>a.flat(Infinity).join(""), |
| LEFT:a=>String(a[0]).slice(0,a[1]??1), RIGHT:a=>String(a[0]).slice(-(a[1]??1)), |
| MID:a=>String(a[0]).substr(a[1]-1,a[2]), LEN:a=>String(a[0]).length, |
| UPPER:a=>String(a[0]).toUpperCase(), LOWER:a=>String(a[0]).toLowerCase(), TRIM:a=>String(a[0]).trim(), |
| SUBSTITUTE:a=>String(a[0]).split(String(a[1])).join(String(a[2])), |
| FIND:a=>{const i=String(a[1]).indexOf(String(a[0]),(a[2]||1)-1);return i<0?"#VALUE!":i+1;}, |
| SEARCH:a=>{const i=String(a[1]).toLowerCase().indexOf(String(a[0]).toLowerCase(),(a[2]||1)-1);return i<0?"#VALUE!":i+1;}, |
| VALUE:a=>{const n=parseFloat(String(a[0]).replace(/,/g,""));return isNaN(n)?"#VALUE!":n;}, |
| EXACT:a=>String(a[0])===String(a[1]), |
| TEXT:a=>typeof a[0]==="number"?a[0].toLocaleString():String(a[0]), |
| TODAY:()=>new Date().toLocaleDateString(), NOW:()=>new Date().toLocaleString(), |
| DATE:a=>new Date(a[0],a[1]-1,a[2]).toLocaleDateString(), |
| EDATE:a=>{const d=new Date(a[0]);d.setMonth(d.getMonth()+Number(a[1]||0));return d.toLocaleDateString();}, |
| EOMONTH:a=>{const d=new Date(a[0]);return new Date(d.getFullYear(),d.getMonth()+Number(a[1]||0)+1,0).toLocaleDateString();}, |
| DAYS:a=>Math.round((new Date(a[0])-new Date(a[1]))/86400000), |
| WEEKDAY:a=>new Date(a[0]).getDay()+1, |
| YEAR:a=>new Date(a[0]).getFullYear(), MONTH:a=>new Date(a[0]).getMonth()+1, DAY:a=>new Date(a[0]).getDate(), |
| COUNTIF:a=>a[0].flat(Infinity).filter(v=>crit(v,a[1])).length, |
| SUMIF:a=>{ |
| const rng=a[0].flat(Infinity), c=a[1], srng=a[2]?a[2].flat(Infinity):rng; |
| let s=0;rng.forEach((v,i)=>{if(crit(v,c)&&typeof srng[i]==="number")s+=srng[i];});return s; |
| }, |
| AVERAGEIF:a=>{ |
| const rng=a[0].flat(Infinity), c=a[1], srng=a[2]?a[2].flat(Infinity):rng; |
| let s=0,n=0;rng.forEach((v,i)=>{if(crit(v,c)&&typeof srng[i]==="number"){s+=srng[i];n++;}});return n?s/n:0; |
| }, |
| VLOOKUP:a=>{ |
| const[key,tbl,col,exact]=a; |
| for(const row of tbl){if(exact===false||exact===0?row[0]==key:row[0]==key)return row[col-1]??"#N/A";} |
| if(exact!==false&&exact!==0){let best="#N/A";for(const row of tbl){if(row[0]<=key)best=row[col-1];}return best;} |
| return"#N/A"; |
| }, |
| HLOOKUP:a=>{ |
| const[key,tbl,rowi]=a; |
| for(let c=0;c<tbl[0].length;c++)if(tbl[0][c]==key)return tbl[rowi-1]?.[c]??"#N/A"; |
| return"#N/A"; |
| }, |
| INDEX:a=>{const[tbl,r,c]=a;return Array.isArray(tbl[0])?(tbl[r-1]?.[(c||1)-1]??"#REF!"):(tbl[r-1]??"#REF!");}, |
| MATCH:a=>{const[key,arr]=a;const f=arr.flat(Infinity);const i=f.findIndex(v=>v==key);return i<0?"#N/A":i+1;}, |
| XLOOKUP:a=>{ |
| const[key,lk,rt,ifnf]=a;const l=lk.flat(Infinity),r=rt.flat(Infinity); |
| const i=l.findIndex(v=>v==key);return i<0?(ifnf??"#N/A"):r[i]; |
| }, |
| SUMIFS:a=>{ |
| const sr=a[0].flat(Infinity);let s=0; |
| for(let i=0;i<sr.length;i++){ |
| let ok=true; |
| for(let j=1;j<a.length;j+=2){if(!crit(a[j].flat(Infinity)[i],a[j+1])){ok=false;break;}} |
| if(ok&&typeof sr[i]==="number")s+=sr[i]; |
| } |
| return s; |
| }, |
| COUNTIFS:a=>{ |
| const n=a[0].flat(Infinity).length;let cnt=0; |
| for(let i=0;i<n;i++){ |
| let ok=true; |
| for(let j=0;j<a.length;j+=2){if(!crit(a[j].flat(Infinity)[i],a[j+1])){ok=false;break;}} |
| if(ok)cnt++; |
| } |
| return cnt; |
| }, |
| RANK:a=>{const v=a[0],arr=nums(a[1].flat(Infinity)).sort((x,y)=>a[2]?x-y:y-x);const i=arr.indexOf(v);return i<0?"#N/A":i+1;}, |
| LARGE:a=>{const n=nums(a[0].flat(Infinity)).sort((x,y)=>y-x);return n[a[1]-1]??"#NUM!";}, |
| SMALL:a=>{const n=nums(a[0].flat(Infinity)).sort((x,y)=>x-y);return n[a[1]-1]??"#NUM!";}, |
| PROPER:a=>String(a[0]).replace(/\w+/g,w=>w[0].toUpperCase()+w.slice(1).toLowerCase()), |
| REPT:a=>String(a[0]).repeat(Math.max(0,a[1])), |
| TEXTJOIN:a=>{const[d,ig,...rest]=a;return rest.flat(Infinity).filter(v=>!ig||v!==""&&v!==0||typeof v==="number"&&v!==0||String(v)!=="").join(String(d));} |
| }; |
| CALC.evalStr=function(f){ |
| try{ |
| let i=0;const s=f; |
| function ws(){while(i<s.length&&s[i]===" ")i++;} |
| function parseExpr(){return parseCmp();} |
| function parseCmp(){ |
| let l=parseAdd();ws(); |
| while(i<s.length){ |
| let op=null; |
| if(s.startsWith("<=",i)||s.startsWith(">=",i)||s.startsWith("<>",i)){op=s.substr(i,2);i+=2;} |
| else if("<>=".includes(s[i])){op=s[i];i++;} |
| else break; |
| const r=parseAdd(); |
| l=op==="="?l==r:op==="<>"?l!=r:op==="<"?l<r:op===">"?l>r:op==="<="?l<=r:l>=r; |
| ws(); |
| } |
| return l; |
| } |
| function parseAdd(){ |
| let l=parseMul();ws(); |
| while(i<s.length&&(s[i]==="+"||s[i]==="-")){const op=s[i++];const r=parseMul();l=op==="+"?num(l)+num(r):num(l)-num(r);ws();} |
| return l; |
| } |
| function parseMul(){ |
| let l=parsePow();ws(); |
| while(i<s.length&&(s[i]==="*"||s[i]==="/")){const op=s[i++];const r=parsePow();l=op==="*"?num(l)*num(r):num(l)/num(r);ws();} |
| return l; |
| } |
| function parsePow(){ |
| let l=parseUnary();ws(); |
| while(i<s.length&&s[i]==="^"){i++;const r=parseUnary();l=num(l)**num(r);ws();} |
| return l; |
| } |
| function parseUnary(){ws();if(s[i]==="-"){i++;return -num(parseUnary());}if(s[i]==="+"){i++;return parseUnary();}return parseAtom();} |
| function num(v){if(typeof v==="boolean")return v?1:0;if(typeof v==="string"){const n=parseFloat(v);return isNaN(n)?0:n;}return v;} |
| function parseAtom(){ |
| ws(); |
| if(s[i]==="("){i++;const v=parseExpr();ws();if(s[i]===")")i++;return v;} |
| if(s[i]==='"'){let j=i+1,out="";while(j<s.length&&s[j]!=='"'){out+=s[j++];}i=j+1;return out;} |
| const rest=s.slice(i); |
| let m=rest.match(/^(TRUE|FALSE)(?![A-Z0-9(])/i); |
| if(m){i+=m[0].length;return m[0].toUpperCase()==="TRUE";} |
| m=rest.match(/^([A-Z]+)\(/i); |
| if(m&&FN[m[1].toUpperCase()]){ |
| const name=m[1].toUpperCase();i+=m[0].length; |
| const args=[];ws(); |
| if(s[i]===")"){i++;} |
| else{ |
| while(true){args.push(parseArg());ws();if(s[i]===","){i++;continue;}if(s[i]===")"){i++;break;}break;} |
| } |
| return FN[name](args); |
| } |
| m=rest.match(/^\$?[A-Z]+\$?\d+:\$?[A-Z]+\$?\d+/); |
| if(m){i+=m[0].length;const[a,b]=m[0].replace(/\$/g,"").split(":");return CALC.range(a,b);} |
| m=rest.match(/^\$?([A-Z]+):\$?([A-Z]+)/); |
| if(m){i+=m[0].length;return CALC.range(m[1]+"1",m[2]+ROWS);} |
| m=rest.match(/^\$?[A-Z]+\$?\d+/); |
| if(m){i+=m[0].length;return CALC.val(m[0].replace(/\$/g,""));} |
| m=rest.match(/^\d+\.?\d*/); |
| if(m){i+=m[0].length;return parseFloat(m[0]);} |
| i++;return"#NAME?"; |
| } |
| function parseArg(){ |
| ws(); |
| const rest=s.slice(i); |
| const m=rest.match(/^\$?[A-Z]+\$?\d+:\$?[A-Z]+\$?\d+/); |
| if(m&&!rest.slice(m[0].length).match(/^\s*[+\-*/^]/)){ |
| const peek=rest.slice(m[0].length).trimStart(); |
| if(peek[0]===","||peek[0]===")"){ |
| i+=m[0].length;const[a,b]=m[0].replace(/\$/g,"").split(":"); |
| return CALC.rangeCells(a,b); |
| } |
| } |
| return parseExpr(); |
| } |
| const r=parseExpr(); |
| if(typeof r==="number"&&isFinite(r))return Math.round(r*1e8)/1e8; |
| if(typeof r==="number")return"#DIV/0!"; |
| return r; |
| }catch(e){return"#ERROR";} |
| }; |
| </script> |
| <script> |
| "use strict"; |
| |
| const panel=document.getElementById("sidepanel"); |
| function openPanel(html){panel.classList.add("open");panel.innerHTML=html;} |
| function closePanel(){panel.classList.remove("open");panel.innerHTML="";} |
| let chartObj=null; |
| |
| function uiChart(){ |
| const[a,b,x,y]=selRect(); |
| openPanel(`<h3>グラフの挿入</h3> |
| <p class="mini">選択範囲 ${A(a,b)}:${A(x,y)}（1列目=ラベル、2列目以降=系列）</p> |
| <div style="margin:8px 0">種類 |
| <select id="ctype"><option value="bar">縦棒</option><option value="line">折れ線</option><option value="pie">円</option><option value="scatter">散布図</option></select> |
| <button class="go" id="cgo">挿入</button> <button onclick="closePanel()">閉じる</button></div> |
| <div style="position:relative;height:260px;background:#fff;border:1px solid var(--bd);border-radius:6px;padding:6px"><canvas id="cvs"></canvas></div>`); |
| document.getElementById("cgo").onclick=()=>{ |
| const t=document.getElementById("ctype").value; |
| const labels=[],sers=[]; |
| for(let c=a+1;c<=x;c++)sers.push({label:String(CALC.val(A(c,b))),data:[]}); |
| for(let r=b+1;r<=y;r++){ |
| labels.push(String(CALC.val(A(a,r)))); |
| for(let c=a+1;c<=x;c++){CALC.depth=0;sers[c-a-1].data.push(CALC.val(A(c,r)));} |
| } |
| const pal=["#1D9E75","#378ADD","#EF9F27","#D85A30","#7F77DD","#D4537E","#639922","#E24B4A"]; |
| if(chartObj)chartObj.destroy(); |
| chartObj=new Chart(document.getElementById("cvs"),{ |
| type:t==="scatter"?"scatter":t, |
| data:{labels,datasets:sers.map((s,i)=>({...s, |
| data:t==="scatter"?s.data.map((v,j)=>({x:j+1,y:v})):s.data, |
| backgroundColor:t==="pie"?pal:pal[i%pal.length],borderColor:pal[i%pal.length],fill:false}))}, |
| options:{responsive:true,maintainAspectRatio:false} |
| }); |
| msg("グラフを挿入しました"); |
| }; |
| document.getElementById("cgo").click(); |
| } |
| |
| function uiPivot(){ |
| const[a,b,x,y]=selRect(); |
| let heads=[];for(let c=a;c<=x;c++)heads.push({c,name:String(CALC.val(A(c,b)))}); |
| const opt=heads.map(h=>`<option value="${h.c}">${h.name}</option>`).join(""); |
| openPanel(`<h3>ピボットテーブル</h3> |
| <p class="mini">データ範囲 ${A(a,b)}:${A(x,y)}（1行目=見出し）</p> |
| <div>行: <select id="prow">${opt}</select></div> |
| <div>値: <select id="pval">${opt}</select> |
| 集計: <select id="pagg"><option value="sum">合計</option><option value="count">件数</option><option value="avg">平均</option><option value="max">最大</option><option value="min">最小</option></select></div> |
| <div style="margin:6px 0"><button class="go" id="pgo">生成</button> <button onclick="closePanel()">閉じる</button></div> |
| <div id="pout"></div>`); |
| document.getElementById("pgo").onclick=()=>{ |
| const rc=+document.getElementById("prow").value, vc=+document.getElementById("pval").value, ag=document.getElementById("pagg").value; |
| const groups={}; |
| for(let r=b+1;r<=y;r++){ |
| CALC.depth=0;const k=String(CALC.val(A(rc,r)));CALC.depth=0;const v=CALC.val(A(vc,r)); |
| if(!groups[k])groups[k]=[];if(typeof v==="number")groups[k].push(v); |
| } |
| const agg={sum:v=>v.reduce((a,b)=>a+b,0),count:v=>v.length,avg:v=>v.length?v.reduce((a,b)=>a+b,0)/v.length:0,max:v=>v.length?Math.max(...v):0,min:v=>v.length?Math.min(...v):0}[ag]; |
| let rows=Object.entries(groups).map(([k,v])=>[k,Math.round(agg(v)*100)/100]); |
| rows.sort((p,q)=>q[1]-p[1]); |
| const total=Math.round(rows.reduce((s,r)=>s+r[1],0)*100)/100; |
| let h='<table class="pvt" style="width:100%;border:1px solid var(--bd);margin-top:6px"><tr><th style="text-align:left;background:var(--hd)">'+heads.find(x=>x.c===rc).name+'</th><th style="text-align:right;background:var(--hd)">'+document.getElementById("pagg").selectedOptions[0].text+' / '+heads.find(x=>x.c===vc).name+'</th></tr>'; |
| rows.forEach(r=>h+='<tr><td>'+r[0]+'</td><td style="text-align:right">'+r[1].toLocaleString()+'</td></tr>'); |
| h+='<tr style="font-weight:600;border-top:2px solid #666"><td>総計</td><td style="text-align:right">'+total.toLocaleString()+'</td></tr></table>'; |
| h+='<div style="margin-top:6px"><button id="pins">新シートへ書き出し</button></div>'; |
| document.getElementById("pout").innerHTML=h; |
| document.getElementById("pins").onclick=()=>{ |
| let i=wb.order.length+1;while(wb.sheets["Pivot"+i])i++; |
| const name="Pivot"+i;wb.sheets[name]={cells:{},fmt:{}};wb.order.push(name); |
| const cells=wb.sheets[name].cells; |
| cells["A1"]=heads.find(x=>x.c===rc).name;cells["B1"]="集計"; |
| rows.forEach((r,idx)=>{cells["A"+(idx+2)]=r[0];cells["B"+(idx+2)]=String(r[1]);}); |
| cells["A"+(rows.length+2)]="総計";cells["B"+(rows.length+2)]="=SUM(B2:B"+(rows.length+1)+")"; |
| wb.active=name;renderAll();msg("ピボットを"+name+"に書き出しました"); |
| }; |
| }; |
| document.getElementById("pgo").click(); |
| } |
| |
| function uiMacro(){ |
| openPanel(`<h3>マクロ（VBA風API）</h3> |
| <p class="mini">Range("A1")/Range("A1:C5") .value .values .set(v) .clear() .bold() .fill(色) / Cells(r,c) / Selection / MsgBox(t) / InputBox(t) / ActiveSheet.name</p> |
| <textarea id="mcode">// 例: 純増2000超に★、書式も設定 |
| for (let r = 2; r <= 7; r++) { |
| if (Range("D"+r).value > 2000) { |
| Range("G"+r).set("★好調"); |
| Range("G"+r).fill("#FFF2CC"); |
| } else { |
| Range("G"+r).clear(); |
| } |
| } |
| Range("G1").set("判定"); Range("G1").bold(); |
| MsgBox("完了: " + ActiveSheet.name + " / 選択=" + Selection.address);</textarea> |
| <div><button class="go" id="mrun">実行 (F5)</button> <button onclick="closePanel()">閉じる</button></div> |
| <div id="mout" class="mini" style="margin-top:6px;white-space:pre-wrap"></div>`); |
| const run=()=>{ |
| const out=document.getElementById("mout");out.textContent=""; |
| const mkRange=spec=>{ |
| spec=String(spec).toUpperCase(); |
| let [p1,p2]=spec.includes(":")?spec.split(":").map(parseA):[parseA(spec),parseA(spec)]; |
| if(!p1||!p2)throw new Error("不正な参照: "+spec); |
| const each=fn=>{for(let c=Math.min(p1[0],p2[0]);c<=Math.max(p1[0],p2[0]);c++)for(let r=Math.min(p1[1],p2[1]);r<=Math.max(p1[1],p2[1]);r++)fn(A(c,r));}; |
| return{ |
| get address(){return A(p1[0],p1[1])+(spec.includes(":")?":"+A(p2[0],p2[1]):"");}, |
| get value(){CALC.depth=0;return CALC.val(A(p1[0],p1[1]));}, |
| get values(){return CALC.rangeCells(A(p1[0],p1[1]),A(p2[0],p2[1]));}, |
| get formula(){return sh().cells[A(p1[0],p1[1])]||"";}, |
| set:v=>each(a=>{sh().cells[a]=String(v);if(String(v)==="")delete sh().cells[a];}), |
| clear:()=>each(a=>{delete sh().cells[a];delete sh().fmt[a];}), |
| bold:on=>each(a=>{sh().fmt[a]=sh().fmt[a]||{};sh().fmt[a].b=on!==false;}), |
| fill:col=>each(a=>{sh().fmt[a]=sh().fmt[a]||{};sh().fmt[a].bg=col;}) |
| }; |
| }; |
| const api={ |
| Range:mkRange, |
| Cells:(r,c)=>mkRange(A(c-1,r)), |
| get Selection(){const[a,b,x,y]=selRect();return mkRange(A(a,b)+":"+A(x,y));}, |
| MsgBox:t=>{out.textContent+="MsgBox: "+t+"\n";}, |
| InputBox:t=>prompt(t)||"", |
| ActiveSheet:{get name(){return wb.active;}} |
| }; |
| try{ |
| pushUndo(); |
| Function("Range","Cells","Selection","MsgBox","InputBox","ActiveSheet",document.getElementById("mcode").value)(api.Range,api.Cells,api.Selection,api.MsgBox,api.InputBox,api.ActiveSheet); |
| renderAll();msg("マクロ実行完了"); |
| }catch(e){out.textContent+="エラー: "+e.message;msg("マクロエラー");} |
| }; |
| document.getElementById("mrun").onclick=run; |
| document.getElementById("mcode").addEventListener("keydown",e=>{if(e.key==="F5"){e.preventDefault();run();}e.stopPropagation();}); |
| } |
| |
| |
| function saveXlsx(){ |
| const wbo=XLSX.utils.book_new(); |
| wb.order.forEach(name=>{ |
| const cells=wb.sheets[name].cells,aoa=[]; |
| Object.keys(cells).forEach(a=>{ |
| const p=parseA(a);if(!p)return; |
| while(aoa.length<p[1])aoa.push([]); |
| const old=wb.active;wb.active=name;CALC.depth=0; |
| const raw=cells[a]; |
| aoa[p[1]-1][p[0]]=String(raw)[0]==="="?CALC.evalCell(a):coerce(raw); |
| wb.active=old; |
| }); |
| XLSX.utils.book_append_sheet(wbo,XLSX.utils.aoa_to_sheet(aoa),name); |
| }); |
| XLSX.writeFile(wbo,(document.getElementById("fname").textContent||"Book1")+".xlsx"); |
| msg("xlsxを保存しました（数式は計算値として保存）"); |
| } |
| function saveCsv(){ |
| const cells=sh().cells;let maxR=0,maxC=0; |
| Object.keys(cells).forEach(a=>{const p=parseA(a);if(p){maxC=Math.max(maxC,p[0]);maxR=Math.max(maxR,p[1]);}}); |
| let lines=[]; |
| for(let r=1;r<=maxR;r++){const row=[];for(let c=0;c<=maxC;c++){CALC.depth=0;let v=cells[A(c,r)]===undefined?"":CALC.val(A(c,r));v=String(v);if(/[",\n]/.test(v))v='"'+v.replace(/"/g,'""')+'"';row.push(v);}lines.push(row.join(","));} |
| const blob=new Blob(["\uFEFF"+lines.join("\n")],{type:"text/csv"}); |
| const aEl=document.createElement("a");aEl.href=URL.createObjectURL(blob);aEl.download=wb.active+".csv";aEl.click(); |
| msg("CSVを保存しました"); |
| } |
| function openFile(file){ |
| const rd=new FileReader(); |
| rd.onload=e=>{ |
| pushUndo(); |
| if(file.name.endsWith(".csv")){ |
| const txt=e.target.result.replace(/^\uFEFF/,""); |
| const cells={}; |
| txt.split(/\r?\n/).forEach((line,r)=>{ |
| let c=0,cur="",inQ=false,ci=0; |
| const putc=()=>{if(cur!=="")cells[A(ci,r+1)]=cur;cur="";ci++;}; |
| for(const ch of line){ |
| if(inQ){if(ch==='"')inQ=false;else cur+=ch;} |
| else if(ch==='"')inQ=true; |
| else if(ch===",")putc(); |
| else cur+=ch; |
| } |
| putc(); |
| }); |
| sh().cells=cells;sh().fmt={}; |
| }else{ |
| const wbi=XLSX.read(e.target.result,{type:"array"}); |
| wb={sheets:{},order:[],active:wbi.SheetNames[0]}; |
| wbi.SheetNames.forEach(n=>{ |
| const ws=wbi.Sheets[n],cells={}; |
| Object.keys(ws).forEach(a=>{ |
| if(a[0]==="!")return; |
| const cell=ws[a]; |
| cells[a]=cell.f?"="+cell.f:String(cell.v); |
| }); |
| wb.sheets[n]={cells,fmt:{}};wb.order.push(n); |
| }); |
| alert("注意: 条件付き書式・図形・VBAマクロは読み込み対象外です(非対応)。上書き保存するとこれらは失われるため、保存は別名で行われます。"); |
| } |
| document.getElementById("fname").textContent=file.name.replace(/\.(xlsx|csv)$/,"")+"（復元）"; |
| renderAll();msg(file.name+" を開きました"); |
| }; |
| file.name.endsWith(".csv")?rd.readAsText(file):rd.readAsArrayBuffer(file); |
| } |
| |
| |
| document.getElementById("tabstrip").addEventListener("click",e=>{ |
| const t=e.target.closest(".rtab");if(!t)return; |
| document.querySelectorAll(".rtab").forEach(x=>x.classList.remove("on"));t.classList.add("on"); |
| ["home","insert","file"].forEach(n=>document.getElementById("rb-"+n).style.display=n===t.dataset.tab?"flex":"none"); |
| }); |
| function autoSum(){ |
| const[c,r]=cur;let r0=r-1; |
| while(r0>=1){CALC.depth=0;const v=CALC.val(A(c,r0));if(typeof v!=="number"||sh().cells[A(c,r0)]===undefined)break;r0--;} |
| r0++; |
| if(r0>=r){msg("上に数値データがありません");return;} |
| pushUndo();sh().cells[A(c,r)]="=SUM("+A(c,r0)+":"+A(c,r-1)+")";renderAll();msg("オートSUM: =SUM("+A(c,r0)+":"+A(c,r-1)+")"); |
| } |
| function sortSel(){ |
| const[a,b,x,y]=selRect();if(y<=b){msg("複数行を選択してください");return;} |
| pushUndo(); |
| const rows=[]; |
| for(let r=b;r<=y;r++){const row=[];for(let c=a;c<=x;c++)row.push(sh().cells[A(c,r)]);rows.push(row);} |
| const keyC=cur[0]-a>=0&&cur[0]<=x?cur[0]-a:0; |
| rows.sort((p,q)=>{const pv=coerce(p[keyC]??""),qv=coerce(q[keyC]??"");return(typeof qv==="number"?qv:0)-(typeof pv==="number"?pv:0);}); |
| rows.forEach((row,i)=>row.forEach((v,j)=>{const addr=A(a+j,b+i);if(v===undefined)delete sh().cells[addr];else sh().cells[addr]=v;})); |
| renderAll();msg("降順で並べ替えました（キー列: "+colL(a+keyC)+"）"); |
| } |
| document.body.addEventListener("click",e=>{ |
| if(e.target.closest("#colorPalette"))return; |
| const colorButton=e.target.closest(".colorbtn"); |
| if(colorButton){ |
| e.stopPropagation(); |
| showColorPalette(colorButton,colorButton.querySelector("#bgcol")?"bg":"fg"); |
| return; |
| } |
| document.getElementById("colorPalette").style.display="none"; |
| const b=e.target.closest("[data-cmd]");if(!b)return; |
| const cmd=b.dataset.cmd; |
| if(cmd==="bold")toggleFmt("b"); |
| else if(cmd==="italic")toggleFmt("i"); |
| else if(cmd==="border")setFmt("bd",true); |
| else if(cmd==="comma")toggleFmt("comma"); |
| else if(cmd==="percent")toggleFmt("percent"); |
| else if(cmd==="decinc")adjustDecimals(1); |
| else if(cmd==="decdec")adjustDecimals(-1); |
| else if(cmd==="clearfmt"){pushUndo();eachSel(a=>delete sh().fmt[a]);renderAll();} |
| else if(cmd==="undo")undo(); |
| else if(cmd==="redo")redo(); |
| else if(cmd==="copy")copySel(); |
| else if(cmd==="cut"){copySel();delSel();} |
| else if(cmd==="paste")pasteClip(); |
| else if(cmd==="alleft")setFmt("al","left"); |
| else if(cmd==="alcenter")setFmt("al","center"); |
| else if(cmd==="alright")setFmt("al","right"); |
| else if(cmd==="autosum")autoSum(); |
| else if(cmd==="sortd")sortSel(); |
| else if(cmd==="chart")uiChart(); |
| else if(cmd==="pivot")uiPivot(); |
| else if(cmd==="macro")uiMacro(); |
| else if(cmd==="open")document.getElementById("filein").click(); |
| else if(cmd==="savex")saveXlsx(); |
| else if(cmd==="savec")saveCsv(); |
| }); |
| const PALETTE=["#000000","#404040","#808080","#BFBFBF","#FFFFFF","#C00000","#FF0000","#FFC000","#FFFF00","#92D050","#00B050","#00B0F0","#0070C0","#002060","#7030A0","#E2F0D9","#DDEBF7","#FFF2CC","#FCE4D6","#F4CCCC","#D9EAD3","#CFE2F3","#D9D2E9","#EADCF8"]; |
| function showColorPalette(anchor,type){ |
| const pop=document.getElementById("colorPalette"); |
| pop.dataset.type=type; |
| pop.innerHTML='<div class="ptitle">'+(type==="bg"?"塗りつぶしの色":"フォントの色")+'</div><div class="pgrid">'+ |
| PALETTE.map(c=>'<button class="sw" style="--c:'+c+'" data-color="'+c+'" title="'+c+'"></button>').join("")+ |
| '</div><div class="pactions"><button data-clear="1">色なし</button><button data-close="1">閉じる</button></div>'; |
| const r=anchor.getBoundingClientRect(); |
| pop.style.left=Math.min(r.left,innerWidth-220)+"px"; |
| pop.style.top=Math.min(r.bottom+6,innerHeight-210)+"px"; |
| pop.style.display="block"; |
| } |
| document.getElementById("colorPalette").addEventListener("click",e=>{ |
| e.stopPropagation(); |
| const pop=e.currentTarget,type=pop.dataset.type; |
| const sw=e.target.closest("[data-color]"); |
| if(sw){ |
| const c=sw.dataset.color; |
| if(type==="bg"){document.getElementById("bgbar").style.background=c;setFmt("bg",c);} |
| else{document.getElementById("fgbar").style.background=c;setFmt("fg",c);} |
| pop.style.display="none"; |
| return; |
| } |
| if(e.target.closest("[data-clear]")){ |
| pushUndo(); |
| eachSel(a=>{sh().fmt[a]=sh().fmt[a]||{};delete sh().fmt[a][type==="bg"?"bg":"fg"];}); |
| if(type==="bg")document.getElementById("bgbar").style.background="transparent"; |
| else document.getElementById("fgbar").style.background="transparent"; |
| renderAll();pop.style.display="none";return; |
| } |
| if(e.target.closest("[data-close]"))pop.style.display="none"; |
| }); |
| document.getElementById("fsize").addEventListener("change",e=>setFmt("fs",+e.target.value)); |
| document.getElementById("filein").addEventListener("change",e=>{if(e.target.files[0])openFile(e.target.files[0]);e.target.value="";}); |
| function setZoom(pct){ |
| const next=Math.max(25,Math.min(200,Math.round(pct/5)*5)); |
| zoom=next/100; |
| document.getElementById("zoomrange").value=String(next); |
| document.getElementById("zoompct").textContent=next+"%"; |
| applySizes();renderAll();msg("ズーム: "+next+"%"); |
| } |
| document.getElementById("zoomrange").addEventListener("input",e=>setZoom(+e.target.value)); |
| document.getElementById("zminus").addEventListener("click",()=>setZoom(zoom*100-5)); |
| document.getElementById("zplus").addEventListener("click",()=>setZoom(zoom*100+5)); |
| document.getElementById("gridwrap").addEventListener("wheel",e=>{ |
| if(!(e.ctrlKey||e.metaKey))return; |
| e.preventDefault(); |
| setZoom(zoom*100+(e.deltaY<0?5:-5)); |
| },{passive:false}); |
| document.getElementById("fx").addEventListener("input",e=>previewFormula(e.target.value)); |
| document.getElementById("fx").addEventListener("focus",e=>previewFormula(e.target.value)); |
| document.getElementById("fx").addEventListener("blur",clearFormulaPreview); |
| document.addEventListener("paste",e=>{ |
| if(editTd||["INPUT","TEXTAREA"].includes(document.activeElement.tagName))return; |
| const text=e.clipboardData&&e.clipboardData.getData("text/plain"); |
| if(!text)return; |
| e.preventDefault(); |
| pasteMatrix(parseClipboardText(text)); |
| }); |
| document.getElementById("cellname").addEventListener("click",e=>{e.target.removeAttribute("readonly");e.target.select();}); |
| document.getElementById("cellname").addEventListener("keydown",e=>{ |
| if(e.key==="Enter"){const p=parseA(e.target.value.toUpperCase());if(p&&p[0]<COLS&&p[1]<=ROWS){cur=[p[0],p[1]];anchor=[...cur];selEnd=[...cur];renderAll();scrollToCur();}e.target.setAttribute("readonly","");document.getElementById("gridwrap").focus();} |
| e.stopPropagation(); |
| }); |
| |
| |
| (function(){ |
| const seed=[ |
| ["月","新規MRR","解約","純増","商談数","受注数","判定","","単価表","プラン","単価"], |
| ["1月",1200,200,"=B2-C2",24,6,"","","","Lite",5000], |
| ["2月",1500,180,"=B3-C3",28,8,"","","","Std",15000], |
| ["3月",2100,250,"=B4-C4",35,11,"","","","Pro",40000], |
| ["4月",1800,300,"=B5-C5",30,9], |
| ["5月",2400,220,"=B6-C6",38,13], |
| ["6月",2900,260,"=B7-C7",42,15], |
| ["合計","=SUM(B2:B7)","=SUM(C2:C7)","=SUM(D2:D7)","=SUM(E2:E7)","=SUM(F2:F7)"], |
| ["受注率","=F8/E8","平均純増","=AVERAGE(D2:D7)","最高月","=INDEX(A2:A7,MATCH(MAX(D2:D7),D2:D7,0))"], |
| ["Pro単価","=VLOOKUP(\"Pro\",J2:K4,2,FALSE)","好調月数","=COUNTIF(D2:D7,\">2000\")"] |
| ]; |
| const cells=sh().cells; |
| seed.forEach((row,r)=>row.forEach((v,c)=>{if(v!==""&&v!==undefined)cells[A(c,r+1)]=String(v);})); |
| ["A1","B1","C1","D1","E1","F1","G1","J1","K1"].forEach(a=>sh().fmt[a]={b:true,bg:"#E1F5EE"}); |
| ["B8","C8","D8","E8","F8"].forEach(a=>sh().fmt[a]={b:true,comma:true}); |
| })(); |
| buildGrid();renderAll(); |
| </script> |
| </body> |
| </html> |
|
|
