Skip to content

Instantly share code, notes, and snippets.

@furyutei
Last active November 1, 2025 00:17
Show Gist options
  • Select an option

  • Save furyutei/82ea83395aa8661b5c323dd000511bd1 to your computer and use it in GitHub Desktop.

Select an option

Save furyutei/82ea83395aa8661b5c323dd000511bd1 to your computer and use it in GitHub Desktop.
[Excel] 数式でアナログ時計を描くのは間違っているだろうか

[Excel] 数式でアナログ時計を描くのは間違っているだろうか

こちらで出したエクセルクイズの回答例ではあるのですが、なにせ処理が重くて……もっとスマートな回答を引き続きお待ちしております。

アナログ時計の数式例

アナログ時計(色付き)

=LAMBDA(radius,[now],LET(
  ch_face,1,ch_frame,2,ch_num,3,
  ch_hour,4,ch_min,5,ch_sec,6,
  framewidth,2,
  diameter,radius*2-1,
  now,IF(ISOMITTED(now),NOW(),now),
  ndradius,30,
  ndratio,ndradius/radius,
  numdots,TEXTSPLIT(
    "-26-2i,-25-2i,-24-2i,-23-2i,-22-2i,"&
    "-26,-26+i,-26+2i,-25+2i,-24,-24+i,-24+2i,-23,-22,-22+i,-22+2i,"&
    "-2+24i,-2+25i,-2+26i,-1+26i,24i,25i,26i,1+26i,2+24i,2+25i,2+26i,"&
    "22-i,22,22+i,23-i,24-i,24,24+i,25-i,25+i,26-i,26,26+i,"&
    "-2-26i,-2-25i,-2-24i,-1-26i,-1-24i,-26i,-25i,-24i,1-24i,2-26i,2-25i,2-24i,",,",",TRUE
  ),
  face,LET(
    n,SEQUENCE(diameter,,-(diameter-1)/2),
    d,n^2+TRANSPOSE(n)^2,
    r,TAKE(TAKE(SORT(TOCOL(d)),ROUNDUP(PI()*(diameter/2)^2,0)),-ROUNDUP(diameter*PI(),0)*framewidth),
    IF(d<=TAKE(r,-1),IF(TAKE(r,1)<d,ch_frame,ch_face),"")
  ),
  handledata,LET(
    zoomratio,1.8,
    tailratio,0.12,
    raddegratio,2*PI()/360,
    rotate,LAMBDA(height,width,ch,angle,LET(
      rn,height*(1+tailratio)*zoomratio,
      cn,width*zoomratio,
      t,COMPLEX(-COS(angle*raddegratio),SIN(angle*raddegratio)),
      EXPAND(
        TOCOL(MAP(COMPLEX(SEQUENCE(rn,,-rn*tailratio/(1+tailratio)),SEQUENCE(,cn,-(cn-1)/2)),LAMBDA(c,LET(
          c,IMPRODUCT(c,t),
          COMPLEX(ROUNDDOWN(IMREAL(c)/zoomratio,0),ROUNDDOWN(IMAGINARY(c)/zoomratio,0))
        )))),,2,ch
      )
    )),
    nowsec,86400*MOD(now,1),
    VSTACK(
      rotate(radius-framewidth,1,ch_sec,MOD(nowsec,60)*6),
      rotate(radius-framewidth,1,ch_min,MOD(nowsec/60,60)*6),
      rotate(radius*0.8,3,ch_hour,MOD(nowsec/3600,12)*30)
    )
  ),
  MAKEARRAY(diameter,diameter,LAMBDA(r,c,LET(
    or,r-radius,oc,c-radius,
    IFERROR(
      VLOOKUP(COMPLEX(ROUNDDOWN(or,0),ROUNDDOWN(oc,0)),handledata,2,FALSE),
      IF(ISNUMBER(MATCH(COMPLEX(ROUNDDOWN(or*ndratio,0),ROUNDDOWN(oc*ndratio,0)),numdots,0)),
        ch_num,
        INDEX(face,r,c)
      )
    )))
  )
))(30)

これは条件付き書式で色を付けるために図形を数値で描いてます。2〜3行目を

  ch_face,"",ch_frame,"■",ch_num,"■",
  ch_hour,"◆",ch_min,"●",ch_sec,"◎",

のようにすることで、記号で描くこともできます。
アナログ時計

デジタル時計の例(おまけ)

こちらのツイート(ポスト)で出題されたデジタル時計の自分なりの数式です。

=LAMBDA([now],LET(
    now,IF(ISOMITTED(now),NOW(),now),
    digits,DROP(REDUCE(0,BASE(DECIMAL(TEXTSPLIT("URF,4I9,SV7,SUF,MU9,UEF,UFF,UQ9,UVF,UUF",","),32),2,15),LAMBDA(a,v,HSTACK(a,--WRAPROWS(REGEXEXTRACT(v,".",1),3)))),,1),
    digit,LAMBDA(n,CHOOSECOLS(digits,SEQUENCE(,3,n*3+1))),
    pad,LAMBDA(w,EXPAND(0,5,w,0)),
    colon,N(ISEVEN(SEQUENCE(5))),
    clock,DROP(REDUCE(0,REGEXEXTRACT(TEXT(now,"h:mm:ss"),".",1),LAMBDA(a,v,HSTACK(a,pad(1),IF(v=":",colon,digit(v))))),,1),
    REPT("■",IFERROR(HSTACK(pad(28-COLUMNS(clock)),clock),clock))
))("22:8:42")
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment