表Aと表Bの数字を足して表Cに反映させたいです。
図のようなGoogleスプレットシートの表A、B、Cがあります。
表A(日中分)がW2:OA33、表B(深夜分)がW36:OA67、表C(合計分)がW70:OA101です。
簡単にいうとAさんの日中分の各セルと深夜分の各セルを足して1日の合計を出したいのです。
※表A、Bそれぞれ人の順番は同じではありません。(他のところから参照している為)
※表Aだけ入力されている人もいれば、表Bだけ入力されている人もいます。
※表Cには氏名は書いてありません。表だけが入力されてあります。
※セルに何も入力がなければ、0と出さずにそのまま空白のセルにしたいです。
※表Aの1日と表Bの2日を足す、表Aの2日と表Bの3日を足す、という風にしたいです。
例えばAさんの表Aの1日と、表Bの2日(色がついている所)を足して、表Cの1日に反映させるにはどのようにすれば良いのでしょうか。
ややこしくなってしまったのですが、何卒ご教授をお願い致します。
No.2ベストアンサー
- 回答日時:
こんにちは。
回答が無いようですので・・・
Googleスプレットシートとのことですが、使用したことがありませんので、MSエクセルで回答しています。
とは言え、スプレッドシートの関数を見てみると、以下の例で使用している関数は存在しているようですので、方法としては同様のことが可能であろうと考えました。
関数の機能や書式がまったく同じかどうかまでの確認はしていませんので、違いがある場合等の調整は、質問者様のほうでお願いいたします。
https://support.google.com/docs/table/25273?hl=ja
また、不明点がいくつかあるので、以下のような仮定をしています。
・ご提示の表で氏名の欄(2行目、36行目)は4セル分が結合セルとなっている。
・No1様も指摘なさっていますが、C表の体裁が不明なので、A表、B表と同じ体裁と仮定
・>表Cには氏名は書いてありません
とのことですが、誰のデータを表示するのか不明なので、ひとまずA表、B表同様に
C表にも氏名欄があるものと仮定しています。
(無い場合は、参照セル位置を変えることで対応可能です(後述))
・>表Aの1日と表Bの2日を足す
の意味ですが、例えば、ご提示の図で色付けされた「3,0,0,24」のデータと「2.1.0.25」
をそれぞれに足した「5,1.0,49」がC表の4つのセルに計算されるものと仮定
まずは、考え方の説明です。
表の位置とサイズが大きいので計算が面倒ではありますが、基本的には氏名でA表、B表の該当する欄(列)を検索して参照すべきセル位置を計算すれば良いだけですね。
日にちの方(行方向)も検索することは不可能ではありませんが、同じ順に並んでいるのでしょうから、検索する必要もなく、単純に一定行数分ずれた位置を参照すればよいものと考えました。
検索する際に用いている氏名は、以下の例ではC表に記載のある氏名を用いています。
「表Cには氏名は書いてありません」とご説明があるのは承知の上ですが、誰のデータを検索(表示)するのかわからないため、ひとまずこのようにしてあります。
例えば「B列と同じ順」とか「A列と同じ順」というのであれば、検索する氏名の参照先をC表からB表(またはA表)に変えればればよくなります。
でも、この場合には片方の表は同じ順になっているので、そちらに関しては検索する必要がありませんので(同じ列を参照すれば良いので)、式は大幅に簡単になりますね。
とりあえず、そのまま計算すると空白部分は「0」と評価されるので、「0」表示となる式を提示しておきます。
>0と出さずにそのまま空白のセルにしたいです
スプレッドシートにも同じ機能が存在するかどうかは存じませんが、0を表示しない方法を関数式とは別に、いくつか記しておきますので、ご参考までに。
さて、計算式ですが、添付図ではC表のX71セルに以下の式を入力しています
(ご説明文の通りにセル位置を設定したつもりですが、ずれていたらご容赦願います)
=INDEX(OFFSET($X$2:$AA$2,ROW(A1),MATCH(OFFSET($W$70,0,INT(COLUMN(D1)/4)*4-3),$X$2:$OA$2,0)-1),MOD(COLUMN(D1),4)+1)+INDEX(OFFSET($X$36:$AA$36,ROW(A2),MATCH(OFFSET($W$70,0,INT(COLUMN(D1)/4)*4-3),$X$36:$OA$36,0)-1),MOD(COLUMN(D1),4)+1)
前半がA表への参照、後半がB表への参照となっています。
氏名の検索には70行目のの氏名を用いていますが、必要ならこの部分を修正すれば宜しいかと。
X71セルに、式を入力したら右方、下方にフィルコピーすることで(エクセルでは)添付図のように計算されます。
※ ご説明文では不明ですが、加算するデータが1日分ずれているので、最後の日は特殊な計算になるものと想像します。
しかしながら、その内容がわかりませんので上式では対応はしていません。
上式のままだと、計算結果が0となるものは「0」として表示されますので、これを表示しない方法について。
1)MSエクセルの場合はソフトのオプション設定に「ゼロ値のセルはゼロを表示する」という項目があるので、このチェックをはずすとゼロの表示はされずに空白表示となります。
(スプレッドシートに同様の機能があるかどうかは存じません)
2)対象範囲(今回の場合はC表)のセルの表示書式を「0;-0;;@」と設定しておくことで、その範囲に関してはゼロは表示されず空白表示となります。
(スプレッドシートの場合の書式設定の仕組みが同じかどうかはわかりません)
3)関数式内で、0の場合を空白にしてしまうには、例示の式を[関数式]と表すとして
=IF([関数式]=0, "", [関数式])
のような式に置き換えることで、計算結果が0の場合は空白表示とすることができます。
ただし、見てもわかるように、式の長さが約2倍ちょっとの長さになります。
いろいろ仮定している部分が多々ありますので、考え方のご参考までに。
No.1
- 回答日時:
まず、
>※表Cには氏名は書いてありません。表だけが入力されてあります。
これの詳細が不明な感じがします。
計算結果をどのように入れるのかがわかりませんし。
・表Aの月末に相対する表Bのデータは翌月の1日(でも表にはないみたいだし)?
それとも表Aの月末-1日、表Bの月末までが範囲になるのでしょうか?
・・・と思ったらExcelじゃないんだね。(何故にこのカテに質問されてるのだろう・・・)
Google Apps Scriptは触った事ないのでお手上げです。
ただ上記は情報としては必要だと思ったので残しておきます。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) Excel2019、2021の日付、曜日の表示について 2 2022/11/29 15:01
- Excel(エクセル) エクセルの祝日に色が反映しない 4 2022/05/18 09:58
- その他(Microsoft Office) 従業員増減対応で当番種類の増減対応な当番表 21 2022/07/19 07:30
- Excel(エクセル) IF 関数で「〇〇 という文字を含む場合」の分岐処理で表示された数字はSUMで数字集計できますか? 3 2022/08/02 16:29
- Excel(エクセル) エクセルシートの合計の変動 5 2022/04/05 15:56
- Excel(エクセル) Excel シート複数 金額日計表と日付 簡単にシートコピーしたら前日の残高と日付を変更させたい 1 2022/07/15 22:10
- Excel(エクセル) WORKDAY関数 4 2023/06/08 13:23
- Excel(エクセル) マクロ/VBAについて教えてください。 10 2022/05/27 12:59
- Excel(エクセル) エクセルで”入力シート”の文字書式の変更を”出力シート”で同じ文字書式で印刷したいです。VBA希望 4 2023/04/24 11:07
- Excel(エクセル) シート参照を含む数式を連続コピー 3 2022/12/10 11:42
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【関数】スペースがいくつ入っ...
-
西暦や和暦の表示をyyyymmdd表...
-
Excelのセルを飛ばして入力する
-
Excelのオートフィル
-
別シートからの文字を変更
-
Excel 2019 のピボットテーブル...
-
エクセルの行の抽出について質...
-
【マクロ】エクセルにかいてあ...
-
スプレッドシート クエリ関数 1...
-
エクセルでセルに「氏名を入力...
-
MOS365 Excel Expert / Excel R...
-
excelの不要な行の削除ができな...
-
EXACT関数とIF関数の組み合わせ...
-
スプレッドシートの関数VLOOKUP...
-
Excelで全角を半角にしたいので...
-
Excel初心者です。 詳しい方、...
-
エクセルの数式で教えてください。
-
4つのパターンを表示するEXACT...
-
スマートな関数を教えて下さい。
-
【Excel】セル内の時間帯が特定...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
ファイル内にある数字の出現回...
-
Excel関数の先頭に「@」が入っ...
-
エクセルの気味悪い不思議
-
Excel VBAで、実行時にsheet上...
-
表示されている人数だけを数え...
-
他人が作ったマクロの理解
-
Excelの関数について質問です。
-
Excel 集計表
-
エクセル 日時の計算式について
-
Excelの関数に関して質問です。...
-
エクセル:セル内の文字列の下...
-
絞り込み検索
-
エクセルの関数で
-
エクセルの書式設定について教...
-
余分なEXCELファイルに印刷され...
-
VBA 同一シート内での転記の仕方
-
長期休みの関数はありますか
-
Excelの空のセル
-
エクセルで入力してある文を別...
-
Excelのマクロで、セルを結合し...
おすすめ情報