Excel総合相談所 158

【1 OSの種類 　 　 　 　 .】 Windows**
【2 Excelのバージョン 　 】 Excel**
【3 VBAが使えるか　 　 .】 はい･いいえ
【4 VBAでの回答の可否】 可･否

注意事項
・情報を隠すために別の問題を設定するのはやめましょう。たいていの場合その問題は的外れな設定で、期待していたものからずれた回答が返ってきます。

スレッド作成は>>980がやります

※前スレ
Excel総合相談所 157
https://mevius.5ch.net/test/read.cgi/bsoft/1728342726/

>>384
処理速度なんて誤差
それより解読やメンテやバグ対策で失われる時間のが何億倍も大きい

>>385
誤差では済まされない

>>386
時間無駄にしてそう

>>387
計算終わるまでボケーと待ってるの?

あ、通じない人か

もしかして無職？

いきなり自己紹介はやめろ

【1 OSの種類 　 　 　 　 .】 Windows10pro
【2 Excelのバージョン 　 】 Excel2019　端末に紐づいているOffice Personalのもの
【3 VBAが使えるか　 　 .】 いいえ
【4 VBAでの回答の可否】 否

業務用オフライン環境での質問です
郵便の発送記録簿を毎日入力しています
複数の部署から毎日手書きの封筒で大概数通～まれに数十通の発送があり、自分は代表部局で発送前に送り先住所と宛名の記録を取っている感じです

シート1のA列が連番の内部管理番号、B列が自分の部署での記録日時（時刻は分まで）、C列が普通郵便～レターパックまでの発送形態です
D列が発送元部署で、E列に送り先住所と宛名が出ます

現状、E列が完全に手打ちで時間を食っています
発送先は毎日テンプレのように送る相手・たまに送る相手・初めて送る相手がランダムに混在しています
とりあえず作業日前日までの発送記録から（できればD列の記述を元にして）、過去に送ったことがある相手については、入力候補を可変でリスト形式で表示できるようにしたいです

EXCELが2019なのでsortやfilterは使えません
ネットで検索したら2021以降の仕様を前提にしたものばかり出てきます
2019でも使える方法の解説はどこかにないでしょうか？

同ブックの別シートは参照先のリスト元として使用可ですが、細かいメンテは自信がありません
テーブル機能は、解説を読めば一応設定はできると思います

>>392
追記

E列を郵便番号と住所に分割しようとしたら上司からNG出てます
この記録簿の書式を参照しているものが他に多数あるため、少なくとも今年度中は分割不可です

F列にこんな数式を書いてみるとか（F2に数式書いて下方にコピー、E列に文字を打つと過去履歴から部分一致で探す）
=IF(E2="",NA(),VLOOKUP("*"&E2&"*",E$1:E1,1,FALSE))

excel online上で最新関数を使って計算して、その結果をコピペで持ってくるって出来んかな？
かえってめんどくさいか？

どんだけ大量のデータまでコピペできるか知らんけど。

>>393
自分専用ブックで郵便番号を分割するなり自由に作業して最後にコピペ

>>394　横だが
残念ながら＃N/Aに成ってしまう　おれが何か間違ってるのかも知らんけど　E:EにするとぜんぶEの値で埋め尽くされてしまうし

>>392 で、>E列に送り先住所と宛名が出ます　とあるが、出ますとは？　その直後に手打ちとあるから誤記とは思うけど
E列の仕様次第だけど、郵便番号と住所と宛て先がぜんぶE列に入っているのか？
全角半角スペースとかの仕様は決められているのか？　現状全データは何件なのか？
部署ごとに宛て先は混在しないのか(同一宛て先にそれぞれの部署が発送することは無いのか)？
　試しに3,000件のサンプルデータ作って試行錯誤してるが、部署でフィルターしておいてから住所の入力をし始め
　適当な位置(例えば兵庫県伊丹市)で Alt+↓ を押すとオートコンプリートのリストが出てきて、すでに入力した
　ものがあればそれを選ぶだけだし、無ければ Esc して続きを入力すればいいし
　件数次第では部署でフィルター要らんかも知れんけど
　あと、全角半角混在だの東京都をひがしきょうみやことか音訓テキトーに入力してたらリストに出て来なかったり
　何度も試してると何故かさっきは出てきたのに出て来なくなった！なケースもあったりするけど　少しだけ楽はできる

>>397
例えば、F2に書いた数式を下コピーしてくとF10はこんな感じになって
=IF(E10="",NA(),VLOOKUP("*"&E10&"*",E$1:E9,1,FALSE))
E10に打った値をE1:E9の中から部分一致で探してF10に表示するよ

失礼、ダミーデータ作った時1行目に項目名入ってたせいでした
重複データがある時、このケースだと同じ郵便番号入れると上位の一件がヒットしてしまうのが難点か
あと、相談者と同じ2019だけどF列が＃N/Aで埋め尽くされる "" にしても変わらん
ヒットするとぬるぬるぬる〜と住所が表示されるのは見てておもしろいけど(シパッ！という表示の仕方では無い)

#N/Aてそういうことか。こうしたら「-」になるからいいよねてこと？
=IFERROR(IF(E2="",NA(),VLOOKUP("*"&E2&"*",E$1:E1,1,FALSE)),"-")

E列に住所と宛名が両方入っているらしいから、宛名（の一部）を打って検索すれば楽になるんじゃないかな

a4:a13の中で
a14と同じ内容のセルの行の
B列からH列で
空白以外のセルの数を数える方法教えてください

>401
なるべくoffset関数使わない方法で

レス全文読みました>>392です
土日明けに出勤なので色々試してみようと思います

取り急ぎ
>>397
誤記ですね
「出ます」ではなく「入ります」です

>>395
オンライン環境にデータ持ち出すのはちょっと論外かと
督促状送る個人の番地やら名前やら普通に混在してるので、何かトラブった瞬間１発クビになります

>>401
=COUNTA(XLOOKUP(A14,A4:A13,B4:H13))

>>403
何が論外かはわからんけど、
excel onlineは無料でほぼ最新関数を試すことができる。
試した結果、良さげならExcel2024の購入提案しても良いと思うが、それが論外なら、化石の中で生きるしかないかもね。

質問です
A列に取引先のリストがあります
隣の列にフリガナを入れたいのです
手入力したくないです
自動でやる方法ないですか？

>>406
ポネチック関数でしゅ！（＾▽＾）

>>407
それは何ですか？

「ポネチック」は、英語の「
phonetic（フォネティック）」のカタカナ表記で、主に以下の2つの意味で使われます。

1. 音声学・発音に関する
音声の、表音の
文字や記号が、個々の音を表していること。
例：「国際音声記号は、あらゆる言語の音声を表記できる表音記号である」。

発音に基づいた
発音の通りに表記すること。
例：「ポネティックスペル（phonetic spelling）」は、発音通りの綴りを意味する。

2. コンピュータの関数名
ExcelのPHONETIC関数
漢字で入力された文字列のふりがな（読み方）を取り出すための関数。
例：セルA1に「東京経済大学」と入力されている場合、
PHONETIC関数を使うことで「とうきょうけいざいだいがく」というふりがなを取得できる。

オフライン端末のデータをネット運用必須の環境で使わせようという発想がそもそも
社会人経験が不足しているというか実務に携わったことが無いというか
浅はかにも程がある
そういう面がZの不祥事とかに繋がる素因かも知らんけど
失うものの大きさに思いが寄らないやら　そのクセ半ギレしてるし

>>410
誤爆か？

>>411

>>392ですが誤爆とは思わないです
元のレスでは簡略化のためオフライン環境と書いていますが、正確には、SVが行うウィルス対策のパターン更新等セキュリティ関係を例外として、
その他は一律ネット環境から隔離された、ローカルのイントラネット環境になります
例えば、この発送簿を参照して同じ環境内の別端末のWordのテンプレートに情報を流し込むことはあっても、イントラネットの外に発送簿の情報を持ち出すことはできません

EXCEL Onlineはこのようなイントラネット環境では動作しませんし、OneDriveの保存を前提としたソフトウェアである以上導入すべきでもありません

強いて言えば別にネット環境の端末はありますが、そこでEXCEL Onlineを使うとすれば、イントラネットからUSBメモリなりで発送簿のデータを持ち出すことになりますよね
例えそうした目的であったとしても、機微に関する情報をイントラネット外に出すという扱いになるので、たぶん試してみただけで即キツい懲戒沙汰になる話です
他所は分かりませんが、すくなくとも自分の勤務先はそういう感覚だと思います

そういう意味でEXCEL Onlineは論外なのです
以後、このソフトに関する話題は自分はここまでとさせて頂きます。深掘りされても自分は今後レスはしないです
あくまでオフライン前提のEXCEL2019の話ということでお願いします

自分はどうだって良いのですが、
EXCEL2024はローカル環境で動くEXCELです。
Excel onlineは、最新の関数を使えば何ができるかテストするのに良いのでは？
Excel onlineでテストする時は実データを用いる必要はありません。テスト用データで良いのです。
実際に何ができるかの提示ができないと、提案もできないでしょう？
古いExcelが必須というなら、それで頑張ってください。

>>413
負け惜しみか？

郵便番号と住所を分割しようという提案すら待ったを掛けられる規模の組織だって気付きなさいよ
ちっさかったりトップがどんどん最新の設備や機能を取り込むのに積極的ならお薦めするのもやぶさかじゃ無いけど
それなりの規模だったり厳格・厳密な組織運営に注力してるようなところだと、そう易々と機器も変えられない
リース切れのタイミングで社内一斉にリプレースとかだって通常運転
限られた資源、限られた環境でフルスイングし続けなきゃいけない業態なんか、ごまんと存在してる
いい社会勉強したと思って、再び半ギレしたようなレス付けるのは控えなさいよ

上司に提案するにしてもスマホで写真撮ってテキスト自動認識とかがいいんじゃねーの？

万年カレンダー作る時に当月の最初の日曜に日付入れる関数が理解できなくてかなり困った

2025年6月1日をセルのA1に書いたとして
A1+1で翌日の6月2日からweekday(A1）を引くとその数値の分だけ下がった日付が日曜日に出るわけだ
う～ん…考えたことないから未だにモヤモヤしてるわ

Excelのテンプレートに万年カレンダーがあるから部分介して理解したよ(´・ω・｀)

分解(´・ω・｀)部分介どうしてこうなった

毎月のカレンダーなら
エクセルのはサイズとか配置とかミバエの融通きかないから
計算だけしてワードに貼ってた

そのカレンダー使ってんの？

>>407
これ使ってもエラー出るんだけど

万年カレンダーは万年使われない

>>421
>420 だけど
A1の紙に印刷して使ってた
会社で

カレンダー作るなら、これも取り込んどき。

内閣府　「国民の祝日」について
www8.cao.go.jp/chosei/shukujitsu/gaiyou.html
の「昭和30年（1955年）から令和8年（2026年）国民の祝日（csv形式：20KB）」

春分の日、秋分の日は天文台が決めるし、振替休日とか昔よりややこしくなったので
2027年分は来年追加される。

>>424
年収低そう

>>426
低くはないよ

カレンダー関連のデータが必要な時はネットのカレンダーサイトをそのままコピーしてる
振り替えとか国民の休日の判定とかいちいち自分でロジックを組まなくていいから楽だし確実
祝日が変わっても気にする必要ない

会社のカレンダーだから休日も会社ルール

>>425
取り込んどきました

=LET(
対象年,2026,
URL,"https://www8.cao.go.jp/chosei/shukujitsu/gaiyou.html";,
a,WEBSERVICE(URL),
b,SUBSTITUTE(a,UNICHAR(13)&UNICHAR(10),""),
c,TEXTBEFORE(TEXTAFTER(TEXTAFTER(b,"（"&対象年&"年）の国民の祝日・休日"),"<tbody><tr>"),"</tr></tbody>"),
d,TEXTSPLIT(c,,"</tr><tr>"),
e,TEXTBEFORE(TEXTAFTER(d,"<th scope=""row"">"),"</td>"),
名称,TEXTBEFORE(e,"</th>"),
日付,対象年&"年"&TEXTAFTER(e,"<td>"),
HSTACK(名称,日付)
)

>>425
あたしゃこれをPowerQueryで取り込んで
12/29～1/3は「年末年始」とかの名目と
休日扱いのフラグ追加した基本データブックとして
いろんなブックで使い回している。
翌年の祝日が確定したら、クエリー更新のみ

>>416 を誰か翻訳してくれ　皆目見当も付かん

>>425
祝日休みの会社いいなあ。。

Office 2016 と Office 2019 のサポートは 2025 年 10 月 14 日に終了し、 拡張機能はなく、セキュリティ更新プログラムも延長されません。 Office 2016 アプリと Office 2019 アプリはすべて引き続き機能します。 ただし、深刻な問題を引き起こす可能性のあるセキュリティ リスクにさらされる可能性があります。

知らんかったわ。

>>432
iPhone標準でテキスト認識機能あるよ

>>392です
色々考えましたが、作業時に作業用の別シートで作業して、作ったデータをコピペして貼り付けるのがベストな気がしてきました
自席のローカルで別excelで効率化のために数式をいじくり倒すなら誰も何も文句は言わないでしょうし

やりたいことについて必須の作業ではないのですが、気になったので最後の質問です
特定のセルの文字について、一文字目から最後の文字まで、一律に隣の列に文字コード形式に置換する方法はないでしょうか？
ローカルでの抽出リストの作成時に、small関数で順位付けを出して、重複の排除と（不完全にせよ）規則性のある並びにすることを考えているのですが、
smallは数に対して作用する数式なので、一度住所列を文字コードに変換しないといけない気がしています

質問です
A列に取引先のリストがあります
隣の列にフリガナを入れたいのです
手入力したくないです
自動でやる方法ないですか？

>>437
=PHONETIC(A2)

>>438
それやるとエラー出るからそれ以外で

>>427
証拠は

>>439
2007とかの古いバージョンでも動作するんだが、何か違ってやしないか？
https://azby.fmworld.net/usage/excel-function/019/　これ見て真似してどうなるか？
サンプルファイルをDLしてテストすることもできるし

>>438
これだとコピペしたデータには使えないよ

>>436
順位ならこれで出るんじゃない？
=COUNTIF(A$2:A$100,"<"&A2)+1
（※A2〜A100までデータが入ってる前提）

✅ 方法③：ふりがな情報がない場合 → 変換する方法

もし PHONETIC 関数で空欄になった場合でも、次のような方法があります：

（A）関数で変換（簡易）

カタカナに変換するなら：

=PHONETIC(A2)

が効かないときは、Googleスプレッドシートの GOOGLETRANSLATE や Power Automate のフローで変換できます。

（B）Excel VBAを使う（完全自動）

WindowsのIMEを利用してフリガナを自動で取得するVBAを組むと、完全に自動でB列にフリガナを生成できます。

✅ 方法④：Microsoft 365 Copilot／関数の新機能を使う（M365ユーザー限定）

Microsoft 365（Copilot付き）の場合、**「音声読み仮名をAIで生成」**できます。
例：

=TEXT(PHONETIC(A2))

または Copilot に「A列の取引先名にフリガナ列を追加して」とお願いすれば一括変換可能です。

いまだにExcel 97-2003とかでやり取りをしてますが、大丈夫でしょうか？

なんかあったら会社が半壊するね。
アサヒみたいに体力がある会社ならまだしも。

>>442
標準モジュールで関数作ってワークシートから呼び出したらできた

Sheet1:
=COPIPEPHONETIC(A1)

標準モジュール:
Function COPIPEPHONETIC(R As Range) As String
R.SetPhonetic
COPIPEPHONETIC = WorksheetFunction.Phonetic(R)
End Function

関数もっと短くてよかった

Function COPIPEPHONETIC(R As Range) As String
COPIPEPHONETIC = Application.GetPhonetic(R)
End Function

VBA、PowerQuery、Power Pivot（DAX）は専用スレあるのに
LAMBDAやLETにはないんだな
数式の延長線上にあるからかな
（複雑になりすぎて「数式とは…？」になりがちだけど）

PowerQuery、Power Pivot（DAX）の建てられた日見てみ？
両方とも最初のスレ
・・・つまり、そゆこと。

何だったら、あんさんが初スレ建ててみなはれ。

需要がないことはよく分かった
つか今は5chなんかよりAIに聞いた方が早いしな
便利だが寂しい時代になったもんだ

>>447
なにこの車輪の再発明
クソ

5chはスレとその住人次第だが、ノイズが多すぎるから・・・

チャッピーより質の低い回答とかもう

AIは「なにこの車輪の再発明 クソ」などとこき下ろしてこないもんナ
しかもスピーディ回答

AIはここも模索して勉強してんのかな？

>>455
悔しかったの？

>>457
最初の発明どこにもなくね？

どうでもいい
誰も気にしてない

>>448
これのこと？

A列に日付が入ってて
別の列で前日と同じかのフラグを作って
書式設定で前日と異なれば違う色にしている
この方法がとフィルタかけると異なる日が並んでても同じ色になってしまう
どのようにしたらいい？

>>461
オレのレスのコピペだ

もうコピペに引っかかる人いなくなった

オリジナルで問題出してほしい
みんなで解くからさ(´・ω・｀)

雑談だが、Win10サポ終了はいいんだが、ユーザーアイコンの右肩に赤丸の通知マークが勝手に付いて
日に日に色濃く成って行きやがって、あまりにもムカ付いたからつべに上がってる裏ワザとか使って11に上げてやった
メモリ4GBストレージ64GBとかいうWin10タブだから諦めてたけど、やってみるもんだ　実務機にはお勧めしない

>>462
自分でコピペすんなよ

>>465
ヒマそう

textsplit関数が使えないのでsubstitute関数で仕切り文字を使ってない文字にすり替えて抽出していますが
何番目の値とか数値指定する時に文字列中の最初の値と最後の値は別の処理をするのがめんどくさいので
同じ式でやる式は良い方法はありますか？

>>468
=IF(ISODD(SEQUENCE(100)),A1,D1)

コピペ

>>469
あります

>>469
最初の値と最後の値を使わなければよい

あーはいはい

365版のショートカットアイコンが変なデザインに
変わって見づらいのだが、元に戻せないのか

初代がズバ抜けて見やすいな
https://asset.watch.impress.co.jp/img/wf/docs/2052/042/image2.png

はよ仕事探せや爺

働いたら負けの稼業だし

爺ならとっくにFIREしてるだろ
Excelで資産運用管理して

クビになったの

収支を付けてたけど、プラスが積み上がるだけだから面白くなくなった
休み無しで働き詰めだった甲斐があった
Financial Independence, Retire Early　？
Financial Independence は誰だってそうだろ？　自立してない社会人なんか居るのか？

a列に人名
b列に入社年月日
c列に退職年月日
が入ってて
縦軸人数
横軸年月日のグラフを書きたい
作業列とかなしでできたっけ?

>>476
エモい

>>482
> 縦軸人数
> 横軸年月日のグラフ
日単位で在籍人数の推移を作りたいのけ？
月末基準の月単位でも良さそうだけれど・・・

普通に考えたら、
・全期間を網羅するカレンダー表X（グラフ横軸用）を作る
・abc列の表Yを表Xと繋げる（a列と入社日<=在籍中なら「1」フラグ列<=退社日でリンクとか）
みたいなことを、カレンダーもa列も可変なのでPower Queryあたり使ってやりそうだけれど。