幽霊続きのお話・・・
幽霊続きの話です。
幽霊セルというものがあることを偶然知ってしまいました。
幽霊セルは、セルに画面上表示されない値が入力されているのが原因で以下の3つが挙げられていました。
１．スペースのみのセル
２．空文字列のセル
３．シングルクォーテーションのみのセル
この中で項番２の空文字列のセルは過去に自分も生成してたかもというやつでした。
次のようなIF関数などを使った数式で空文字列を設定している場合に生じるという話だった。
=IF(A1="", "", A1*B1)
この数式では、入力のない行の計算結果に0を表示させないよう、A1セルが空のときにあえて空文字列（""）を設定しています。
このように数式で空文字列を設定したセルをコピーし、別のセルに「値のみ貼り付け」をすると、その空文字列（長さ0の文字列）を値として保持するセルができてしまうという仰天するような話だった。これはこれで何も悪さしないのなら問題ないんだけど・・・
＜悪さの例＞
１．COUNTA関数が実際より多い数になる
２．空白なのにISBLANK関数がFALSEを返す
３．Ctrl+矢印のジャンプで何もないと所で止まる
結局、 目には見えないのに確かにEXCELは何かいると答える、まるで幽霊のようなセルと言えます。
時に、数式や集計を狂わせ、CSVや印刷で大量の空行を吐き出すなどの損害を被ることになります。
そんな幽霊セルを解決するブログに巡り合いました。
2020-02-24 空白なのに空白じゃないセルを空白にしたい
シートの空文字列を除去
空白なのに空白じゃないセルを空白にしたい - シーゴの Excel 研究室
上記のサイトのコード解析をする過程でこれまで、使ったことのなかったコードスニペットが以下になります。
■空白を網掛けにする
Range("C11").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
■PCの画面全体を撮る
Public Sub 全体を撮る()
keybd_event vbKeySnapshot, 0&, &H1, 0&
keybd_event vbKeySnapshot, 0&, &H1 Or &H2, 0&
End Sub■アクティブウィンドウを撮る
Public Sub アクティブ画面を撮る()
keybd_event &HA4, 0&, &H1, 0& ' &HA4がAltキー、&H1キーを押す
keybd_event vbKeySnapshot, 0&, &H1, 0& ' vbKeySnapshotがPrtScキー番号は&H2Cらしい
keybd_event vbKeySnapshot, 0&, &H1 Or &H2, 0& 'PrtScキー &H2でキーを放す
keybd_event &HA4, 0&, &H1 Or &H2, 0& ' Atlキーを放す
End Sub■スペースのみの文字列を削除となっているが、結果的に以下の3つすべてを除去することに成功した。
（上記ブログの改変を行いシングルクォーテーションのみのセルのみ除去するようにした）
１．スペースのみのセル
２．空文字列のセル
３．シングルクォーテーションのみのセル
Sub テキスト置換_スペースのみの文字列を削除()
Dim QMSG As String
Dim RC
QMSG = "「テキスト置換_スペースのみの文字列を削除」はシート上の"
QMSG = QMSG & vbLf & "「スペースのみ」を一括除去します。"
QMSG = QMSG & vbLf & "結果的に「'」のみのセル、空文字も除去します。"
RC = MsgBox(QMSG, vbYesNo, "■シートの「スペースのみ」を除去")
If RC = vbYes Then
Else
Exit Sub
End If
'Dim WS0 As Worksheet
'Set WS0 = ActiveSheet
Dim WS1 As Worksheet
Set WS1 = Sheets("幽霊セル")
WS1.Activate
If TypeName(Selection) <> "Range" Then Beep: Exit Sub
Dim rng As Range
Set rng = Intersect(Selection, ActiveSheet.UsedRange)
If rng Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Call rangeTextReplaceAll(rng, "^( | )+$", "")
Application.ScreenUpdating = True
Range("C11").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
MsgBox "「スペースのみの文字列を削除」が終了しました！"
End Sub
Private Sub rangeTextReplaceAll(rng As Range, rePattern As String, reReplace As String)
Dim re As Object
Set re = CreateObject("VBScript.RegExp")
re.Global = True
re.Pattern = rePattern
Dim col As Range
For Each col In rng.Columns
Dim vals As Variant
Dim preChar As Variant
ReDim preChar(1 To col.Cells.Count, 1 To 1)
Dim i As Long
For i = 1 To col.Cells.Count
If col.Cells(i, 1) <> "" Then
preChar(i, 1) = col.Cells(i, 1).PrefixCharacter
End If
Next
If col.Cells.Count > 1 Then
vals = col.Formula
Else
vals = col.Resize(, 2).Formula
End If
For i = 1 To col.rows.Count
If Left(vals(i, 1), 1) <> "=" Then
vals(i, 1) = re.Replace(vals(i, 1), reReplace)
End If
Next
col.Formula = vals
For i = 1 To col.Cells.Count
If preChar(i, 1) <> "" Then
col.Cells(i, 1).Value = preChar(i, 1) & vals(i, 1)
End If
Next
Next
End Sub■シートにごみが残り、EXCELのサイズが膨れ上がっている場合には
以下のサブルーチンが使えそう。
選択範囲外の作成にこんなテニクニックを使うとは何か勉強になった気がした。
冷静に考えるとわざわざこんなテク使わなくても単純に選択範囲外の作成は
可能な気がしている。次回紹介することにする。
Sub セルのクリア_選択範囲以外をクリア()
Dim QMSG As String
Dim RC
QMSG = "「セルのクリア_選択範囲以外をクリア」はシート上の"
QMSG = QMSG & vbLf & "「選択範囲以外」を一括除去します。"
RC = MsgBox(QMSG, vbYesNo, "■シートの「選択範囲以外」を除去")
If RC = vbYes Then
Else
Exit Sub
End If
Dim WS1 As Worksheet
Set WS1 = Sheets("幽霊セル")
WS1.Activate
If TypeName(Selection) <> "Range" Then Beep: Exit Sub
If Selection.Cells.CountLarge = 1 Then Beep: Exit Sub
Application.ScreenUpdating = False
Dim diff As Range
Set diff = rangeDiff(ActiveSheet.UsedRange, Selection)
If Not diff Is Nothing Then
diff.Clear
End If
Application.ScreenUpdating = True
End Sub
Private Function rangeDiff(rng1 As Range, rng2 As Range) As Range
Dim tmp As Range
Set tmp = Intersect(rng1, rng2)
If tmp Is Nothing Then Set rangeDiff = rng1: Exit Function
If tmp.Address = rng1.Address Then Set rangeDiff = Nothing: Exit Function
With ActiveWorkbook.Worksheets.Add
.Range(rng1.Address).Value = 1
.Range(rng2.Address).Clear
On Error Resume Next
Set rangeDiff = rng1.Worksheet.Range( _
.Range(rng1.Address).SpecialCells(xlCellTypeConstants).Address)
On Error GoTo 0
Application.DisplayAlerts = False
.Delete
Application.DisplayAlerts = True
End With
End Function
最強英語フレーズ５５０－P48
■もっと詳しく説明していただけますか？ （Could you explain that in more detail?)
Ａ：もっと詳しく説明していただけますか？ ｜ Could you explain that in more detail?
Ｂ：もちろん。チームのサポートなくしてクローズできませんでした。
｜ Certainly. I couldn't do this without
最強英語フレーズ５５０－P42
■おはようございます。調子はどうですか？ ｜ Good morning! How's your life treating you?
Ａ：おはようございます。調子はどうですか？｜ A: Good morning! How's your life treating you?
Ｂ：おはようございます。調子はいいですよ。｜ B: Good morning. (It's treating me) Very well.
Ａ：確かにいつも調子よさそうですね。 ｜ A: Yes, you always look so great.
Ｂ：ありがとうございます。 ｜ B: Thanks for your kind words.
Ｂ：あなたもいつも調子がよさそうですよ。 ｜ B: You look happy too.
Ａ：あなたの言う通りですね。 ｜ A: You're right.
■ほかにも使える表現
Ａ：こんにちは！ 調子はどう？ ｜ A: Hi! How's it going?
Ａ：おはよう！ 調子はどう？ ｜ A: Good morning! How's everything?
Ａ：こんにちは！調子はどうですか？ ｜ A: Hello! How are you doing?
Ａ：おはようございます！ ｜ A: Good morning!
：最近調子はどうですか？ ｜ How's life treating you?
Ａ：こんにちは！ ｜ A: Hello!
：ここ最近調子はどうですか？ ｜ How's your life been treating you?
■仕事の調子はいかがですか？ ｜ How's your work going?
Ａ：仕事の調子はいかがですか？ ｜ A: How's your work going?
Ｂ：幸い、先週大きな案件をクローズしたところでした。 ｜ B: Thankfully, I just closed a big deal last week.
Ａ：おめでとうございます。どんな案件だったのですか？ ｜ A: Congratulations! What was it?
Ｂ：私にとって今までで一番大きな案件の一つだったのです。｜ B: This was one of the biggest deals of my career.
Ａ：それはすごいです。とても素晴らしいですね。 ｜ A: That's great. Sounds fantastic!
■ほかにも使える表現
Ａ：仕事の調子はどう？ ｜ A: How is your work?
Ａ：仕事の調子はどうですか？ ｜ A: How are things with work?
Ａ：仕事の具合はどうですか？ ｜ A: How is your work getting along?
Ａ：あなたの今の仕事はどうなってるの？ ｜ A: How's your business going?
Ａ：職場での仕事の調子はどうですか？ ｜ A: How are things going at your company?
■少しお時間ありますか？ ｜ Do you have a minute?
Ａ：少しお時間ありますか？ ｜ A: How's your work going?
Ｂ：大丈夫ですよ。ただ10分後に会議があります。 ｜ B: Of course. But I have a meeting 10 minutes later.
Ａ：分かりました。 ｜ A: No problem.
：クローズされた案件について簡単に教えてもらえますか。｜ : So, let me quickly ask you about your deal.
Ｂ：はい。クローズに１年以上かかりました。 ｜ B: Sure. I spent over 1.5 years and finally closed it.
Ａ：すごいですね！ ｜ A: Wow. That's amazing!
■ほかにも使える表現
Ａ：ちょっといいですか？ ｜ A: Do you have a moment?
Ａ：ちょっと空いていますか？ ｜ A: Are you free right now?
Ａ：少し話をすることができますか？ ｜ A: Are you available to talk?
Ａ：簡単にちょっと話せますか？ ｜ A: Can I have a quick chat?
Ａ：今お話しできますか？ ｜ A: Can we talk now?
■英文記録35
■メモ MEMO
■皆さん、こんにちは。Hello everyone,
■今朝の全体会議で、新入社員のダナ・レドモンドさんを含む皆さん全員にお会いできてうれしかったです。
I was pleased to see you all at the general meeting this morning, including our newest employee, Dana Redmond.
■彼女とはメールを通じて連絡をしていましたが、ついに顔と名前を一致させることができたのは良いことでした。
I've been talking to her through e-mail, but it's nice to finally put a face to the name.
■これはこの会議で話し合った項目のいくつかをざっと振り返り、重要な1，2点についてリマインドするための短いメッセージです。（リマインド＝思い出す）
This is just a short message to run through some of the things we discussed at the meeting, and to remind you of one or two important points.
■まず最初はお分かりの通りこの四半期は変化でいっぱいになるでしょう。
First up, as you can see, this quarter is going to be jam-packed with changes.
■開発スタッフは次の製品の開発に早速取り組んでいます。
（開発スタッフは、次の製品に向けて全力で取り組んでいます。)
The development staff have hit the ground running with our next product.
■製品の仕組みを皆さんにご理解いただくため、開発チームは詳細を説明するファイルをまとめました。
To make sure that everyone understands the way it works, the team has put together a file explaining the details.
■これを至急ダウンロードしてください。
Everyone should download this as soon as possible.
■私たちはこの企画のリリース日が近づくにあたり、確実に同じ認識を持つようにしなければなりません。
We have to make sure that we are all on the same page, as this project gets closer to a release date.
■第二に、この迅速な開発は、経営陣が行動を起こしているということだと、ハロルドは説明しました。
Second, Harold has explained that the very fast development means that management has moved things forward.
■これが意味するのは、超過勤務が必要になるだろうということです。
This means we will need people working extra hours.
■この発表は会議ではあまり受けがよくありませんでした。
This announcement didn't go down well at the meeting.
■しかし、誰かが戦う構えをとる前に申しますと、私の出した概算では、必要な超過勤務時間はそれほど多くありません。
But before anyone gets up in arms, my rough figures show that the extra hours we need won't be that many.
■もしあなたが手一杯であるなら構いません。
If you have your hands full, it should be fine.
■数時間の残業をしたい人もたくさんいますから、時間外労働を望まないなら、おそらくやらずに済むでしょう。
I know there are plenty of people who want a few extra hours, so if you don't want to do the extra work, you probably will not have to.
■そして、もう一つ解決するべき問題があります。■工場の敷地内を歩くときは、安全帽を着用しなければならないことを忘れないでください。
And one more issue to get out of the way: please remember, if you are walking through the factory area, you must wear a hard hat.
■この規則に変更はありませんが、何人かの人が帽子を持ち歩いて、規則の限界を試しています。
This rule has not changed, but a few people are testing its limits by carrying their hats.
■こういったことはやめるべきです。
This kind of things has to end.
■いつ事故が起きてもおかしくありませんし、今、私たちはどんな事故も起こすわけにはいきません。
It is an accident waiting to happen, and right now, we cannot afford to have any accidents.
■たった１、２分しかいないのに馬鹿らしいと思うかもしれませんが、これは規則です。
（1～2分程度の訪問であれば、バカバカしいと思うかもしれませんが、それがルールです。）
It might seem silly if you are only visiting for a minute or two, but that is the rule.
■今後は、もし安全帽を着用していなければーーー頭に、ですよーーー警備係に呼び止められて、かぶるように求められることになります。
（これからは、頭にハードハットをかぶっていないと、警備員に止められて、ハードハットをかぶるように言われます。）
From now on, if you are not wearing your hard hat --- on your head --- you will be stopped by security and asked to put it on.
■疑いの余地はありません、私たちはこれまでで最高の製品に取り組んでいるのです。
（これまでで最高の製品を作っていることは間違いありません。）
There's no doubt about it, we are working on our best product yet.
■そしてこのチャンスを生かすため、この段階において、全員が一丸となる必要があります。
And we need everyone working together at this stage of the process to take advantage of the opportunity.
■これらの問題について質問があれば、マネージャに話をするか、私のオフィスに立ち寄っていただいても構いません。
If you have any questions about these issues, you can talk to your manager, or even stop by my office.
■皆さん、お疲れ様。
Good work, everyone.
■①このメモはいつ書かれましたか？
a. 会議のすぐ後 b. 会議の直前 c. 歓迎会の前
When was this memo written?
a. Shortly after a meeting
b. Just before a meeting
c. Before a welcome party
■②全員がダウンロードするように言われている物は何？
a. 残業時間のスケジュール b. 次の新製品を説明するファイル c. 前四半期の利益の報告書
What is everyone asked to download?
a. A schedule for overtime hours
b. A file explaining the next new product
c. A report of the last quarter's profits
■③警備係が工場で誰かを呼び止めるかもしれないのはなぜ？
a. 従業員に正しい安全装置を身に着けるよう要請するため b. 従業員に残業を頼むため c. 従業員に新しい規則を知らせるため
Why might security stop someone in the factory?
a. To ask an employee to wear proper safety equipment
b. To ask an employee to work overtime
c. To inform an employee about the new rules
