Несколько нужных функций Гугл Spreadsheets: IMPORTRANGE, VLOOKUP, MATCH, INDEX

Основное преимущество Гугл Docs — возможность совместной работы в режиме «онлайн», просмотра конфигураций,
изготовленных каждым участником, и автоматическим сохранением животрепещущей версии (не будет больше досадных вылетов и выключений компа без сохраненного текста либо документа!).

В особенности полезны Гугл Spreadsheets, либо Гугл таблицы — аналог приложения Excel.

Они могут понадобиться для вас для:

  • совместного планирования отпуска с друзьями и расчета бюджета;
  • ведения реестров с вашими: клиентами, заказами, поставщиками и т.д., которые заполняются несколькими людьми параллельно;
  • онлайн — координации совместных действий.

Я поделюсь несколькими ординарными, но весьма полезными функциями, которые могут сохранить для вас много времени. В статье приводятся российские наименования функций, чтоб вы могли пользоваться ими и в Excel (не считая функции IMPORTRANGE, которой в Excel нет).

Функция IMPORTRANGE

Для переноса данных из 1-го файла в иной в Гугл Таблицах употребляется функция IMPORTRANGE.

Для чего же она может понадобиться?

К примеру, для вас необходимы животрепещущие данные из файла ваших коллег;

либо вы желаете обрабатывать данные из файла, к которому у вас есть доступ «Лишь для просмотра»;

либо вы желаете собрать в одном документе таблицы из нескольких и вкупе их обрабатывать либо просматривать.

Эта функция дозволяет получить копию спектра из иной Гугл таблицы. Форматирование при всем этом не переносится — лишь данные.

Синтаксис функции последующий:

spreadsheet_key (ключ_таблицы) – последовательность знаков в атрибуте «key left»>По другому говоря, ключ таблицы — это последовательность знаков в ссылке на таблицы опосля «spreadsheets/d/»

Заместо ключа таблицы вы сможете употреблять полную ссылку на документ:

В файле, в котором вы введете эту формулу, будет отображаться спектр A1:CM500 с Листа1 из файла, который находится по соответственной ссылке.

Не считая того, ссылку на файл и ссылку на спектр можно вводить не в саму функцию, а в ячейки вашего документа, и ссылаться на их.

Так, если в ячейку A2 вы введете ссылку на документ, из которого необходимо загрузить данные, а в ячейку B2 — ссылку на лист и спектр, то загружать данные можно будет при помощи последующей формулы:


Видео:

IMPORTRANGE как аргумент иной функции

IMPORTRANGE быть может аргументом иной функции, если спектр, который вы импортируете, подступает на эту роль.

Разглядим обычной пример — среднее значение по продажам из спектра, находящегося в другом документе.

Это начальный документ. Пусть данные будут добавляться и нам необходимо среднее по продажам 2016 (другими словами от ячейки D2 и до упора вниз)

Поначалу импортируем этот спектр:

А позже используем это как аргумент функции СРЗНАЧ (AVERAGE):

Получаем итог, который будет обновляться при добавлении новейших строк в начальном файле в столбце D.

Функция ВПР (VLOOKUP)

Это функция — аналог функции ВПР в Excel. Она нужна для переноса данных из одной таблицы в другую.

У нее последующие аргументы:

VLOOKUP (разыскиваемое значение; таблица; номер столбца; интервальный_просмотр)

разыскиваемое значение — это то значение, которое мы будем находить в иной таблице.

К примеру, в отдельном файле у вас есть обычная ставка перевода книжек с британского — по трем категориям трудности.

А в таблице с текущими проектами вы просто указываете категорию трудности (это и будет разыскиваемое значение), по которому функцией ВПР (VLOOKUP) подтягиваете ставку.

Таблица — это спектр данных, из которого вы подтягиваете информацию.

Аргумент «номер столбца» описывает , из какого столбца ТАБЛИЦЫ (а не листа! это принципиально) вы будете брать данные.
Интервальный_просмотр обычно равен нулю — в таком случае будет вестись четкий, а не ориентировочный поиск.

Функция ПОИСКПОЗ (MATCH)

Функция ПОИСКЗПОЗ (в британской версии Excel и Гугл Таблиц она именуется MATCH) дозволяет найти порядковый номер элемента (обычно — текста, записанного в ячейке) в определенном перечне.

Эта функция весьма комфортна для резвого сопоставления 2-ух списков: разумеется, что если функция не может отыскать порядковый номер какого-то элемента из первого перечня во 2-м перечне, то его там просто нет.

Таковым образом, вы сможете стремительно осознать, какие элементы 1-го перечня отсутствуют во 2-м.

Синтаксис функции последующий:

MATCH (искомое_значение; перечень; четкий поиск)

Разыскиваемое значение — то, что мы отыскиваем (обычно ячейка с текстом), перечень — спектр, в котором мы отыскиваем. Крайний аргумент должен приравниваться нулю, если вы желаете вести четкий поиск (обычно конкретно это и необходимо)

В примере ошибка #N/A возникает лишь в тех вариантах, в которых соответственного элемента нет во 2-м перечне.

Сочетание функций ИНДЕКС+ПОИСКПОЗ (INDEX+MATCH)

К огорчению, функция ВПР (VLOOKUP) не работает, когда разыскиваемые значения в начальной таблице находятся не в первом столбце.

Но вы сможете пользоваться сочетанием функций ПОИСКПОЗ (MATCH — дискуссировалась ранее) и ИНДЕКС (INDEX).

Функция ИНДЕКС возвращает элемент из перечня по его порядковому номеру.

А порядковый номер вы определяете при помощи MATCH.

Интересно почитать:  Excel ссылка на лист в формуле excel

В примере мы подтягиваем тему по наименованию книжки, хотя наименования находятся не в первом столбце разыскиваемой таблицы.

How to use INDEX and MATCH

INDEX and MATCH is the most popular tool in Excel for performing more advanced lookups. This is because INDEX and MATCH is incredibly flexible – you can do horizontal and vertical lookups, 2-way lookups, left lookups, case-sensitive lookups, and even lookups based on multiple criteria. If you want to improve your Excel skills, INDEX and MATCH should be on your list.

This article explains in simple terms how to use INDEX and MATCH together to perform lookups. It takes a step-by-step approach, first explaining INDEX, then MATCH, then showing you how to combine the two functions together to create a dynamic two-way lookup. There are more advanced examples further down the page.

The INDEX Function

The INDEX function in Excel is fantastically flexible and powerful, and you’ll find it in a huge number of Excel formulas, especially advanced formulas. But what does INDEX actually do? In a nutshell, INDEX retrieves the value at a given location in a range. For example, let’s say you have a table of planets in our solar system (see below), and you want to get the name of the 4th planet, Mars, with a formula. You can use INDEX like this:

Using INDEX to get the name of the 4th planet

INDEX returns the value in the 4th row of the range.

What if you want to get the diameter of Mars with INDEX? In that case, we can supply both a row number and a column number, and provide a larger range. The INDEX formula below uses the full range of data in B3:D11, with a row number of 4 and column number of 2:

Using INDEX to get the diameter of the 4th planet

INDEX retrieves the value at row 4, column 2.

To summarize, INDEX gets a value at a given location in a range of cells based on numeric position. When the range is one-dimensional, you only need to supply a row number. When the range is two-dimensional, you’ll need to supply both the row and column number.

At this point, you may be thinking «So what? How often do you actually know the position of something in a spreadsheet?»

Exactly right. We need a way to locate the position of things we’re looking for.

Enter the MATCH function.

The MATCH function

The MATCH function is designed for one purpose: find the position of an item in a range. For example, we can use MATCH to get the position of the word «peach» in this list of fruits like this:

Using MATCH to find position in a vertical range

MATCH returns 3, since «Peach» is the 3rd item. MATCH is not case-sensitive.

MATCH doesn’t care if a range is horizontal or vertical, as you can see below:

Using MATCH to find position in a horizontal range

Same result with a horizontal range, MATCH returns 3.

Important: The last argument in the MATCH function is match type. Match type is important and controls whether matching is exact or approximate. In many cases you will want to use zero (0) to force exact match behavior. Match type defaults to 1, which means approximate match, so it’s important to provide a value. See the MATCH page for more details.

INDEX and MATCH together

Now that we’ve covered the basics of INDEX and MATCH, how do we combine the two functions in a single formula? Consider the data below, a table showing a list of salespeople and monthly sales numbers for three months: January, February, and March.

Sales by salesperson by month

Let’s say we want to write a formula that returns the sales number for February for a given salesperson. From the discussion above, we know we can give INDEX a row and column number to retrieve a value. For example, to return the February sales number for Frantz, we provide the range C3:E11 with a row 5 and column 2:

But we obviously don’t want to hardcode numbers. Instead, we want a dynamic lookup.

How will we do that? The MATCH function of course. MATCH will work perfectly for finding the positions we need. Working one step at a time, let’s leave the column hardcoded as 2 and make the row number dynamic. Here’s the revised formula, with the MATCH function nested inside INDEX in place of 5:

Taking things one step further, we’ll use the value from H2 in MATCH:

INDEX and MATCH to find Feb sales for any name

MATCH finds «Frantz» and returns 5 to INDEX for row.

  1. INDEX needs numeric positions.
  2. MATCH finds those positions.
  3. MATCH is nested inside INDEX.

Let’s now tackle the column number.

Two-way lookup with INDEX and MATCH

Above, we used the MATCH function to find the row number dynamically, but hardcoded the column number. How can we make the formula fully dynamic, so we can can return sales for any given salesperson in any given month? The trick is to use MATCH twice – once to get a row position, and once to get a column position.

Интересно почитать:  Формула в excel если значение меньше 0 то равно 0

From the examples above, we know MATCH works fine with both horizontal and vertical arrays. That means we can easily find the position of a given month with MATCH. For example, this formula return the position of March, which is 3:

But of course we don’t want to hardcode any values, so let’s update the worksheet to allow the input of a month name, and use MATCH to find the column number we need. The screen below shows the result:

Dynamic lookup with INDEX and MATCH

A fully dynamic, two-way lookup with INDEX and MATCH.

The first MATCH formula returns 5 to INDEX as the row number, the second MATCH formula returns 3 to INDEX as the column number. Once MATCH runs, the formula simplifies to:

and INDEX correctly returns $10,525, the sales number for Frantz in March.

Note: you could use Data Validation to create dropdown menus to select salesperson and month.

Video: How to debug a formula with F9 (to see MATCH return values)

Left lookup

One of the key advantages of INDEX and MATCH over the VLOOKUP function is the ability to perform a «left lookup». Simply put, this just means a lookup where the ID column is to the right of the values you want to retrieve, as seen in the example below:

Left lookup with INDEX and MATCH

Case-sensitive lookup

By itself, the MATCH function is not case-sensitive. However, you use the EXACT function with INDEX and MATCH to perform a lookup that respects upper and lower case, as shown below:

Case-sensitive lookup with INDEX and MATCH

Note: this is an array formula and must be entered with control + shift + enter, except in Excel 365.

Closest match

Another example that shows off the flexibility of INDEX and MATCH is the problem of finding the closest match. In the example below, we use the MIN function together with the ABS function to create a lookup value and a lookup array inside the MATCH function. Essentially, we use MATCH to find the smallest difference. Then we use INDEX to retrieve the associated trip from column B.

Find closest match with INDEX and MATCH

Note: this is an array formula and must be entered with control + shift + enter, except in Excel 365.

Multiple criteria lookup

One of the trickiest problems in Excel is a lookup based on multiple criteria. In other words, a lookup that matches on more than one column at the same time. In the example below, we are using INDEX and MATCH and boolean logic to match on 3 columns: Item, Color, and Size:

INDEX and MATCH with multiple criteria

Note: this is an array formula and must be entered with control + shift + enter, except in Excel 365.

More examples of INDEX + MATCH

Here are some more basic examples of INDEX and MATCH in action, each with a detailed explanation:

КАК ИНВЕСТИРОВАТЬ В ЗАРУБЕЖНЫЕ КОМПАНИИ

Глава 1. Почему увлекателен зарубежный рынок акций

По итогам 2020 г. размер торгов на Санкт-Петербургской бирже вырос в 10 раз по сопоставлению с предшествующим годом. Уже в феврале 2021 г. по размеру сделок она обходит Московскую биржу, при том, что еще в прошедшем году оборот СПБ биржи составлял половину от оборота Мосбиржи, а исторически никогда не превосходил и 10%.

10-кратный рост сделок на «питерской» бирже за крайний год

График 10-кратный рост сделок на «питерской» бирже за последний год

Источник: Санкт-Петербургская биржа, https://spbexchange.ru/

В чем причина настолько быстрого спроса к данной для нас торговой площадке?

Неповторимость ее в том, что тут можно приобрести акции интернациональных компаний. Комфортно, неопасно и полностью легитимно можно стать обладателем Microsoft , Apple либо Tesla . В конце 2014 года СПБ биржа сделала мини-революцию в русском биржевом деле, в первый раз организовав торги акциями зарубежных компаний.

Для чего инвестировать за предел?

1-ая причина инвестиций в международные компании – диверсификация.

Русские компании составляют маленькую часть от всего диапазона способностей для инвестора в акции. Для осознания состояния мирового рынка можно взять фондовый индекс, который включает наибольшее количество компаний как развитых, так и развивающихся государств. Неплохим ориентиром является индекс FTSE Global All cap [1] , обхватывающий 98% глобального рынка акций.

Толика русских компаний в нем составляет всего 0.32% [2] .

Даже при инвестировании в самые ликвидные и надежные российские инструменты, настолько мощная концентрация сбережений не делает портфель устойчивым к страновым рискам и экономическим потрясениям.

FTSE Global All Cap Index по странам

График FTSE Global All Cap Index по странам

Невзирая на то, что США (Соединённые Штаты Америки — государство в Северной Америке) заносят наименее четверти в мировой ВВП (Валовой внутренний продукт — макроэкономический показатель, отражающий рыночную стоимость всех конечных товаров и услуг, то есть предназначенных для непосредственного употребления, произведённых за год во всех отраслях экономики на территории государства), вклад данной для нас страны в общую капитализацию мирового рынка акций еще выше. Вышеупомянутый индекс на 58,45% сформирован южноамериканскими бумагами.

Интересно почитать:  Как работать в эксель с таблицами формулы

Соединенные Штаты постоянно входят в число наилучших на международном уровне по собственной общей конкурентоспособности и легкости ведения бизнеса. Будучи размеренной демократией с прозрачной и прогнозируемой правовой системой, деловая культура США (Соединённые Штаты Америки — государство в Северной Америке) поощряет свободное предпринимательство и конкурентнсть. Нормативное регулирование и защита права принадлежности в особенности способствует открытию и ведению бизнеса.

Дальше в качестве зарубежных инвестиций будут рассматриваться конкретно акции южноамериканского фондового рынка.

Большая доходность и страховка от инфляции

Акции – это главный актив инвесторов во всем мире. В длительном горизонте за счет роста цены они обеспечивают доходность выше, чем остальные инструменты. К тому же почти все компании платят дивиденды, тем еще более увеличивая доход инвестора.

Покрывает ли инфляцию доходность?

Динамика цены индекса будет ориентиром для оценки доходности акций, которые его образуют. Сравним на историческом горизонте 25 лет (наибольший для русского фондового рынка), 15 и 10 лет S & P 500 и RTSi :

  • S & P 500 – один из более нередко применяемых фондовых индексов, покрывает порядка 80% капитализации рынка США (Соединённые Штаты Америки — государство в Северной Америке).
  • RTSi обхватывает 84% рынка русских акций, рассчитывается в баксах США (Соединённые Штаты Америки — государство в Северной Америке), что комфортно для сравнения «яблок с яблоками».

Примерная среднегодовая доходность русских компаний, без учета дивидендных выплат, за 25 лет составила 9.8% при росте инфляции 9.5% за этот период. При всем этом главный рост русского рынка пришелся на период до 2008. За крайние 10 лет среднегодовая доходность S & P 500 в 5 превосходит доходность RTSi .

Таблица с данными превышения доходности S&P500 над RTSi

График превышения доходности S&P500 над RTSi

Непременно, исторические характеристики не являются гарантией будущих результатов. Тем не наименее, а мериканский фондовый рынок смотрится еще наиболее прогнозируемым в критериях размеренно низкой инфляции.

Надежность вложений

Четверть компаний S & P 500 имеют вековую историю. Procter & Gamble выплачивает дивиденды своим акционерам уже 131 год попорядку с момента собственного основания в 1890 году. И крайние 65 лет наращивает выплаты, показывая приверженность компании вернуть стоимость своим акционерам. Wells Fargo (1852), Pfizer (1849), ExxonMobil (наследник Standard Oil 1870), Berkshire Hathaway (1839), Colgate — Palmolive (1806) являются одними из наистарейших компаний на Нью-Йоркской фондовой бирже.

Глоссарий для начинающих инвесторов

FTSE Global All cap

фондовый индекс, состоит из акций больших, средних и малых компаний на развитых и развивающихся рынках по всему миру. Всего составляющих – 9 122

S&P 500

фондовый индекс, в корзину которого включено 505 акций, торгуемых на фондовых биржах США (Соединённые Штаты Америки — государство в Северной Америке) общественных компаний, имеющих самую большую капитализацию

индикатор фондового рынка Рф , включает более ликвидные акции больших и оживленно развивающихся русских эмитентов

Акция

эмиссионная ценная бумага, толика владения компанией, закрепляющая права её обладателя (акционера) на получение части прибыли акционерного общества в виде дивидендов, роль в управлении компании и на часть имущества в случае ее ликвидации акционерного общества в виде дивидендов, роль в управлении компании и на часть имущества в случае ее ликвидации

Биржа

устроитель торгов продуктами, валютой, ценными бумагами, производными и иными рыночными инструментами

Брокер

посредник меж личным инвестором и биржей. Его задачка исполнить заявку инвестора «приобрести» либо «реализовать» от имени инвестора. Брокеры зарабатывают средства, взимая комиссию со собственных клиентов за любой выполняемый ими заказ. Потому лучше постоянно держать в голове про конфликт (наиболее острый способ разрешения противоречий в интересах, целях, взглядах, возникающий в процессе социального взаимодействия) интересов меж брокером и инвестором: чем больше клиент хаотично покупает и реализует инструменты, тем больше зарабатывает брокер

Индекс

сводный индекс, вычисляемый на базе цен определённой группы ценных бумаг. При расчёте индекса его изначальное (базисное) значение может являться суммой цен либо равняться к произвольному числу (к примеру, 100 либо 1000). Индикатор состояния рынка ценных бумаг

Капитализация

рыночная стоимость 1 ценной бумаги в воззвании. Капитализация компании — произведение количества акций акционерного общества на их текущую (рыночную) стоимость

Фондовый рынок

рынок, на котором акции компаний выпускаются и торгуются или через биржи, или на внебиржевых рынках. Способ, при помощи которого компании завлекают капитал, а инвесторы обладают частью компании

[Главы 2 и 3 в следующих блогах]

[1] FTSE Global All cap

[2] FTSE Russell Factsheet. FTSE Global All Cap Index по состоянию на 31.07.2021

[3] Рост значения индекса на сен’21 к историческим значениям по формуле [ Индекс 1 – Индекс 0 ] / Индекс 0

[4] Compound annual growth rate – совокупный среднегодовой темп роста

Ссылка на основную публикацию
Adblock
detector