Excel

Excelでﾌｨﾙﾀｰ機能により抽出したｾﾙの一つに数式を入れ､同じ列のほかのｾﾙを範囲選択し当該数式をｺﾋﾟｰ&ﾍﾟｰｽﾄすれば､抽出されているその列のｾﾙに数式が入ると思います｡

この抽出した状態のままで数式を数値に変えたいのですが､どのようにすれば可能でしょうか｡

(抽出した状態のため形式を選択して貼付けを使用することができない｡またAlt+;で可視ｾﾙ選択→Ctrl+Rを行っても､形式を選択することができないため数式のままとなり数値に変えることができない)

一度ﾌｨﾙﾀｰ解除し数式を数値に変更後再度ﾌｨﾙﾀｰをかけて復旧する ということは実務上できません…｡

詳しい方､何卒 よろしくお願い申し上げます｡

No.10 回答者： goomania 回答日時： 2021/10/31 03:37

No.5です。

>ご回答ありがとうございます｡ただ今回フィルターを解除せずに数値化
>したいという状況がございまして…
>フィルタの条件が複雑なため解除後復旧できないおそれがあるためです｡
>マクロの方向で進めてみます｡
とのことで、マクロで解決する方針のようです。
No.9さんのマクロを個人用マクロブックに保存してショートカットキーを登録すれば、数値化するセルを選択し、ショートカットキーを押すだけで完了すると思います。とはいうものの、
>当方マクロ未経験者ですが･･･
とおっしゃっているので、方針に反しますがマクロを使用しない方法を記しておきます。
当方の環境EXCEL365で検証した方法ですが、ご質問者の環境でうまくいくか保証できません。必ずバックアップをとった上でお試しください。
オリジナルのシートはフィルタをかけたまま解除しない前提です。手順は以下のとおりです。
(1)No.5の回答で示した添付画像①のようなデータがsheet1にあり、添付画像②のようなフィルタがかかっている状態だとします。
(2)上記(1)のシートを「シートの移動またはコビー」を使って複製します。これをとsheet2とします。
(3)No.5の回答で示した添付画像②のように、複製したsheet2に、ご質問者のいう「数式」を記述します。例ではC列に記述します。これを数値にしてsheet1に反映させるのが目的です。
(4)今回回答の添付画像(以下「今回画像」という)①のようにsheet2の未使用の列を使ってに可視データ行全てに、オートフィル等で「1」を入力します。例ではE列を使っています。
(5)今回画像②のようにsheet2のフィルタを解除し、(オリジナルであるsheet1はフィルタ解除してはいけません)「数式」を入力した列を全てコビーし未使用列へ値コピーします。例ではC列をG列へコピーしています。
(6)今回画像③フィルタ機能を使ってE列でフィルタをかけて「空白セル」(つまり「1」以外)の行を抽出します。
(7)フィルタがかかった状態でC列の可視セルを「ALT+;」で選択し(今回画像③)、コピー先をCTRLキーを押しながら選択して、「CTRL+R」でコピーします(今回画像④)。これにより(3)で数式を記述したセル以外を元に戻します。
(8)再度フィルタを解除し、(4)で「1」を入力した列(例ではE列)で最初に「1」が登場する行のG列のセルを探します。そのセル以下の全てのG列のデータをCTRL+Cでコビーします(今回画像⑤)。
(9)フィルタがかったままのsheet1の数式記述列(例ではC列)の可視セルの先頭行データのセル(フィルタ行直下のセル)を選択し(今回画像⑥)、CTRL+Vで貼付けします(今回画像⑦)。
これで、フィルタをかたまま数式を数値化できるはずです。ご質問者の環境ではフィルタ解除不能につき確認困難ですが、sheet1のフィルタを解除して数式を表示させてみると必要な部分のみ数値化されていることが確認できます(今回画像⑧)。
なお、(7)で右方向へのコビーを行っていますので、右方向コビーで「数式がエラーとなる」ような場合、この方法は使えません。
「数式がエラーとなる」というのは、単に「エラー表示になる」という意味ではなく、「数式バーに#REF!が表示されている」など「数式そのものがエラー」という意味です。

No.9 回答者： ママチャリ 回答日時： 2021/10/30 20:54

No.2です。

ずいぶん難しそうなマクロを出してきましたね！！
数式を値に変えたいだけでしょ！！値張り付けにこだわる必要もないですよね？
個人的には、こんなので十分だと思うのですが、いかがでしょう。

Sub sample()
With Selection
.Value = .Value
End With
End Sub

この回答へのお礼

ありがとうございます｡
ご教示いただいた構文をもとに､結果的に下記構文を利用することになりました(ﾌｨﾙﾀｰで抽出した行に対しても有効に機能させるために下記のようにする必要があるとのことです(私は理解できておりませんが､))

Sub 値に変える()

For Each c In Selection.SpecialCells(xlCellTypeVisible)
c.Value = c.Value
Next c
End Sub

お礼日時：2021/11/04 19:14

No.8 回答者： chonami 回答日時： 2021/10/30 16:34

CSVにしたらフィルタは解除されますが、元のシートへの貼り付けはフィルタがかかったままできますよね？

フィルタリングの条件が複雑なら、ユーザー設定のビューを使うとかフィルタリングした状態に作業列でフラグをたてておくとか、先に書いておいてもらえると方法は色々提示できるんですけどね。
まぁ、方法が決まったならそちらでどうぞ。

この回答へのお礼

なるほど…参考にいたします｡
ありがとうございます｡

お礼日時：2021/10/30 16:53

No.7 回答者： chonami 回答日時： 2021/10/26 13:00

質問者側からの反応が全然ないのですが、できればフィルタ解除してコピペができない理由も書いておいていただけるといいですね。

物理的にフィルタが解除できないのか、フィルタの条件が複雑で解除したら再度フィルタリングできる自信がないとか、いじったら怒られるとか、それによって回答側の対応も変わってくるかも知れませんし。

まぁ、でも実際のところマクロが一番簡単なのかな。

この回答へのお礼

ご回答ありがとうございます｡
ﾌｨﾙﾀの条件が複雑なため解除後復旧できないおそれがあるためです｡ﾏｸﾛの方向で進めてみます｡

お礼日時：2021/10/30 15:34

No.6 回答者： chonami 回答日時： 2021/10/26 12:51

ちなみに№4の回答は「（そのブックのままでは）オートフィルタが解除できない」という前提で考えています。

No.5 回答者： goomania 回答日時： 2021/10/26 03:14

ご質問者の作業されているEXCELブックで以下のような作業が可能なのか、また、以下の手順がご質問者のご希望に沿ったものなのか判りませんが、一応できる手段を考えてみました。

添付画像をご覧ください。
(1)オートフィルターの対象となる添付画像①のような表があったとします。C列には「B列×2」という数式が入っています。
(2)画像の例ではA列を「3」に絞り込むフィルターをかけ、抽出されたC4に「B列×3」という数式を上書きし、C列の可視セルにコピーしています。(添付画像②)
(3)一旦、オートフィルターを解除し、C列をコピーし、使用していない列(画像ではG列)に値で貼付けします。(添付画像③)
(4)再度オーフィルターで上記(2)と同様にフィルターをかけ、見出し以外のG列の可視セルを「ALT+;」で選択し、コピー先のC列の可視セルも同様にをCTRLキーを押しながら選択します。その状態で、ALTを押したまま、「HFIL」の順にキーを押すと、選択されたG列の可視セルがC列にコピーされます。(添付画像④)
(5)(4)の結果、意図どおりにコピーされたか、オートフィルターを解除し、検証したものが添付画像⑤です。フィルターがかかった状態のまま、抽出されたセルのみ、飛び飛びにコピーされていることが確認できます。これで対象セルが数値化できました。
あとは作業に使用したG列の内容をデリートすれば終了です。
冒頭にも述べましたが、ご希望に沿うのか、作業可能かが不明なので、まずは、原本をコビーしてこの手順をお試しください。

この回答へのお礼

ご回答ありがとうございます｡ただ今回ﾌｨﾙﾀｰを解除せずに数値化したいという状況がございまして…

お礼日時：2021/10/30 15:51

No.4 回答者： chonami 回答日時： 2021/10/25 12:54

とりあえず、考えてみました。

もしシート全部が値になっていいなら、そのシートを一番左に配置して、該当ブックをいったんCSVで保存。
そのCSVを開いてシート全部をコピーし、元のエクセルシートに値で貼り付け（書式を変更しないために。）

これなら、値になりますけど。

この回答へのお礼

ありがとうございます｡
確かに値になりましたがﾌｨﾙﾀｰは解除されてしまうのですね…

お礼日時：2021/10/30 15:32

No.3 回答者： fujillin 回答日時： 2021/10/24 23:00

こんばんは

機能にないことを好きなやりで行いたければ、マクロを作成すれば、ある程度までは実現できるでしょう。
マクロを作らずにやるのなら、機能的に許されている範囲で行うしかありません。

＞抽出した状態のため形式を選択して貼付けを使用することができない
必ずしもそうではありません。
抽出状態でも、連続したセル範囲であれば「値のペースト」は可能です。
ですので、範囲を分割して行うことで、ご希望の操作は可能かと。

ショートカットを利用して操作すれば、比較的速く終わるとは思いますが、抽出の状態によっては、フィルターを解除した方が速いかもしれません。

この回答へのお礼

ｲﾝﾀｰﾈｯﾄ上で下記構文を取得しPERSONAL.XLSBの標準ﾓｼﾞｭｰﾙに保存､数式をｸﾘｯﾌﾟﾎﾞｰﾄﾞに
ｺﾋﾟｰした状態でﾏｸﾛ実行しましたが､値の貼付けを行うことができません(ｴﾗｰであることを
示す音が鳴りました｡ﾍﾟｰｽﾄしようとしたｾﾙには､何も貼り付けられませんでした)｡

なお実行方法については､[開発ﾀﾌﾞのﾏｸﾛ]→[ValuePasteを選択]→[実行] の手順で行いました｡



Sub ValuePaste()
Dim vClip As Variant
On Error Resume Next
vClip = Application.ClipboardFormats
If vClip(1) = True Then
Beep
Exit Sub
End If
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub


当方ﾏｸﾛ未経験者ですが下記を加工する ないしは別の何らかの構文を利用することで､
値の貼付けを行うことができるようになりたいのですが､何か良い方法はないでしょうか､?

お礼日時：2021/10/30 15:57

No.2 回答者： ママチャリ 回答日時： 2021/10/24 21:42

一般機能で出来ない場合は、マクロを組みましょう！！

選択されているセルに対して、.Value=.Valueだけで出来ちゃうと思いますよ。

この回答へのお礼

ｲﾝﾀｰﾈｯﾄ上で下記構文を取得しPERSONAL.XLSBの標準ﾓｼﾞｭｰﾙに保存､数式をｸﾘｯﾌﾟﾎﾞｰﾄﾞに
ｺﾋﾟｰした状態でﾏｸﾛ実行しましたが､値の貼付けを行うことができません(ｴﾗｰであることを
示す音が鳴りました｡ﾍﾟｰｽﾄしようとしたｾﾙには､何も貼り付けられませんでした)｡

なお実行方法については､[開発ﾀﾌﾞのﾏｸﾛ]→[ValuePasteを選択]→[実行] の手順で行いました｡



Sub ValuePaste()
Dim vClip As Variant
On Error Resume Next
vClip = Application.ClipboardFormats
If vClip(1) = True Then
Beep
Exit Sub
End If
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub


当方ﾏｸﾛ未経験者ですが下記を加工する ないしは別の何らかの構文を利用することで､
値の貼付けを行うことができるようになりたいのですが､何か良い方法はないでしょうか､?

お礼日時：2021/10/30 15:57

No.1 回答者： chonami 回答日時： 2021/10/24 20:09

>一度ﾌｨﾙﾀｰ解除し数式を数値に変更後再度ﾌｨﾙﾀｰをかけて復旧する ということは実務上できません…｡

でも、一般機能ではそれしか方法ないと思いますよ。

この回答へのお礼

ｲﾝﾀｰﾈｯﾄ上で下記構文を取得しPERSONAL.XLSBの標準ﾓｼﾞｭｰﾙに保存､数式をｸﾘｯﾌﾟﾎﾞｰﾄﾞに
ｺﾋﾟｰした状態でﾏｸﾛ実行しましたが､値の貼付けを行うことができません(ｴﾗｰであることを
示す音が鳴りました｡ﾍﾟｰｽﾄしようとしたｾﾙには､何も貼り付けられませんでした)｡

なお実行方法については､[開発ﾀﾌﾞのﾏｸﾛ]→[ValuePasteを選択]→[実行] の手順で行いました｡



Sub ValuePaste()
Dim vClip As Variant
On Error Resume Next
vClip = Application.ClipboardFormats
If vClip(1) = True Then
Beep
Exit Sub
End If
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub


当方ﾏｸﾛ未経験者ですが下記を加工する ないしは別の何らかの構文を利用することで､
値の貼付けを行うことができるようになりたいのですが､何か良い方法はないでしょうか､?

お礼日時：2021/10/30 15:56