忍者ブログ

Excel.sys

Excelでどこまでできるか! ExcelとStiLLでビジネス・デザイン自由自在!!

   

[PR]

×

[PR]上記の広告は3ヶ月以上新規記事投稿のないブログに表示されています。新しい記事を書く事で広告が消えます。

検索値の左側の値を取得する方法2(OFFSET)

先週はセル範囲を特定し値を求める関数としてINDEX関数を使うケースを
ご紹介しました。
今回はINDEX関数の代わりにOFFSET関数を使う方法をご紹介します。

◆こんな時
検索値から左側にある値を取得したい!

◆解決ポイント
OFFSET、MATCH関数を組み合わせて使用します

◆使用方法
OFFSET関数は基準となるセル位置を最初に指定してから行、列の変位を
指定することで基準セルとの相対範囲を決定できる関数です。

使い慣れてくるとINDEX関数より柔軟に利用できるので便利です。

例:セルA1を基準としてA1から2行下のA3の値は次の式で求められます。
=OFFSET(A1,2,0) ・・・ 簡単でしょ。

それでは先週と同じ表を使ってF列の順位がC列と一致する行の名前を取得する式を下記に記述します。
 

まず値を取得する行を求める式をMATCH関数で記述します。
これは先週と同じです。

行:=MATCH(F2,$C$2:$C$5,0)= 1
この式でF2の順位(1位)が C2:C5 の範囲の1行目であることが求められます。

次に今回求めたい値は名前ですのでA列(セルA1)を基準とします。
上の式を組込み1位の名前をOFFSET関数で求める式は次のようになります。

セルG2(名前):=OFFSET($A$1,MATCH(F2,$C$2:$C$5,0),0)
この式をセルG5までドラッグするだけで式の設定は完成です。

・引数はINDEX関数と同じ3つですが設定する内容はシンプルですね。

◆「StiLL」のコマンドボタンについて
「StiLL」はExcelを便利にアップグレードするアドインツールです。

上の式の結果を値に置き換えることも超やさしく実装できます。

StiLL はすぐ使える。(いいね!)

ー 仕事に趣味に ー
StiLL はEXCELのベストパートナー

体験セミナー実施中!(無料)

それではまた来週。
PR

検索値の左側の値を取得する方法

VLOOKUP関数は、検索値から右側の値を取得できますが、
左側の値を取得することができません。
これはINDEX関数とMATCH関数を組み合わせることで可能です。 


◆こんな時
検索値の左側にある値を取得したい。

◆解決ポイント
INDEX、MATCH関数を組み合わせての使用

◆使用方法
下図を例に説明していきます。
 

 G2セルに下記数式を入力します。
=INDEX(A2:C5,MATCH(F2,C2:C5,0),1)

・数式の説明
◎INDEX関数
A2からC5セルの範囲で、行、列を指定してデータを取得します。

行:MATCH(F2,C2:C5,0)
列:1列目

◎MATCH関数
F2セル(1位)をC2からC5セルの範囲で検索し、合致したセルの行を返します。
この時のMATCH関数の結果は「3」になります。

 

A2からC5セルの範囲で、3行の1列目にあたるデータ(A3セル)の「小川」が取得できました。

同じようにして、G3セル以降は下記の数式を入力します。
G3セル : =INDEX(A2:C5,MATCH(F3,C2:C5,0),1)
G4セル : =INDEX(A2:C5,MATCH(F4,C2:C5,0),1)
G5セル : =INDEX(A2:C5,MATCH(F5,C2:C5,0),1)




INDEX、MATCH関数の組み合わせで、検索値の左側の値を
取得することができました。

◆「StiLL」について
「StiLL」には便利機能がたくさんあります。
通常のプログラミング言語のような構文や文法を使用することなく、
処理を自動化する設定が可能です。


無料で体験セミナーを開催しています。
一度参加してみてください。
https://www.still.co.jp/event/semina.html

Twitterやっています。
ぜひ見に来てください!
https://twitter.com/iliy_still

それではまた来週

COUNTIFS関数の意外で便利な使い方

COUNTIFS関数は複数条件でデータを絞り、
該当する件数を取得する場面で広く使われます。

その機能を活かしてこんな便利な使い方も出来ます。

◆こんな時
入力でついつい行を開けて入力してしまい後で修正が大変!

◆解決ポイント
COUNTIFS関数で簡単にチェックできます。

◆使用方法
例:セルB6からB11までを数字の入力範囲とするとき
  空白行の有無のチェックは次の式でできます。

式:=IF(COUNTIFS(B7:B11,">0",B6:B10,"")>0,1,0)

式の説明:B列の7行目から11行目までで0より大きく、かつ、
     対応するB列の6行目から10行目までが空白のセル
     の件数を求める式となります。

この結果、上記の式の値が1以上ならば入力されたセルの間に
空白行が存在していることを示します。




解説:通常、範囲の開始行と終了行を同じにして求めることが多いので
   "各式での範囲は同じにしないといけない"と思われているかと
   思いますが、

   実は範囲の行数が同じなら開始行と終了行は異なっていてもOKです

   この例ではそれぞれのセルは以下のように対応されます
   B7 ⇒ B6:B列の7行目と同じB列の6行目(真上のセル)が対応
   B8 ⇒ B7:B列の8行目と同じB列の7行目が対応
   --------------------------------------------------
   B11 ⇒ B10:B列の11行目とB列の10行目が対応

   今回の例では、その機能を使って空白行の存在をチェックする
   式として設定しています。

・同じ列(B列)で開始行を1行ずらして設定するところがミソです。

◆「StiLL」のコマンドボタンについて
「StiLL」はExcelを便利にアップグレードするアドインツールです。

上の式の結果でエラー表示させることも超やさしく実装できます。

StiLL はすぐ使える。

ー 仕事に趣味に ー
StiLL はEXCELのベストパートナー

体験セミナー実施中!(無料)

それではまた来週。

桁ごとに1セルずつ配置する方法

「12,345」を1セル1桁にして表示したい場合、
LEFTとRIGHT関数を組み合わせることによって実現できます。

◆こんな時
金額などを、桁ごとに1セルずつ表示させたい。

◆解決ポイント
LEFT、RIGHT関数の使用

◆使用方法
下図を例に説明していきます。




1.C2セルに下記数式を入力します。

'=LEFT(RIGHT(A2,7),1)




2.同様にして、D2からH2セルを下記のように数式を設定します。

D2セル: =LEFT(RIGHT(A2,6),1)
E2セル: =LEFT(RIGHT(A2,5),1)
F2セル: =LEFT(RIGHT(A2,4),1)
G2セル: =LEFT(RIGHT(A2,3),1)
H2セル: =LEFT(RIGHT(A2,2),1)




一番右端の桁のI2セルは、下記のように設定します。

'=RIGHT(A2,1)




1桁ずつ表示できました。




◆「StiLL」について
「StiLL」には便利機能がたくさんあります。
通常のプログラミング言語のような構文や文法を使用することなく、
処理を自動化する設定が可能です。
https://www.still.co.jp/event/semina.html


Twitterやっています。
ぜひ見に来てください!
https://twitter.com/iliy_still

それではまた次回

ちょこっと便利に使える F4 キー

普段マウスから手を離さずにExcelを操作している方も、
F4 を押下するだけで効率アップ!
その機能をご紹介します。

◆こんな時
手入力の作業をできるだけ省力化したい!

◆解決ポイント
F4キーの機能を覚えておくと便利です。
1)入力中の式のセル参照を絶対/相対に切り替えられる
2)直前のセルの書式設定を他のセルに反映できる

次の処理が F4 でできます。

◆使用方法

1)式の入力時にセルの参照を絶対参照/相対参照に切り替える
 いちいちキーボードで $ を追加入力したり削除したりせずに済みます。

 式の入力中に F4 キーを押下する度にセルの参照形式が変化します

 例)セルA1にセルZ1を参照する式を入力する場合
   セルA1:=Z1 と打ち込んだ状態で、F4キーを1回押下すると

   入力した式のセル参照形式が
   =$Z$1 に変化し

   さらに F4 を押下すると
   =Z$1 となり

   さらにさらに F4 を押下して
   =$Z1 になり

   もう1回 F4 を押下すると
   =Z1 に戻ります

   関数の入力中でも同様に参照形式を変更できます。

2)直前に書式設定した状態を他のセルにも反映させる
 「セルの書式設定」にある以下の6つの設定状態が継承されます。
 ・表示形式
 ・配置
 ・フォント
 ・罫線
 ・塗りつぶし
 ・保護

 例)セルA1の塗りつぶしを黄色にして、かつフォントを太字に設定後
   セルB2をマウスで選択して F4 を押下するとセルA1と同じ書式が
   設定されます。

   またCtrlキーを押下した状態で複数のセルを選択後に F4 を押下
   すると選択されているすべてのセルに同じ書式が設定されます。

◆「StiLL」のコマンドボタンについて
「StiLL」は Excel のベストパートナー!!

上の例の様な書式設定もStiLLボタン一つで即実装できます。
これをシステムに組み込んで自動化もやさしく実現します!

StiLLなんてすぐ使える。

ー 仕事に趣味に ー

体験セミナー実施中!(無料)

それではまた来週。

お知らせ

「StiLL体験セミナー」
(東京 恵比寿)
!!NEW!!
 StiLL体験セミナー

日付:2020年2月4日(火)
時間:14:00~17:00
場所:アイエルアイ総合研究所
   
参加費:無料(定員8名)
詳細・お申込:こちらから

ブログ内検索

Copyright ©  -- Excel.sys --  All Rights Reserved
Design by CriCri / Photo by Geralt / powered by NINJA TOOLS / 忍者ブログ / [PR]