ツール管理へ戻る

v1 のソース

<!DOCTYPE html>
<html lang="ja">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>ロット条件抽出ツール</title>
<style>
  * { box-sizing: border-box; }
  body { font-family: "Meiryo", "Yu Gothic", sans-serif; margin: 0; padding: 16px; color: #222; background: #f4f6f9; }
  h1 { font-size: 20px; margin: 0 0 12px; }
  h2 { font-size: 15px; margin: 0 0 8px; }
  #err { background: #fdecea; color: #b71c1c; padding: 8px 12px; margin-bottom: 12px; display: none; white-space: pre-wrap; border-radius: 4px; }
  .panel { border: 1px solid #ccd3dd; border-radius: 6px; padding: 12px; margin-bottom: 12px; background: #fff; }
  .row { margin: 6px 0; }
  .items label, .row label { display: inline-block; margin: 3px 14px 3px 0; }
  button { padding: 6px 14px; margin-right: 8px; cursor: pointer; border: 1px solid #4a78c2; background: #4a78c2; color: #fff; border-radius: 4px; }
  button.sub { background: #fff; color: #4a78c2; }
  button.toggle.on { background: #2e7d32; border-color: #2e7d32; color: #fff; }
  button.toggle.off { background: #fff; border-color: #888; color: #555; }
  input[type=number] { width: 80px; padding: 3px; }
  .summary { margin: 8px 0; font-weight: bold; }
  .tblwrap { max-height: 440px; overflow: auto; border: 1px solid #ccd3dd; background: #fff; }
  table { border-collapse: collapse; width: 100%; font-size: 12px; }
  th, td { border: 1px solid #c8cfd9; padding: 4px 6px; text-align: center; white-space: nowrap; }
  th { background: #e8eef7; position: sticky; top: 0; }
  td.ng { color: #c62828; font-weight: bold; }
  td.left { text-align: left; white-space: normal; }
  .bars { margin-top: 12px; }
  .bar-row { display: flex; align-items: center; margin: 3px 0; font-size: 12px; }
  .bar-label { width: 80px; }
  .bar-track { flex: 1; background: #e6ebf2; height: 16px; border-radius: 3px; overflow: hidden; }
  .bar-fill { background: #4a78c2; height: 100%; }
  .bar-val { width: 50px; text-align: right; }
  .note { font-size: 12px; color: #555; }
</style>
</head>
<body>
<h1>ロット条件抽出ツール(不良率・不良項目)</h1>
<div id="err"></div>

<div class="panel">
  <h2>1. ファイルを選択</h2>
  <input type="file" id="file" accept=".xlsx">
  <div class="note" id="fileInfo">xlsxファイルを選択してください(データはこのブラウザ内だけで処理され、外部へ送信されません)。</div>
</div>

<div id="ui" style="display:none">
  <div class="panel">
    <h2>2. 条件を指定</h2>
    <div class="row">
      <label><input type="checkbox" id="useRate" checked> 不良率の条件を使う</label>
      不良率
      <input type="number" id="thr" value="0.5" step="0.1" min="0"> % 以上
    </div>
    <div class="row">
      <b>不良項目</b>(選択した項目の件数が1以上のロット)
      <button class="sub" id="allOn" type="button">全選択</button>
      <button class="sub" id="allOff" type="button">全解除</button>
      <div class="items" id="items"></div>
    </div>
    <div class="row">
      <b>条件の組み合わせ:</b>
      <label><input type="radio" name="mode" value="AND" checked> AND(両方に当てはまる)</label>
      <label><input type="radio" name="mode" value="OR"> OR(どちらかに当てはまる)</label>
    </div>
    <div class="row">
      <b>N数0の扱い:</b>
      <button id="zeroBtn" class="toggle on" type="button">N数0を省く:オン</button>
      <span class="note" id="zeroInfo"></span>
    </div>
    <div class="row">
      <button id="run" type="button">抽出する</button>
      <button class="sub" id="dlCsv" type="button">抽出結果をCSV出力</button>
    </div>
    <div class="note">不良項目を何も選ばない場合は、不良率の条件だけで絞り込みます。両方とも使わない場合は、(N数0を省く設定を除き)全ロットを表示します。</div>
  </div>

  <div class="summary" id="summary"></div>
  <div class="tblwrap"><table id="tbl"></table></div>

  <div class="panel bars">
    <h2>機械別の該当ロット数</h2>
    <div id="bars"></div>
  </div>
</div>

<script>
(function () {
  var allRows = [];
  var itemNames = [];
  var result = [];
  var skipZero = true;

  function showErr(e, extra) {
    var el = document.getElementById('err');
    el.style.display = 'block';
    el.textContent = 'エラーが発生しました: ' + (e && e.message ? e.message : e) + (extra ? '\n' + extra : '');
  }
  function clearErr() { document.getElementById('err').style.display = 'none'; }
  function esc(s) {
    return String(s == null ? '' : s).replace(/[&<>"]/g, function (c) {
      return { '&': '&amp;', '<': '&lt;', '>': '&gt;', '"': '&quot;' }[c];
    });
  }

  /* ---------- xlsx 読み込み(ZIP解凍 + XML解析) ---------- */
  function u16(b, o) { return b[o] | (b[o + 1] << 8); }
  function u32(b, o) { return (b[o] | (b[o + 1] << 8) | (b[o + 2] << 16) | (b[o + 3] << 24)) >>> 0; }

  async function inflateRaw(bytes) {
    var ds = new DecompressionStream('deflate-raw');
    var stream = new Blob([bytes]).stream().pipeThrough(ds);
    var buf = await new Response(stream).arrayBuffer();
    return new Uint8Array(buf);
  }

  async function readZip(buf) {
    var b = new Uint8Array(buf);
    var eocd = -1;
    for (var i = b.length - 22; i >= 0; i--) {
      if (b[i] === 0x50 && b[i + 1] === 0x4b && b[i + 2] === 5 && b[i + 3] === 6) { eocd = i; break; }
    }
    if (eocd < 0) throw new Error('xlsxファイルの形式を認識できません。');
    var count = u16(b, eocd + 10);
    var cdOff = u32(b, eocd + 16);
    var files = {};
    var p = cdOff;
    for (var n = 0; n < count; n++) {
      if (u32(b, p) !== 0x02014b50) break;
      var method = u16(b, p + 10);
      var csize = u32(b, p + 20);
      var nlen = u16(b, p + 28), elen = u16(b, p + 30), clen = u16(b, p + 32);
      var lho = u32(b, p + 42);
      var name = new TextDecoder().decode(b.subarray(p + 46, p + 46 + nlen));
      files[name] = { method: method, csize: csize, lho: lho };
      p += 46 + nlen + elen + clen;
    }
    async function get(name) {
      var f = files[name];
      if (!f) return null;
      var lnl = u16(b, f.lho + 26), lel = u16(b, f.lho + 28);
      var start = f.lho + 30 + lnl + lel;
      var data = b.subarray(start, start + f.csize);
      if (f.method === 0) return new TextDecoder().decode(data);
      return new TextDecoder().decode(await inflateRaw(data));
    }
    return { get: get, names: Object.keys(files) };
  }

  function parseXml(s) { return new DOMParser().parseFromString(s, 'application/xml'); }

  function colIndex(ref) {
    var m = /^([A-Z]+)/.exec(ref);
    var s = m ? m[1] : 'A', n = 0;
    for (var i = 0; i < s.length; i++) n = n * 26 + (s.charCodeAt(i) - 64);
    return n - 1;
  }

  async function loadWorkbook(buf) {
    var zip = await readZip(buf);
    var wbXml = parseXml(await zip.get('xl/workbook.xml'));
    var relXml = parseXml(await zip.get('xl/_rels/workbook.xml.rels'));
    var rels = {};
    Array.prototype.forEach.call(relXml.getElementsByTagName('Relationship'), function (r) {
      rels[r.getAttribute('Id')] = r.getAttribute('Target');
    });

    var shared = [];
    var ssText = await zip.get('xl/sharedStrings.xml');
    if (ssText) {
      var ss = parseXml(ssText);
      Array.prototype.forEach.call(ss.getElementsByTagName('si'), function (si) {
        var t = '';
        Array.prototype.forEach.call(si.getElementsByTagName('t'), function (x) { t += x.textContent; });
        shared.push(t);
      });
    }

    var sheets = [];
    var sheetEls = wbXml.getElementsByTagName('sheet');
    for (var i = 0; i < sheetEls.length; i++) {
      var el = sheetEls[i];
      var name = el.getAttribute('name');
      var rid = el.getAttribute('r:id') || el.getAttributeNS('http://schemas.openxmlformats.org/officeDocument/2006/relationships', 'id');
      var target = rels[rid];
      if (!target) continue;
      var path = target.charAt(0) === '/' ? target.slice(1) : 'xl/' + target.replace(/^\.\//, '');
      var xml = await zip.get(path);
      if (!xml) continue;
      sheets.push({ name: name, data: parseSheetXml(xml, shared) });
    }
    return sheets;
  }

  function parseSheetXml(xml, shared) {
    var doc = parseXml(xml);
    var rows = [];
    Array.prototype.forEach.call(doc.getElementsByTagName('row'), function (rowEl) {
      var rIdx = parseInt(rowEl.getAttribute('r'), 10) - 1;
      var arr = [];
      Array.prototype.forEach.call(rowEl.getElementsByTagName('c'), function (c) {
        var ci = colIndex(c.getAttribute('r'));
        var t = c.getAttribute('t');
        var vEl = c.getElementsByTagName('v')[0];
        var val = '';
        if (t === 'inlineStr') {
          val = c.textContent;
        } else if (vEl) {
          var raw = vEl.textContent;
          if (t === 's') val = shared[parseInt(raw, 10)] || '';
          else if (t === 'str' || t === 'e') val = raw;
          else if (t === 'b') val = raw === '1';
          else { var num = Number(raw); val = isNaN(num) ? raw : num; }
        }
        arr[ci] = val;
      });
      rows[rIdx] = arr;
    });
    for (var i = 0; i < rows.length; i++) if (!rows[i]) rows[i] = [];
    return rows;
  }

  /* ---------- データ解析 ---------- */
  function norm(s) { return String(s == null ? '' : s).replace(/[\s\u3000]/g, ''); }

  function dateStr(v) {
    if (v === '' || v == null) return '';
    if (typeof v === 'number') {
      var d = new Date(Math.round((v - 25569) * 86400000));
      return d.getUTCFullYear() + '/' + ('0' + (d.getUTCMonth() + 1)).slice(-2) + '/' + ('0' + d.getUTCDate()).slice(-2);
    }
    return String(v);
  }

  function findHeader(data) {
    for (var r = 0; r < data.length; r++) {
      var row = data[r] || [];
      for (var c = 0; c < row.length; c++) {
        if (norm(row[c]) === '検査日') return { r: r, c: c };
      }
    }
    return null;
  }

  function findCol(head, key) {
    var c;
    for (c = 0; c < head.length; c++) if (norm(head[c]) === key) return c;
    for (c = 0; c < head.length; c++) if (norm(head[c]).indexOf(key) >= 0) return c;
    return -1;
  }

  function parseSheet(name, data) {
    var h = findHeader(data);
    if (!h) return null;
    var head = data[h.r];
    var cDate = findCol(head, '検査日');
    var cLot = findCol(head, 'ロット№');
    var cCut = findCol(head, '切落日');
    var cN = findCol(head, 'N');
    var cDef = findCol(head, '不良数');
    var cRate = findCol(head, '不良率');
    var cJudge = findCol(head, '判定');
    var cContent = findCol(head, '不良内容');
    var cNote = findCol(head, '備考');
    if (cN < 0) cN = 3;
    if (cDate < 0 || cDef < 0 || cContent < 0) return null;

    var nameRow = data[h.r + 1] || [];
    var items = [];
    var endCol = cNote > cContent ? cNote : nameRow.length;
    for (var c = cContent; c < endCol; c++) {
      var nm = String(nameRow[c] == null ? '' : nameRow[c]).trim();
      if (nm !== '') items.push({ col: c, name: nm });
    }

    var machine = name;
    for (var r = 0; r < Math.min(6, data.length); r++) {
      var rr = data[r] || [];
      for (var cc = 0; cc < rr.length; cc++) {
        if (norm(rr[cc]) === '機械№') {
          for (var k = cc + 1; k <= cc + 3; k++) {
            if (rr[k] !== undefined && rr[k] !== '') { machine = String(rr[k]); break; }
          }
        }
      }
    }

    var out = [];
    for (var i = h.r + 2; i < data.length; i++) {
      var row = data[i] || [];
      var d = row[cDate], N = row[cN];
      if (d === undefined || d === '') continue;
      var Nn = Number(N) || 0;
      var defNum = Number(row[cDef]) || 0;
      var rate = Number(row[cRate]);
      if (row[cRate] === undefined || row[cRate] === '' || isNaN(rate)) rate = Nn > 0 ? defNum / Nn : 0;
      var counts = {};
      items.forEach(function (it) {
        counts[it.name] = (counts[it.name] || 0) + (Number(row[it.col]) || 0);
      });
      out.push({
        machine: machine,
        date: dateStr(d),
        lot: row[cLot] == null ? '' : row[cLot],
        cut: dateStr(cCut >= 0 ? row[cCut] : ''),
        N: Nn,
        def: defNum,
        rate: rate,
        judge: cJudge >= 0 && row[cJudge] != null ? row[cJudge] : '',
        counts: counts,
        note: cNote >= 0 && row[cNote] != null ? row[cNote] : ''
      });
    }
    return { rows: out, items: items.map(function (x) { return x.name; }) };
  }

  /* ---------- 画面処理 ---------- */
  function buildItems() {
    var box = document.getElementById('items');
    box.innerHTML = '';
    itemNames.forEach(function (nm) {
      var l = document.createElement('label');
      var cb = document.createElement('input');
      cb.type = 'checkbox'; cb.className = 'itemchk'; cb.value = nm;
      l.appendChild(cb);
      l.appendChild(document.createTextNode(' ' + nm));
      box.appendChild(l);
    });
  }

  function selected() {
    return Array.prototype.map.call(document.querySelectorAll('.itemchk:checked'), function (e) { return e.value; });
  }

  function updateZeroBtn() {
    var btn = document.getElementById('zeroBtn');
    btn.textContent = 'N数0を省く:' + (skipZero ? 'オン' : 'オフ');
    btn.className = 'toggle ' + (skipZero ? 'on' : 'off');
    var zeroCount = allRows.filter(function (r) { return r.N === 0; }).length;
    document.getElementById('zeroInfo').textContent = 'N数0のロット: 全体で ' + zeroCount + ' 件';
  }

  function run() {
    var thr = (Number(document.getElementById('thr').value) || 0) / 100;
    var useRate = document.getElementById('useRate').checked;
    var sel = selected();
    var mode = document.querySelector('input[name=mode]:checked').value;
    result = allRows.filter(function (r) {
      if (skipZero && r.N === 0) return false;
      var okRate = useRate ? r.rate >= thr - 1e-12 : null;
      var okItem = sel.length > 0 ? sel.some(function (nm) { return (r.counts[nm] || 0) > 0; }) : null;
      if (okRate === null && okItem === null) return true;
      if (okRate === null) return okItem;
      if (okItem === null) return okRate;
      return mode === 'AND' ? (okRate && okItem) : (okRate || okItem);
    });
    updateZeroBtn();
    render();
  }

  function render() {
    var html = '<thead><tr><th>機械№</th><th>検査日</th><th>ロット№</th><th>N</th><th>不良数</th><th>不良率</th><th>判定</th>';
    itemNames.forEach(function (nm) { html += '<th>' + esc(nm) + '</th>'; });
    html += '<th>備考</th></tr></thead><tbody>';
    result.forEach(function (r) {
      html += '<tr><td>' + esc(r.machine) + '</td><td>' + esc(r.date) + '</td><td>' + esc(r.lot) + '</td><td>' + r.N +
        '</td><td>' + r.def + '</td><td>' + (r.rate * 100).toFixed(2) + '%</td><td class="' +
        (String(r.judge).indexOf('×') >= 0 ? 'ng' : '') + '">' + esc(r.judge) + '</td>';
      itemNames.forEach(function (nm) { html += '<td>' + (r.counts[nm] || 0) + '</td>'; });
      html += '<td class="left">' + esc(r.note) + '</td></tr>';
    });
    html += '</tbody>';
    document.getElementById('tbl').innerHTML = html;

    var base = skipZero ? allRows.filter(function (r) { return r.N !== 0; }).length : allRows.length;
    document.getElementById('summary').textContent =
      '対象ロット ' + base + ' 件中、該当 ' + result.length + ' 件' + (skipZero ? '(N数0を除外中)' : '');

    var byM = {};
    result.forEach(function (r) { byM[r.machine] = (byM[r.machine] || 0) + 1; });
    var keys = Object.keys(byM).sort();
    var max = 0;
    keys.forEach(function (k) { if (byM[k] > max) max = byM[k]; });
    var bars = '';
    keys.forEach(function (k) {
      bars += '<div class="bar-row"><div class="bar-label">' + esc(k) + '</div><div class="bar-track"><div class="bar-fill" style="width:' +
        (max ? (byM[k] / max * 100) : 0) + '%"></div></div><div class="bar-val">' + byM[k] + '件</div></div>';
    });
    document.getElementById('bars').innerHTML = bars || '<div class="note">該当するロットはありません。</div>';
  }

  function csvCell(v) {
    var s = String(v == null ? '' : v);
    return /[",\n]/.test(s) ? '"' + s.replace(/"/g, '""') + '"' : s;
  }

  function downloadCsv() {
    var head = ['機械№', '検査日', 'ロット№', 'N', '不良数', '不良率(%)', '判定'].concat(itemNames).concat(['備考']);
    var lines = [head.map(csvCell).join(',')];
    result.forEach(function (r) {
      var line = [r.machine, r.date, r.lot, r.N, r.def, (r.rate * 100).toFixed(2), r.judge]
        .concat(itemNames.map(function (nm) { return r.counts[nm] || 0; })).concat([r.note]);
      lines.push(line.map(csvCell).join(','));
    });
    var blob = new Blob(['\uFEFF' + lines.join('\r\n')], { type: 'text/csv;charset=utf-8' });
    var a = document.createElement('a');
    a.href = URL.createObjectURL(blob);
    a.download = '抽出結果.csv';
    document.body.appendChild(a);
    a.click();
    document.body.removeChild(a);
    setTimeout(function () { URL.revokeObjectURL(a.href); }, 1000);
  }

  async function onFile(ev) {
    clearErr();
    var f = ev.target.files && ev.target.files[0];
    if (!f) return;
    document.getElementById('fileInfo').textContent = '読み込み中: ' + f.name;
    try {
      if (typeof DecompressionStream === 'undefined') {
        throw new Error('お使いのブラウザは必要な機能(DecompressionStream)に対応していません。最新のChromeまたはEdgeでお試しください。');
      }
      var buf = await f.arrayBuffer();
      var sheets = await loadWorkbook(buf);
      allRows = [];
      var nameSet = {}, order = [], used = 0;
      sheets.forEach(function (s) {
        if (s.name === 'まとめ') return;
        var p = parseSheet(s.name, s.data);
        if (!p) return;
        used++;
        allRows = allRows.concat(p.rows);
        p.items.forEach(function (nm) { if (!nameSet[nm]) { nameSet[nm] = true; order.push(nm); } });
      });
      itemNames = order;
      document.getElementById('fileInfo').textContent =
        f.name + ' を読み込みました(機械シート ' + used + ' 枚、ロット ' + allRows.length + ' 件)。';
      buildItems();
      document.getElementById('ui').style.display = 'block';
      run();
    } catch (e) {
      showErr(e, 'ファイルの読み込みに失敗しました。');
    }
  }

  document.getElementById('file').addEventListener('change', onFile);
  document.getElementById('run').addEventListener('click', function () { try { run(); } catch (e) { showErr(e); } });
  document.getElementById('dlCsv').addEventListener('click', function () { try { downloadCsv(); } catch (e) { showErr(e); } });
  document.getElementById('zeroBtn').addEventListener('click', function () {
    try { skipZero = !skipZero; run(); } catch (e) { showErr(e); }
  });
  document.getElementById('allOn').addEventListener('click', function () {
    Array.prototype.forEach.call(document.querySelectorAll('.itemchk'), function (e) { e.checked = true; });
  });
  document.getElementById('allOff').addEventListener('click', function () {
    Array.prototype.forEach.call(document.querySelectorAll('.itemchk'), function (e) { e.checked = false; });
  });
})();
</script>
</body>
</html>