Руководство по сборщику позиций и анализу конкурентов в Rush Analytics
Видимость задачиЭтот функционал позволяет в 1-2 клика оценить реальные результаты продвижения сайта в глобальной сети при помощи интуитивно понятного интерфейса.
Система автоматически показывает, какие из выбранных поисковых запросов популярны и востребованы у пользователей, а какие являются лишь бесполезным отягощением вашего бюджета. Позволяет отслеживать потенциал привлечения трафика и оперативно предпринимать корректирующие действия.
При построении видимости группы запросов учитываются только запросы, находящиеся в ТОП-20, т.к. реальные пользователи дальше не ходят.
Видимость группы запросов (формула мегаиндекс)
Q1, Q2, … Qn – коэффициент видимости запроса 1,2, … n в данной группе
Q1 = 1, если позиция запроса от 1 до 3 включительно
Q2 = 0,85, если позиция запроса 4.
Q3 = 0,6, если позиция запроса 5.
Q4 = 0,5, если позиция запроса от 6 до 7 включительно.
Q6 = 0,2, если позиция запроса 10.
Q7 = 0,1, если позиция запроса от 11 до 15 включительно.
Q8 = 0,05, если позиция запроса от 16 до 20 включительно.
W1, W2 … Wn – точная частотность по wordstat этих запросов («!»)
VIS – видимость группы запросов в %
VIS = 100 * (Q1*W1 + Q2*W2 + … + Qn*Wn) / (W1 + W2 + … + Wn)
Выводится на графике для каждой группы запросов, а также для всех запросов.
В функционале видимости задачи вы можете задать: поисковую систему, регион и дату по которой будут показаны данные.
Так же можно обновить частотность вашей задачи.
Данные могут сортироваться по:Вы можете отметить галочкой те группы которые хотите увидеть на графике.
Отобразить или скрыть ненужные для вас столбцы
Добавьте группы в задачу для более детальной аналитики своего ранжирования! (перейдите в настройки задачи)
Профессиональные инструменты для проверки позиций в Google
Существует много различных программ и сервисов для проверки текущих позиций сайта по ключевым словам в поисковых системах, таких как Google, Bing и Yahoo. Многие используют свои собственные программы для анализа позиций и подбора ключевых слов. Когда есть необходимость проверить позиции в Google я чаще всего пользуюсь следующими сервисами:Rank Checker
Бесплатный плагин для Firefox, разработанный англоязычной seo-компанией SEOBook, который позволяет быстро проверить текущие позиции. Он особенно полезен в тех случаях, когда важна не столько абсолютная точность, сколько нужно просто быстрее оценить, где находится сайт в выдаче. Полезной фишкой является экспорт результатов съема позиций в файл CSV для дальнейшего анализа в MS Excel. На скриншоте ниже показано, в каких поисковых системах можно снимать позиции (Google, Bing, Yahoo). Что еще более приятно – можно выбрать проверку в региональных поисковиках Google, т.е. можно снять позиции и в com.by и в co.uk и т.д. После выбора поисковой системы, в специальное поле вписываем URL сайта, позиции которого будут сниматься, а чуть ниже вписываем список ключевых слов, позиции которого будет проверять. Запускаем проверки и через какое-то время, как правило это происходит очень быстро, мы получим готовые результаты. Чтобы ускорить съем позиций рекомендую в настройках выставить меньший интервал задержки между запросами.
SRWare Iron
Идеальный браузер, построенный из Google Chrome. Главные его преимущества: быстрый серфинг по сайтам и удобный дизайн, как и в Google Chrome, а также добавлены фишки, благодаря которым браузер не отслеживает запросы пользователей, а также есть режим инкогнито, благодаря которому можно вручную проверить абсолютно точные позиции сайта по любым запросам.
Многие, кто хоть раз пробовал воспользоваться браузером SRWare Iron, в последствии, потом переходили полностью на него, увидев, какой он быстрый и гибкий. Раньше я пользоваться Firefox, т.к. мне нравилось использовать различные плагины и расширения, что делало работу в интернете более удобной, однако большое количество различных дополнений значительно сокращало работу браузера. К счастью, оказалось большое количество бесплатных плагинов и тем для браузера SRWare Iron, при чем в любой момент можно быстро отключить то или иное приложение. На скриншоте ниже показана панель StumbleUpon, которая разворачивается и сворачивается при нажатии на иконку расширения.
Advanced Web Rankings (часто упоминается как AWR)
Отличная программа, которую мне удалось опробовать для съема позиций. Правда радовался и не долго, когда закончился триальный период в 30 дней, пришлось удалить софтину, ибо стоит она от 100 до 600 баксов, в зависимости от количества прикрученных фишечек.
Полнофункциональная программа Advanced Web Rankings позволяет:
- Отслеживание позиций. Удобный мониторинг позиций по ключевым словам в рамках компаний, отслеживание динамики роста либо падения позиций в поиске.
- Анализ конкурентов. Сбор данных всех участвующих в поиске сайтов в рамках определенной компании, благодаря чему можно найти больше ключевых слов и путей для развития своих проектов.
- Отчеты. Есть большой выбор различных отчетов, в которые полезные данные генерируются в удобном виде в файлы формата CSV. Любой отчет можно настроить и адаптировать под свои нужды. Кроме того, есть большое количество наглядных графиков, таблиц и диаграмм, на которых можно четко отслеживать прогресс продвижения сайтов.
А какими инструментами для съема позиций предпочитаете пользоваться вы?
Как провести аудит сайта самостоятельно [Руководство по сервису «Анализа сайта»]
Руководство по сервису «Анализ сайта» от PR-CY. Автоматическая проверка сайта: SEO, трафик, позиции в выдаче по запросам, технический аудит, юзабилити, санкции ПС и другие параметры.
Следить за состоянием своего сайта или клиентских проектов можно в одном удобном сервисе. Мы создали «Анализ сайта» для экономии вашего времени. Наблюдайте за индексацией в поисковых системах, появлением входящих ссылок, санкций от поисковых систем, ищите ошибки на внутренних страницах и следите за позициями в выдаче в одном интерфейсе нашего сервиса.
«Анализ сайта» от PR-CY в течение пары секунд проверит ресурс, покажет отчет с ошибками и успешными тестами и даст чек-лист с советами по исправлению.
Возможности сервиса:
- Проверка сайта по более 70 тестам
В течение пары секунд определит количество индексируемых страниц, построит динамику трафика, найдет внешние и внутренние ссылки на сайт, проанализирует оптимизацию текста, юзабилити и мобилопригодность страницы, посчитает видимость по поисковым фразам и другое. - Анализ внутренних страниц сайта
Краулер проверит коды ответов сервера, мета-теги, оптимизацию контента и технические данные на внутренних страницах. - Поиск и выделение ошибок
Наглядно обозначит, какие тесты сайт провалил, и даст подсказки, как это исправить. - Составление списка задач
Чек-лист задач можно передать специалисту или заняться им самостоятельно, отмечая выполненные пункты прямо в сервисе. К каждому пункту есть описание и ссылки на статьи по теме. - Сравнение с конкурентами
Добавьте к своему сайту до четырех конкурентов и сравнивайте показатели и графики. - Отслеживание позиций
- Брендированный отчет
Показать результаты клиентам или руководству можно в формате pdf-отчета. Загрузите свой логотип и введите название компании, задайте оформление через CSS, выберите нужные тесты и скачивайте брендированный отчет в два клика. - API для разработчиков
Создавайте свои расширения, сервисы и приложения на базе данных, полученных с помощью API Анализа.
Подробное руководство поможет разобраться со всеми возможностями Анализа сайта.
Как работать в «Анализе сайта» — полное руководство по сервисуДля тех, кто не хочет читать статью, мы подготовили видеоинструкцию.
Разберем, как добавить сайт и настроить проверку, какие тесты есть в Анализе, и где включаются специальные функции.
Настройка и интерфейс Анализа
Как добавить сайты и настроить проверки
В разделе «Мои проекты» находятся активные сайты, вы можете добавить столько проектов, сколько позволяет ваш тариф. Для них доступны все функции расширенного анализа.
Перед работой нужно провести простую настройку.
Список проектовКаждый проект нужно настроить отдельно. Подключите системы статистики, чтобы сервис получил больше данных.
Если вы поставите отметку у пункта «Еженедельный отчет по проекту», вам на почту раз в неделю будет приходить письмо со сводкой по сайту. В письме появятся только те количественные данные, которые изменились за неделю — индексация, ИКС, трафик, ссылки и активность в соцсетях. Письма будут приходить по вторникам в 10 утра.
Пример письма с отчетомОтдельным блоком в письме будет информация о внутренних страницах. Увидеть полный список тестов можно будет по ссылке из отчета.
Пример отчета о работе внутренних страницЕсли вам не хочется получать письма с данными по изменению показателей сайта, просто не включайте еженедельный отчет в настройках.
Ниже в настройках добавьте конкурентов, чтобы сравнивать ваши количественные показатели. Вы можете ввести конкурентов вручную или добавить из предложенных сервисом.
Добавление конкурентов в сервисВ отчетах это будет выглядеть так:
Как запустить проверку
Для запуска проверки просто кликните на название сайта из раздела «Мои проекты». По клику на иконку можно сразу перейти к анализу внутренних страниц или к проверке позиций.
Перейти к общему анализу сайта, к анализу внутренних страниц или проверке позицийСначала разберем тесты общего анализа, а потом анализ внутренних страниц и проверку позиций.
Отчеты Анализа сайта
Общий анализ касается главной страницы и сайта в целом. После запуска анализа результаты появятся в течение нескольких секунд.
В верхней части страницы отражена общая информация о проекте. Есть возможность добавить конкурентов для сравнения, если вы не сделали это в настройках.
Кнопка добавления конкурентов для сравнения
Под оценкой сайта есть три шкалы: успешные тесты, ошибки и информационные сообщения. Клик на каждую из них включает фильтрацию результатов. Если вы кликнули на число ошибок, в интерфейсе дальше будут отображаться только тесты, в которых проверка показала ошибку.
Тесты для проверки сгруппированы по разделам, они отражены слева в меню: поисковые системы, трафик, ссылки на сайт, оптимизация и другие. Разберем отчеты по порядку.
Поисковые системы
В этом разделе сервис считает ИКС, оценивает прогресс в получении знаков от Яндекса, считает индексацию страниц ПС, ищет санкции и проверяет сайт на вирусы.
Если вы добавили конкурентов, он посчитает данные по ним и покажет в столбце для сравнения. Зеленые и красные цифры означают рост или спад на указанное число пунктов.
Сравнение данных с конкурентамиПо клику на строку теста появится пояснение и ссылки на статьи с информацией по теме.
Пояснение и дополнительные ссылкиГрафик с динамикой показателя откроется по клику на значок с диаграммой.
Как посмотреть динамику индексации сайтаМетрика PR-CY Rank
В конце отчета для экономии времени собраны ссылки на дополнительные инструменты. К примеру, по клику на «Упоминания сайта в поиске» откроется страница Яндекса с адресом вашего сайта в строке поиска —— и не надо ничего вводить самостоятельно.
Трафик
Сервис собирает данные по трафику из доступных источников: сначала ищет открытые счетчики статистики — LiveInternet и Яндекс.Метрика. Если их нет, берет данные из Alexa, но этот показатель имеет косвенное значение, поэтому лучше подключать Метрику и Google Analytics для более точной статистики.
Также он делит трафик по источникам, считает посетителей из социальных сетей и составляет картину аудитории по географии и полу.
Анализ аудитории сайтаСсылки на сайт
Анализ сайта считает количество доноров и страниц, с которых стоят ссылки. По этим данным вы можете отслеживать результаты ссылочного продвижения. Если вы добавили конкурентов, то можете сравнить показатели. Также сервис анализирует характер ссылок и определяет, какой процент из них занимают естественные, а какие ссылки относятся к покупным.
Почитать по теме:
SEO- и естественные ссылки: ссылочное продвижение в Google и Яндекс
По качеству ссылок сервис определяет уровень доверия поисковиков к сайту — чтобы у сайта был высокий траст, он должен иметь ссылки с трастовых и тематических доменов.
Проанализировать ссылочный профиль сайтаАнализ сайта находит новые ссылки и показывает их в таблице с указанием страницы-донора, анкором и атрибутом rel — nofollow, dofollow или noindex. Так вы можете отслеживать новые ссылки и контролировать ход ссылочного продвижения.
Почитать по теме:
Как использовать анализ PR-CY.ru для проверки ссылок
Результаты можно экспортировать в таблице Excel.
Социальные сети
Яндекс и Google видят социальные сети компании и учитывают их, когда составляют мнение о качестве сайта. Анализ сайта находит страницы в социальных сетях и собирает статистику об активности. По этим данным вы можете следить за ростом аудитории ваших групп и сравнивать данные с конкурентными.
Фрагмент статистики по социальным сетямАнализ сайта проверяет наличие страниц в социальных сетях в два этапа:
- Робот ищет код разметки Schema.org: Organization.
- Если робот не находит этот код, то начинает искать ссылки на соцсети.
Как только он находит код или ссылки, то анализирует данные и выводит в отчет. Советуем использовать код разметки Schema.org, чтобы процесс шел быстрее и другие боты тоже могли быстро найти данные о соцсетях.
К примеру, разметка может выглядеть так:
<script type="application/ld+json">
{
"@context" : "http://schema.org",
"@type" : "Organization",
"name" : "PR-CY",
"url" : "http://pr-cy.ru",
"sameAs" : [
"http://vk.com/prcyru",
"https://www.facebook.com/prcyru",
"https://twitter.com/prcyru",
"https://plus.google.com/+prcyru"
]
}
</script>name — название сайта, url — адрес сайта, sameAs — ссылки на соцсети.
Если группа не отображается в сервисе
Бывает такое, что компания завела страницу, например, в Фейсбуке, но в Анализе сайтов ссылка на нее не появляется. Проблема может быть в формате страницы. Важно правильно зарегистрировать ее в социальной сети и указать адрес сайта в информации о странице.
В Фейсбуке это должна быть бизнес-страничка, на которой в специальной графе «веб-сайт» прописана ссылка. Сообщества для обсуждения и страницы в формате личного профиля не засчитаются.
Выбор профиля страницыВ ВКонтакте считается только группа или публичная страница, но не личная страничка, даже если она оформлена на представителя бренда. В информации о группе тоже нужно указать сайт.
Добавление сайта в информацию о группеВ Твиттере нет разделения по видам страниц, поэтому просто зарегистрируйте аккаунт бренда и добавьте ссылку на сайт в описание, как это сделали мы для аккаунта twitter.com/prcyru:
Ссылка на сайт в описании Твиттер-аккаунтаОптимизация
Сервис анализирует, как страница оптимизирована для поисковых систем. По этому пункту можно быстро проверить текстовую составляющую: нет ли дублей в заголовках, отображается ли описание, не зашкаливает ли тошнотность текста и другое.
Проверка контента на главной страницеВторая часть этого раздела — техническая оптимизация. Размер страницы, скорость загрузки, вес ресурсов, которые на ней размещены. Если ресурсы занимают слишком много места, сервис выведет ошибку — это значит, что их можно оптимизировать.
Анализ скорости загрузки страницыТакже в этом тесте можно увидеть, есть ли на главной битые ссылки и ошибки в HTML-коде, смогут ли поисковики распознать разметку Schema.org и Open Graph у вашего и конкурентных сайтов. Если сервис не нашел микроразметку, он покажет предупреждение — это не считается ошибкой, но код разметки для поисковых систем лучше указать.
Серверная информация
Анализ покажет всю доступную серверную информацию о вашем или чужом сайте. Он определяет IP, местоположение сервера, датацентр. Определяет наличие SSL-сертификата, настройку редиректа с www и корректность кодировки, наличие правильно размещенного файла robots.txt и карты сайта.
Поиск robots.txt и карты сайтаЕсли вы следите за чужим сайтом в Анализе, вам может быть интересен раздел о технологиях: сервис покажет язык программирования, который используется на сайте, CMS, сервер, фреймворки и другие технологии.
Технологии сайта в тесте АнализаЮзабилити
Сервис ищет фавикон и проверяет страницу 404, но по большей части раздел юзабилити связан со скоростью загрузки. Сервис анализирует скорость поэтапно, проверяет настройки кэширования элементов на главной странице, сжатие и время ответа сервера.
Проверка скорости загрузки главной страницыЕще показывает, какие CSS не используются — это значит, что их загрузку можно отложить и тем самым ускорить появление контента для пользователя.
Мобилопригодность
Анализ проверит, как сайт отображается на мобильных устройства: найдет тег viewport, оценит размер шрифта и проверит наличие плагинов, которые не запустятся у пользователей со смартфона.
В миниатюре на макете вы увидите, как главная страница смотрится на маленьком экране смартфона.
Отображение страницы на смартфонеПоисковые фразы
Раздел с поисковыми фразами покажет, в каком состоянии оптимизация страниц вашего сайта под ключевые слова. Сервис анализирует пользовательские запросы, по которым они находят ваш сайт, отображает позиции и частоту показов в месяц. Обратите внимание, по каким главным ключам больше всего показов и выше позиции.
Почитать по теме:
Видимость сайта в поисковиках – A.PR-CY и LINE.PR-CY
Есть две таблицы по Яндексу и Google отдельно, в них отражены ключи, ссылки на страницу, ее позиции и показы. Результаты можно скачать в таблице Excel.
Оптимизация под поисковые фразыНа этом отчеты общего анализа заканчиваются, перейти к краулеру для проверки внутренних страниц можно из левого меню:
Ссылка для перехода к анализу внутренних страницПроверка внутренних страниц сайта
Краулер проанализирует внутренние страницы сайта и найдет ошибки. Он доступен на всех платных тарифах Анализа, количество страниц для проверки зависит от тарифа.
Как настроить краулер для анализа внутренних страниц
Вы можете изменить настройки по умолчанию: выставить ограничения для ботов согласно вашему файлу robots.txt и указать задержку между запросами, чтобы не перегрузить сервер, где находится сайт.
Если вы поставите отметку пункту «Выполнять анализ автоматически — Каждый день», то сможете получать информацию об изменениях параметров в еженедельных отчетах.
Настройки краулера в Анализе сайтаПосле настройки запустите краулер и подождите несколько секунд, пока он анализирует информацию. Откроется отчет с разделами:
- коды ответов сервера;
- мета-теги;
- контент;
- технические данные.
В разделе Страницы сайта находится сводка по всем отчетам проверки с указанием, сколько страниц содержит конкретную ошибку. По клику на количество страниц сервис откроет список с адресами:
На этой же странице есть кнопка для экспорта страниц — по клику скачивается отчет, сформированный в таблице Excel.
Скачать таблицу с отчетамиПо клику на Страницы сайта вы перейдете на список всех просканированных страниц. Настраивайте отображение результатов с помощью фильтров и колонок и смотрите только те показатели, которые вам интересны:
Краулер позволяет найти внутренние страницы с ошибками в мета-тегах, сбоями в кодировке, большим количество ссылок и другими ошибками.
Обновлять проверки внутренних страниц можно от пяти раз в день в зависимости от тарифа.
Проверка позиций сайта по запросам
Недавно мы добавили в Анализ сайта возможность отслеживать позиции страниц по ключевым запросам. Теперь не нужно переключаться между Анализом сайта и LINE, если вам нужна функциональность обоих сервисов. Следите за позициями в интерфейсе Анализа.
По настройке есть видеоинструкция:
Настройка
В «Настройках» можно выбрать, где отслеживать позиции — в Яндексе, Google, в каком регионе, на каком языке и устройстве — десктопе или мобильном. Сервис умеет учитывать поддомены. К примеру, если интересует только Москва, десктопная и мобильная выдача в Яндексе и Google, настройки будут такими:
Настройки проверки позицийЗапросы для отслеживания нужно добавить в поле раздела «Управление запросами».
Съем частотности пока реализован только по Яндекс.Вордстату. Частота запросов определяется с указанием региона, по которому вы проверяете позиции.
Для удобной работы с ключевыми запросами вы можете создавать группы и сортировать слова. Каждому слову можно присвоить несколько групп. Например, чтобы сортировать по тематике и частотности.
Чтобы создать новую группу, зайдите в раздел «Группы» в меню слева и нажмите «Добавить ». Впишите названия и сохраните. Далее в разделе «Управление запросами» выберите запросы, которые хотите сгруппировать, и добавьте в нужную группу.
Сводка по топу в регионах
В сводке по регионам видны изменения средней позиции и топов, вы сами устанавливаете интересующий вас временной период. По клику на календарь открывается фильтрация по датам. Вы можете посмотреть сводку по неделе, месяцу или году, а можете выбрать даты сами.
Выбор фильтрации по датамКликните на нужный регион и сервис построит график, чтобы вы оценили среднюю позицию:
Сводка по топу в регионахЕсли выбрать данные по топам, сервис покажет количество запросов, попавших в топ-3, 10, 30 и топ-50.
Позиции
В отчете Позиции вы увидите запрос, позицию сайта по нему и целевую страницу в зависимости от выбранной поисковой системы и региона. Вверху отображается динамика, чтобы вы представляли, растут позиции или падают.
Если вы включите ежедневное автообновление, сервис будет собирать данные самостоятельно, и вы не пропустите важные события.
Отчет с динамикой позицийЕсли кликнуть на запрос в таблице позиций, появится история изменений на графике.
Релевантные страницы
Недавно мы добавили отчет по релевантным страницам, в нем показываем URL по вашим ключевым словам, полученным из выдачи. Удобно смотреть сводку по разделам вашего сайта, если ключи плохо разбиты по группам. Кстати, в этом же отчете в один клик можно проверить уровень оптимизации страницы.
Отчет по релевантным страницам
Все данные доступны для экспорта в таблице. То что видите на экране, будет у вас в файле cvs.
Мы работаем над тем, чтобы добавить больше графиков по видимости и топам.
Цены и лимиты
Проверка позиций доступна на тарифах «Начальный», «Профи» и «Агентство» и включена в стоимость тарифа. Вам не нужно отдельно платить за проверки каждого ключевого слова.
Лимиты:
- «Начальный» — 1000 слов;
- «Профи» — 2000 слов;
- «Агентство» — 5000 слов.
=»text-align:>
Еще одна функция расширенного тарифа Анализа сайтов — создание брендированного отчета о результатах проверки.
Брендированный отчет о результатах проверки
На тарифе «Профи» можно создавать брендированные pdf-отчеты с подробной сводкой по результатам проверки сайта. Их можно использовать для демонстрации начальству, клиентам, в качестве коммерческих предложений.
В настройках можно отредактировать отчет на свой вкус. Ввести название компании, залить свой логотип для колонтитула, заполнить введение текстом:
Настройка отчетаДобавить контакты на последнюю страницу отчета, настроить отображение чек-листа с задачами:
Настройка заключения и списка задачВыбрать тесты, результаты которых будут отображены в отчете:
Задать стиль документу с помощью CSS:
Готовый отчет с вашими настройками можно скачать на странице с результатами полного анализа.
Скачать отчетДлина отчета зависит от количества выбранных вами тестов. Каждый раздел начинается с новой страницы, в колонтитуле название вашей компании и логотип.
Пример отчетаГотовый документ можно распечатать и сдать начальству или отправить по электронной почте, использовать в качестве базы для коммерческого предложения клиенту.
Вы можете доработать сервис и на базе Анализа сайта собрать свое расширение с помощью API.
API Анализа сайта
API Анализа — интерфейс из набора команд, который выполняет все 70+ тестов из сервиса «Анализ сайтов». Вам будет доступны все тесты, история и обновление данных.
С помощью API вы сможете встроить данные в код сайта для быстрого анализа и отчетов с нужными вам тестами, создать отдельное приложение, расширение, виджет или сервис для проверки.
Варианты использования:
- White-label анализ сайта
Создайте свой аналог Анализа сайта на площадке своего проекта. - Клиентский аудит
У вас будут все данные по сайту клиента, оформляйте их в свой отчет с оригинальными текстами и передавайте готовый аудит клиенту. - Массовый мониторинг
Получите данные по вашим сайтам API, смотрите историю изменений и отслеживайте динамику. - Свои продукты
Создавайте ботов, виджеты, приложения, отчеты и другие продукты на базе Анализа.
API доступно на тарифах «Профи» и «Агентство».
Инструкция по API
Тарифы Анализа
Сервис работает в бесплатном и расширенном вариантах.
Полная функциональность доступна на платных тарифах: они дают возможность отслеживать динамику показателей, сравнивать с конкурентами, проверять внутренние страницы, отслеживать позиции сайта по ключевым запросам, составлять отчеты, пользоваться API.
Тарифы «Начальный», «Профи» и «Агентство» различаются количеством доступных проверок и функциями:
- Если вы занимаетесь своими проектами, вам подойдет «Начальный» тариф.
Он позволяет следить за пятью сайтами одновременно и проверять позиции по 500 запросам. - Если ведете клиентские сайты, выбирайте «Профи».
Анализируйте до 20 сайтов одновременно, проверяйте позиции по 1500 запросам, пользуйтесь API и составляйте брендированные отчеты для демонстрации результатов. - Если вы работаете в большой студии, вам подойдет тариф «Агентство».
Он дает возможность вести до 60 проектов одновременно, проверять позиции по 5000 запросов, функцией API и отчетами.
Подробное сравнение тарифов и цены. При покупке годового тарифа цена вдвое ниже!
При покупке любого тарифа дарим наше «Руководство по самостоятельному аудиту» — pdf с перечнем советов о том, как самостоятельно проверить сайт по всем параметрам.
Если вы еще не уверены, нужны ли вам все функции тарифов Анализа сайта, подключите триал и неделю пользуйтесь ими бесплатно.
Попробовать все возможности «Анализа сайта» бесплатно
Анализ сайта от PR-CY — сервис для быстрой самостоятельной проверки сайта по 70+ параметрам, включая оптимизацию, скорость загрузки, индексацию в ПС и трафик. Он ищет ошибки и помогает оперативно их исправить на главной и внутренних страницах сайта.
Комплексное продвижение сайта под ключ СЕО
SEO-оптимизация
Инструменты для работы с семантическим ядром
- «Планировщик ключевых слов Google»
«Планировщик ключевых слов» Google определяет ключевые фразы, которые чаще всего используют пользователи крупнейшей поисковой системы мира при поиске той или иной информации. Данный инструмент позволяет прогнозировать трафик и бюджет на контекстную рекламу, группировать запросы и скачивать их на компьютер в формате .csv.
- Yandex Wordstat Assistant
Плагин для Google Chrome, который помогает работать с «Яндекс.Вордстатом». Установив данное расширение, вы сможете одним кликом копировать подходящие ключевые слова, а потом одним скопом вставлять их в документ Excel. Очень удобно.
- Подбор слов «Яндекс»
Всем известный сервис для поиска наиболее популярных запросов среди пользователей поисковой системы «Яндекс». Показывает статистику запросов, помогает прогнозировать показы, демонстрирует похожие запросы.
- Key Collector
Профессиональный инструмент для работы с семантическим ядром сайта. Позволяет определять наиболее популярные ключевые запросы, оценивать стоимость продвижения проекта по ним, отслеживать топовые ключи конкурентов и т.д. Функций у этого приложения очень много. Большинство из них вам даже могут никогда не понадобиться, если вы не занимаетесь SEO профессионально.
- Slovoeb
Бесплатная замена предыдущему приложению (от того же разработчика), но с ограничением по функционалу. Подойдет владельцам сайтов, продвигающим свой ресурс самостоятельно. Все основные функции в данном приложении предусмотрены: поиск ключевых слов, отслеживание позиций сайта, возможность задавать быстрый фильтр в списке ключей и т.д.
- Rush Analytics
Rush Analytics автоматически анализирует ключевые слова из сервиса «Подбор слов» «Яндекса». В настройках проекта можно указать параметры оценки ключей: добавить операторы, зафиксировать порядок слов. Собранные ключи и статистику можно экспортировать. Rush Analytics собирает подсказки из «Яндекса», Google и YouTube. С их помощью можно расширить семантическое ядро. Собранные подсказки можно загрузить в таблицу.
- SpyWords
Русскоязычный сервис, позволяющий получать информацию о рекламных кампаниях конкурентов (текст объявлений, позиции), в также сравнивать семантические ядра выбранных ресурсов (ключи, позиции, сниппеты в выдаче).
Инструменты для проверки позиций сайта
Сервисов и приложений для проверки позиций сайта существует очень много. Два из них уже были описаны в предыдущем пункте – Sloboeb и Key Collector, а остальные разберем далее.
- Allpositions
Очень простой сервис с интуитивно понятным интерфейсом. Отчет, сделанный с помощью этого сервиса, выглядит следующим образом:
Главные плюсы этого инструмента:
- импорт ключей и экспорт отчетов в таблице .csv
- разные поисковые системы («Яндекс», Google, Mail.ru, Rambler, Tut.by)
- возможность настроить автоматический съем позиций сразу после апдейта «Яндекса»
- если нажать на вкладку «Подробнее» можно посмотреть результаты по ТОПУ-10 и сниппет, который показывается в выдаче по выбранному запросу
- TopInspector
Очень похож на предыдущий сервис. Из плюсов можно выделить следующие:
- импорт слов из «Яндекс.Метрики» и Google Analytics
- до 4-х регионов в одном проекте
- группировка и сортировка фраз
- выгрузка отчетов в формате .csv и .xls
Минус: первый отчет формируется довольно долго. Анализ 7 ключевых слов в двух поисковых системах занял у меня более суток, хотя сервис об этом предупреждает.
Инструменты для отслеживания апдейтов «Гугла» и «Яндекса»
- Promosite
Сервис по отслеживанию апдейтов «Яндекса». Работает очень просто – чем выше столбец на диаграмме, тем сильней «тряска» в выдаче.
- Mozcast
Сервис для отслеживания апдейтов Google. Работает примерно по такому же принципу, как и предыдущий – чем выше температура, тем сильнее изменения в выдаче.
- Форум Searchenegines
Главный форум рунета для тех, кто интересуется продвижением сайтов. Регистрация в этом сообществе позволит вам всегда быть в курсе апдейтов поисковых систем. Для этого нужно периодически заглядывать в две главные темы форума – «Яндекс» и «Google».
Инструменты для мониторинга ссылок
Для отслеживания внешних ссылок своего сайта лучше всего использовать «Яндекс.Вебмастер» и «Панель для веб-мастеров» от Google. А вот для отслеживания ссылок конкурентов следует воспользоваться сторонними.
- Linkpad
Linkpad показывает внешние ссылки выбранного ресурса, а также некоторую другую важную с точки зрения поисковой оптимизации информацию (количество страниц в индексе, сайты-доноры, анкоры и т.д.). Главный минус бесплатной версии – ограниченные по количеству строк. Главный плюс – можно просмотреть абсолютно все ссылки без каких-либо ограничений.
Технические инструменты SEO
Инструменты этой группы позволяют проверять техническое соответствие ресурсов требованиям поисковых систем, а также получать другую важную с точки зрения поисковой оптимизации информацию.
- Google Search Console (бесплатный)
С помощью Google Search Console можно проверить состояние индексирования сайта, получить сведения о поисковом трафике, проверить состояние файла sitemap и параметры URL.
- «Яндекс.Вебмастер» (бесплатный)
Сервис позволяет следить за индексированием ресурса, настраивать параметры индексирования, определять поисковые запросы, оценивать позиции сайта в поиске и получать уведомления о технических ошибках.
- Netpeak Spider (платный)
Приложение, которое позволяет анализировать внутренние и внешние ссылки, быстро находить ошибки, включая неработающие линки, дублированный контент и неверные редиректы. Делает подробные отчеты в формате таблицы Excel.
- Xenu Link Sleuth (бесплатный)
Находит битые ссылки, составляет карту сайта, ищет страницы с долгим временем отдачи и неуникальными заголовками, предоставляет компактные отчеты.
- Screaming Frog SEO Spider Tool (условно-бесплатный)
Позволяет быстро выполнить SEO-аудит ресурса. Сканирует сайт или определенный его раздел и выдает все работающие и неработающие страницы с детальными данными по каждой из них (title, description, h2, h3, ключевые слова, количество знаков на странице, количество слов).
- Расширение Check My Links для Chrome (бесплатный)
Полезнейшее расширение Google Chrome – находит на любой странице ссылки (обычные и редиректные) и показывает, работают они или нет. Все плохие ссылки можно в 1 клик скопировать в буфер обмена.
Мастхэв-плагин для редакторов, маркетологов, SEO-специалистов.
- Google PageSpeed Insights (бесплатный)
Сервис, который определяет скорость загрузки сайта и дает рекомендации по ее увеличению.
- Mobile-friendly test (бесплатный)
Сервис «Гугла», который проверяет, насколько хорошо оптимизирован ваш сайт под мобильные устройства, указывает на допущенные ошибки и дает советы по улучшению.
- Плагин для Microsoft Excel Seo Tools (бесплатный)
Плагин, который автоматизирует процесс сбора информации по любым страницам выбранного сайта (дата публикации, заголовок, количество слов в статье, категория, к которой она принадлежит, и многое другое). Благодаря этому плагину вы сможете определить наиболее эффективный контент вашего сайта и сосредоточить усилия именно на нем. Главные плюсы: есть интеграция с Majestic SEO и Google Analytics.
- Расширение RDS bar для Mozilla и Chrome (бесплатный)
Расширение для быстрого анализа основных SEO-показателей сайта: проиндексированные страницы, входящие ссылки, индекс цитирования, трафик. Можно быстро посмотреть файл robots.txt, sitemap.xml, узнать CMS, на которой сделан сайт, и т. д.
Инструменты для анализа конкурентов
- Similarweb (условно-бесплатный)
Онлайн-сервис, который позволяет сравнивать между собой несколько сайтов. При сравнении оцениваются показатели интернет-трафика, посещаемости, обзор каналов.
- Serpstat (условно-бесплатный)
Платформа, которая состоит из четырех блоков: анализ обратных ссылок, SEO-аудит сайта, анализ семантики и аналитика конкурентов.
- Megaindex (условно-бесплатный)
Платформа для SEO-анализа. Можно провести анализ сайта, анализ видимости в Google и «Яндекс», подобрать ключевые слова.
- «Социальный разведчик» (бесплатный)
Сервис позволяет анализировать социальную активность сайтов. Чтобы воспользоваться инструментом, достаточно ввести URL интересующих ресурсов в поле, выбрать социальные сети и нажать кнопку «Собрать».
- Be1.ru (бесплатный)
Сервис имеет основные инструменты: комплексный анализ сайта, антиплагиат, проверка позиций сайта, и дополнительные: проверка скорости интернета, проверка на наличие вирусов и т. п.
- WayBack Machine (бесплатный)
Цифровой архив Всемирной паутины. Можно отслеживать изменения сайта, а также посмотреть первоначальный вид за любую дату.
- Socialblade (бесплатный)
Сервис для анализа статистики каналов на YouTube, так можно получать данные и для Twitch, Instagram, Twitter и Dailymotion. Очень много видов подробных статистик, данные по приросту подписчиков, место в общем рейтинге и много чего другого.
- Pepper.Ninja (платный)
Сервис для тонкого таргетирования во «ВКонтакте»: помогает найти людей по тематическим записям, отыскать нужную аудиторию в других группах, отыскать сообщества по ключевым словам. Также осуществляет некоторые запросы для Instagram, Facebook.
- LiveDune (платный)
Сервис глубокой аналитики, причем не только своих социальных сетей, но и чужих. Можно осуществлять поиск блогеров и смотреть рейтинг. Информация постоянно обновляется в фоновом режиме.
Компания AVSEO всегда готова помочь вам продвинуть сайт и войти в топы поисковой выдачи.
Переходите в эру SEO вместе с нами!
Из-за ненависти к «1С» сделали внутренний сервис выставления счетов
И увеличили скорость обработки заявок на 25%. Путь компании от ненависти к «1С» и работы через Excel к созданию своего сервиса выставления счетов.
{«id»:78888,»url»:»https:\/\/vc.ru\/tribuna\/78888-iz-za-nenavisti-k-1s-sdelali-vnutrenniy-servis-vystavleniya-schetov»,»title»:»\u0418\u0437-\u0437\u0430 \u043d\u0435\u043d\u0430\u0432\u0438\u0441\u0442\u0438 \u043a \u00ab1\u0421\u00bb \u0441\u0434\u0435\u043b\u0430\u043b\u0438 \u0432\u043d\u0443\u0442\u0440\u0435\u043d\u043d\u0438\u0439 \u0441\u0435\u0440\u0432\u0438\u0441 \u0432\u044b\u0441\u0442\u0430\u0432\u043b\u0435\u043d\u0438\u044f \u0441\u0447\u0435\u0442\u043e\u0432″,»services»:{«facebook»:{«url»:»https:\/\/www.facebook.com\/sharer\/sharer.php?u=https:\/\/vc.ru\/tribuna\/78888-iz-za-nenavisti-k-1s-sdelali-vnutrenniy-servis-vystavleniya-schetov»,»short_name»:»FB»,»title»:»Facebook»,»width»:600,»height»:450},»vkontakte»:{«url»:»https:\/\/vk.com\/share.php?url=https:\/\/vc.ru\/tribuna\/78888-iz-za-nenavisti-k-1s-sdelali-vnutrenniy-servis-vystavleniya-schetov&title=\u0418\u0437-\u0437\u0430 \u043d\u0435\u043d\u0430\u0432\u0438\u0441\u0442\u0438 \u043a \u00ab1\u0421\u00bb \u0441\u0434\u0435\u043b\u0430\u043b\u0438 \u0432\u043d\u0443\u0442\u0440\u0435\u043d\u043d\u0438\u0439 \u0441\u0435\u0440\u0432\u0438\u0441 \u0432\u044b\u0441\u0442\u0430\u0432\u043b\u0435\u043d\u0438\u044f \u0441\u0447\u0435\u0442\u043e\u0432″,»short_name»:»VK»,»title»:»\u0412\u041a\u043e\u043d\u0442\u0430\u043a\u0442\u0435″,»width»:600,»height»:450},»twitter»:{«url»:»https:\/\/twitter.com\/intent\/tweet?url=https:\/\/vc.ru\/tribuna\/78888-iz-za-nenavisti-k-1s-sdelali-vnutrenniy-servis-vystavleniya-schetov&text=\u0418\u0437-\u0437\u0430 \u043d\u0435\u043d\u0430\u0432\u0438\u0441\u0442\u0438 \u043a \u00ab1\u0421\u00bb \u0441\u0434\u0435\u043b\u0430\u043b\u0438 \u0432\u043d\u0443\u0442\u0440\u0435\u043d\u043d\u0438\u0439 \u0441\u0435\u0440\u0432\u0438\u0441 \u0432\u044b\u0441\u0442\u0430\u0432\u043b\u0435\u043d\u0438\u044f \u0441\u0447\u0435\u0442\u043e\u0432″,»short_name»:»TW»,»title»:»Twitter»,»width»:600,»height»:450},»telegram»:{«url»:»tg:\/\/msg_url?url=https:\/\/vc.ru\/tribuna\/78888-iz-za-nenavisti-k-1s-sdelali-vnutrenniy-servis-vystavleniya-schetov&text=\u0418\u0437-\u0437\u0430 \u043d\u0435\u043d\u0430\u0432\u0438\u0441\u0442\u0438 \u043a \u00ab1\u0421\u00bb \u0441\u0434\u0435\u043b\u0430\u043b\u0438 \u0432\u043d\u0443\u0442\u0440\u0435\u043d\u043d\u0438\u0439 \u0441\u0435\u0440\u0432\u0438\u0441 \u0432\u044b\u0441\u0442\u0430\u0432\u043b\u0435\u043d\u0438\u044f \u0441\u0447\u0435\u0442\u043e\u0432″,»short_name»:»TG»,»title»:»Telegram»,»width»:600,»height»:450},»odnoklassniki»:{«url»:»http:\/\/connect.ok.ru\/dk?st.cmd=WidgetSharePreview&service=odnoklassniki&st.shareUrl=https:\/\/vc.ru\/tribuna\/78888-iz-za-nenavisti-k-1s-sdelali-vnutrenniy-servis-vystavleniya-schetov»,»short_name»:»OK»,»title»:»\u041e\u0434\u043d\u043e\u043a\u043b\u0430\u0441\u0441\u043d\u0438\u043a\u0438″,»width»:600,»height»:450},»email»:{«url»:»mailto:?subject=\u0418\u0437-\u0437\u0430 \u043d\u0435\u043d\u0430\u0432\u0438\u0441\u0442\u0438 \u043a \u00ab1\u0421\u00bb \u0441\u0434\u0435\u043b\u0430\u043b\u0438 \u0432\u043d\u0443\u0442\u0440\u0435\u043d\u043d\u0438\u0439 \u0441\u0435\u0440\u0432\u0438\u0441 \u0432\u044b\u0441\u0442\u0430\u0432\u043b\u0435\u043d\u0438\u044f \u0441\u0447\u0435\u0442\u043e\u0432&body=https:\/\/vc.ru\/tribuna\/78888-iz-za-nenavisti-k-1s-sdelali-vnutrenniy-servis-vystavleniya-schetov»,»short_name»:»Email»,»title»:»\u041e\u0442\u043f\u0440\u0430\u0432\u0438\u0442\u044c \u043d\u0430 \u043f\u043e\u0447\u0442\u0443″,»width»:600,»height»:450}},»isFavorited»:false}
6986 просмотров
После института я полгода продавал рекламу в журнале, и все полгода выставлял счета по инструкции из 17 пунктов и боялся ткнуть не туда среди миллиона кнопок в одном окне «1С». Мне никак не удавалось запомнить последовательность. Ещё нужно было что-то куда-то «провести», я так до конца и не понял, что это значит. В то время я возненавидел «1С» всей душой за невероятную непонятность, перегруженность и отсутствие альтернативы.
Дальше по порядку о том, как я пришёл к тому, чтобы сделать внутренний сервис выставления счетов, а «1С» использовать только для подготовки закрывающих документов и отчётности. Тяжело полностью отказаться от «1С», когда у тебя компания работает с НДС. Но хотя бы пользуется ею теперь только бухгалтер.
Мы с другом хотели открыть бизнес, но денег у нас (на самом деле у него) было только на открытие ООО. А у челябинцев есть такая традиция: если хочешь открыть бизнес, но у тебя нет денег — ты начинаешь заниматься перепродажей металлопроката. Мы не стали нарушать эту традицию, так у нас появился бизнес.
Денег на бухгалтера не было, поэтому мы решили что наймём его через пару месяцев, ближе к отчётному периоду, а счета, товарные накладные и счета-фактуры пока будем делать в Excel. Это потом выяснилось, что мы всем клиентами делали неправильные документы, потому что я указал ошибочную формулу расчёта НДС.
В итоге мы наняли бухгалтера, она начала делать закрывающие документы в «1С», а мы продолжили выставлять счета через Excel (формулу НДС я поправил, всё ок).
Первые проблемы с эффективностью
Вскоре мы столкнулись с ограничением: много входящих заявок и менеджеры не успевают их обрабатывать. Радоваться тут нечему, особенность нашего рынка такая, что оплачивается в лучшем случае каждый десятый счёт. Так бывает, когда ты продаёшь то же самое, что у всех, по цене чуть дороже, чем у большинства. Тут как в фильме «В погоне за счастьем», где Уилл Смит продавал аппарат, который «чуть хуже рентгена, зато стоит в два раза дороже».
Вместо набора новых менеджеров я начал смотреть в сторону ускорения работы отдела продаж. Непаханое поле. Первым делом мы собрали прайсы всех поставщиков в единую базу, это сильно упростило поиск. Раньше менеджеры вручную искали в папке, в которой было больше 150 прайсов, по несколько сотен строк в каждом.
Наша база поставщиков
Следом взялись за выставление счетов. На тот момент процедура выглядела так:
Создать папку и назвать её именем клиента.
Скопировать в эту папку шаблон счёта.
Заполнить все позиции, добавить строки по необходимости.
Проверить показатели и без орфографических ошибок написать расшифровку суммы.
Спросить у руководителя продаж, какой поставить номер счёта.
Сохранить в PDF и отправить покупателю.
Эффективность труда зашкаливает… А учитывая, что оплачивается каждый десятый счёт — ситуация меня совсем угнетала.
Поиск существующих решений
Как у любого бизнеса, у нас есть свои особенности:
- Часто из реквизитов известно только название компании, а иногда и этого нет.
- Мы перекупы, у нас нет единого каталога товаров, часто появляются новые позиции.
- Нет цен, менеджеры закладывают прибыль самостоятельно в зависимости от стоимости закупа, количества и прочего.
Поиск среди существующих сервисов с учётом этих особенностей результатов не дал. Ни один нам не подошёл.
- Некоторые делались людьми, которым не приходилось самостоятельно выставлять счета, иначе я это объяснить не могу.
- Была пара хороших, но они ориентировались на ИП-шников и небольшие компании на УСН, в которых только один человек занимается счетами. И они больше про ведение отчётности, чем про эффективность отдела продаж.
- Большинство требовали заполнить все реквизиты клиента и заранее завести все товары. Что тоже нам не подошло.
- «1С» принципиально нет.
Делаем своё решение
На рынке металлопроката иногда приходится ждать часами чтобы получить счёт от поставщика. Мы поставили себе задачу, что покупатель должен получить счёт уже во время первого телефонного разговора с менеджером. Я предположил, что помимо эффективности отдела это должно плодотворно сказаться и на конверсии в продажи.
За пару месяцев нам удалось добиться нужного результата. Мы сделали простой инструмент для выставления счетов и удачно «поженили» с нашей базой прайсов с уже более 200 поставщиков. Не представляю, как бы мы это интегрировали в сторонние сервисы.
Как обработка заявок работает сейчас:
Окно ввода заявки
- Менеджер вводит компанию и характеристики запрашиваемых товаров. Нажимает на кнопку «Рассчитать».
- Открывается окно, в котором уже заполнены все данные счёта, выбран по алгоритму оптимальный поставщик, заложена рекомендуемая прибыль.
- Менеджер проверяет данные, по необходимости редактирует, в том же окне вводит email покупателя и отправляет счёт.
Окно редактирования счёта и отправки
Оплаченные счета выгружаются бухгалтеру, она делает закрывающие документы и радуется, что её («прелесть») «1С» не засоряется менеджерами и только она владычествует над всем.
Результат
За почти год работы в таком формате мы выставили более 10 тысяч счетов. В итоге:
- В несколько раз сократилось время обработки заявок и выставления счёта. Нет лишних функций, кнопок и действий.
- Сократилось время обучения новых сотрудников. Возможность ошибки сведена к минимуму. Они начинают выставлять счета в «боевом» режиме уже на следующий день, как только начнут отличать круглую трубу от квадратной.
- То же количество менеджеров стало обрабатывать на 25% больше заявок.
- Клиентам оказалось плевать, за две минуты они получили счёт или через несколько часов. На конверсию в продажи это никак не повлияло.
Внедрение этой штуки повысило эффективность отдела продаж. Иначе мне пришлось бы нанимать двух лишних менеджеров, оборудовать рабочие места и платить зарплаты. При том же количестве менеджеров мы стали обрабатывать больше заявок, так что результатом я доволен.
Самостоятельный сервис
Недавно показывал товарищу, как это всё работает, и у нас получился вот такой диалог:
— Ты пытался это кому-то продать?
— Нет, это же для внутренних нужд делалось.
— А много компаний, которые хотят слезть с «1С» или у кого похожая модель продаж и они хотят ускорить какие-то процессы?
— Не знаю, наверняка.
— У тебя почти готовый самостоятельный сервис для выставления счетов, чё ты его не сделаешь до конца?
Почему бы и нет. В России 8 млн компаний и ИП, среди которых можно поискать свою аудиторию. Например, таких же перекупов, как и мы.
Сейчас мы делаем MVP. В сентябре планируем показать первую версию с базовой функциональностью.
Все похожие сервисы идут в сторону управления бухгалтерией и отправки отчётности. Кто-то уже вовсю интегрируется с банками. Мне больше интересна задача эффективности и автоматизации процессов в отделах продаж. Интеграции с CRM, а не с банками. Не знаю пока, что из этого всего получится, будем пробовать.
Вопрос к читателям vc.ru
Были у кого-то похожие боли в компании? На что нам стоит обратить внимание в первую очередь?
P.S. У нас пока сделан только сбор контактов, чтобы уведомить о запуске. Кому интересен сервис или кто просто хочет помочь на этапе тестирования, вот ссылка на страницу.
Словарь терминов SEM-специалиста — !SEMTools для Excel
Данный словарь содержит термины, часто используемые специалистами по поисковому маркетингу — специалистами по контекстной рекламе и SEO.
Знание данных терминов ускоряет понимание профильных статей, а употребление вслух, в соцсетях или на других площадках закрепляет за вами имидж сведущего специалиста. Тем не менее, знание терминов не должно становиться самоцелью, а лишь помогать постигать новое в профессии.
A
A/B тестирование
AdSense
AdWords
AOV, average order value
B
Bounce rate
C
CAC, customer aquisition cost
Call tracking
CPC, cost per click
CPM, cost per mile
CPV, cost per view
CR, conversion rate
CTA, Call to action
CTA-элемент
CTR, click-through rate
D
DSA, Dynamic search ads
Drilldown
E
eCPC стратегия
F
Funnel-based
G
GDN, Google display network
GSP-аукцион
GTM
H
h2
I
Impression share
K
K-means clustering
KPI, key performance indicator
L
LSI, Latent semantic indexing
LTV, Lifetime value
LT, Life time
N
N-грамма
N-граммное расстояние
O
On-page SEO
P
PPC
Q
QS
R
RLSA
ROI
ROAS, показатель и стратегия
ROMI
S
SEM
SEO
SERM
SERP
Sitemap
T
Title
U
UTM-метка
UX
V
VCG-аукцион
Viewable impression
VTR
А
Автотаргетинг
Ассоциированные конверсии
Алгоритмы поисковых систем
Апдейт
Атрибуция на основе воронки
Атрибуция по последнему значимому клику
Атрибуция по последнему клику
Атрибуция, взвешенная через хиты
Аффилиат
Б
Баден-Баден
Биддер
Биддинг
Брендовый запрос
Брендовый трафик
Быстрые ссылки
В
Вебвизор
Внешняя оптимизация (SEO)
Внутренняя оптимизация (SEO)
Вордстат
Воронка
Временной таргетинг
Г
Геозависимость
Геомаркеры
Геотаргетинг
Д
Дедупликация
Дескрипшн
Динамические поисковые объявления
Динамический ремаркетинг
Дорвей
Дрилдаун
ДРР
З
Заголовок
И
ИКС, индекс качества сайта
Индексация
Искренняя цена за клик
К
Каннибализация
Карта сайта
Кластеризация
Кликабельность
Кликбейт
Клики
Ключевик
Ключевое слово
Ключевой показатель эффективности
КМС
Колдунщик
Коллтрекинг
Коммерциализация запроса
Конверсии по показам
Конкурентность
Контекстная реклама
Контекстно-медийная сеть
Копирайтинг
Косинусное расстояние
Коэффициент конверсии
Креатив
Кросс-девайс
Л
Латентно-семантическое индексирование
Лемматизация
Лендинг
Лид
М
Мастер отчетов
Место размещения
Метатег
Метод K-ближайших соседей
Метрика
Минус-слова, минус-фразы
Минусинск
Многоканальные последовательности
Модель атрибуции
Морфологическая парадигма
Н
Неявные дубли
Низкочастотное ключевое слово
Нормализация
О
Омонимия
Оптимизация текста
Операторы Яндекс.Директ
Операторы Google Ads
П
Парсер
Поведенческие показатели
Поведенческий таргетинг
Поисковая выдача
Поисковая оптимизация
Поисковые подсказки
Поисковый запрос
Показатель качества ключевого слова (Google)
Показатель качества аккаунта (Яндекс)
Показатель отказов
Показы
Пользователь
Пользовательский интент
Посадочная страница
Посетитель
Пословное расстояние
Призыв к действию
Прогноз бюджета
Прогнозатор бюджета
Пузомерки
Пулинг
Р
Расстояние Левенштейна
Расширенный геотаргетинг
Рекламная сеть Яндекса
Релевантность
Ремаркетинг
Ретаргетинг
РСЯ
С
Сателлит
Сеанс
Сезональность
Сезонность
Семантика
Семантический анализ
Семантическое ядро
Сессия
Синонимы
Сквозная аналитика
Скрейпер
Слово-маркер
Словоформа
Случайная ошибка выжившего
Спам
Спецразмещение (блок)
Средний чек
Срез
Ссылка
Ссылочная масса
Ссылочное ранжирование
Статистическая достоверность
Стоп-слова
Стоп-слова Яндекс Директ
Съём позиций
Т
Тайтл
Таргетинг
Техническая оптимизация (SEO)
Тип соответствия ключевого слова
Топонимы
Транзационные маркеры
Ф
Фид
Х
Хит
Ц
Ценность конверсии
Ч
Частотность
Ю-Я
Юзабилити
Яндекс.Вебмастер
Яндекс.Вордстат
Яндекс.Директ
Яндекс.Маркет
Яндекс.Метрика
Яндекс.XML
Учёным пришлось переименовать 27 человеческих генов, чтобы Excel автоматически не конвертировал их названия в даты Статьи редакции
{«id»:185501,»url»:»https:\/\/dtf.ru\/life\/185501-uchenym-prishlos-pereimenovat-27-chelovecheskih-genov-chtoby-excel-avtomaticheski-ne-konvertiroval-ih-nazvaniya-v-daty»,»title»:»\u0423\u0447\u0451\u043d\u044b\u043c \u043f\u0440\u0438\u0448\u043b\u043e\u0441\u044c \u043f\u0435\u0440\u0435\u0438\u043c\u0435\u043d\u043e\u0432\u0430\u0442\u044c 27 \u0447\u0435\u043b\u043e\u0432\u0435\u0447\u0435\u0441\u043a\u0438\u0445 \u0433\u0435\u043d\u043e\u0432, \u0447\u0442\u043e\u0431\u044b Excel \u0430\u0432\u0442\u043e\u043c\u0430\u0442\u0438\u0447\u0435\u0441\u043a\u0438 \u043d\u0435 \u043a\u043e\u043d\u0432\u0435\u0440\u0442\u0438\u0440\u043e\u0432\u0430\u043b \u0438\u0445 \u043d\u0430\u0437\u0432\u0430\u043d\u0438\u044f \u0432 \u0434\u0430\u0442\u044b»,»services»:{«vkontakte»:{«url»:»https:\/\/vk.com\/share.php?url=https:\/\/dtf.ru\/life\/185501-uchenym-prishlos-pereimenovat-27-chelovecheskih-genov-chtoby-excel-avtomaticheski-ne-konvertiroval-ih-nazvaniya-v-daty&title=\u0423\u0447\u0451\u043d\u044b\u043c \u043f\u0440\u0438\u0448\u043b\u043e\u0441\u044c \u043f\u0435\u0440\u0435\u0438\u043c\u0435\u043d\u043e\u0432\u0430\u0442\u044c 27 \u0447\u0435\u043b\u043e\u0432\u0435\u0447\u0435\u0441\u043a\u0438\u0445 \u0433\u0435\u043d\u043e\u0432, \u0447\u0442\u043e\u0431\u044b Excel \u0430\u0432\u0442\u043e\u043c\u0430\u0442\u0438\u0447\u0435\u0441\u043a\u0438 \u043d\u0435 \u043a\u043e\u043d\u0432\u0435\u0440\u0442\u0438\u0440\u043e\u0432\u0430\u043b \u0438\u0445 \u043d\u0430\u0437\u0432\u0430\u043d\u0438\u044f \u0432 \u0434\u0430\u0442\u044b»,»short_name»:»VK»,»title»:»\u0412\u041a\u043e\u043d\u0442\u0430\u043a\u0442\u0435″,»width»:600,»height»:450},»facebook»:{«url»:»https:\/\/www.facebook.com\/sharer\/sharer.php?u=https:\/\/dtf.ru\/life\/185501-uchenym-prishlos-pereimenovat-27-chelovecheskih-genov-chtoby-excel-avtomaticheski-ne-konvertiroval-ih-nazvaniya-v-daty»,»short_name»:»FB»,»title»:»Facebook»,»width»:600,»height»:450},»twitter»:{«url»:»https:\/\/twitter.com\/intent\/tweet?url=https:\/\/dtf.ru\/life\/185501-uchenym-prishlos-pereimenovat-27-chelovecheskih-genov-chtoby-excel-avtomaticheski-ne-konvertiroval-ih-nazvaniya-v-daty&text=\u0423\u0447\u0451\u043d\u044b\u043c \u043f\u0440\u0438\u0448\u043b\u043e\u0441\u044c \u043f\u0435\u0440\u0435\u0438\u043c\u0435\u043d\u043e\u0432\u0430\u0442\u044c 27 \u0447\u0435\u043b\u043e\u0432\u0435\u0447\u0435\u0441\u043a\u0438\u0445 \u0433\u0435\u043d\u043e\u0432, \u0447\u0442\u043e\u0431\u044b Excel \u0430\u0432\u0442\u043e\u043c\u0430\u0442\u0438\u0447\u0435\u0441\u043a\u0438 \u043d\u0435 \u043a\u043e\u043d\u0432\u0435\u0440\u0442\u0438\u0440\u043e\u0432\u0430\u043b \u0438\u0445 \u043d\u0430\u0437\u0432\u0430\u043d\u0438\u044f \u0432 \u0434\u0430\u0442\u044b»,»short_name»:»TW»,»title»:»Twitter»,»width»:600,»height»:450},»telegram»:{«url»:»tg:\/\/msg_url?url=https:\/\/dtf.ru\/life\/185501-uchenym-prishlos-pereimenovat-27-chelovecheskih-genov-chtoby-excel-avtomaticheski-ne-konvertiroval-ih-nazvaniya-v-daty&text=\u0423\u0447\u0451\u043d\u044b\u043c \u043f\u0440\u0438\u0448\u043b\u043e\u0441\u044c \u043f\u0435\u0440\u0435\u0438\u043c\u0435\u043d\u043e\u0432\u0430\u0442\u044c 27 \u0447\u0435\u043b\u043e\u0432\u0435\u0447\u0435\u0441\u043a\u0438\u0445 \u0433\u0435\u043d\u043e\u0432, \u0447\u0442\u043e\u0431\u044b Excel \u0430\u0432\u0442\u043e\u043c\u0430\u0442\u0438\u0447\u0435\u0441\u043a\u0438 \u043d\u0435 \u043a\u043e\u043d\u0432\u0435\u0440\u0442\u0438\u0440\u043e\u0432\u0430\u043b \u0438\u0445 \u043d\u0430\u0437\u0432\u0430\u043d\u0438\u044f \u0432 \u0434\u0430\u0442\u044b»,»short_name»:»TG»,»title»:»Telegram»,»width»:600,»height»:450},»odnoklassniki»:{«url»:»http:\/\/connect.ok.ru\/dk?st.cmd=WidgetSharePreview&service=odnoklassniki&st.shareUrl=https:\/\/dtf.ru\/life\/185501-uchenym-prishlos-pereimenovat-27-chelovecheskih-genov-chtoby-excel-avtomaticheski-ne-konvertiroval-ih-nazvaniya-v-daty»,»short_name»:»OK»,»title»:»\u041e\u0434\u043d\u043e\u043a\u043b\u0430\u0441\u0441\u043d\u0438\u043a\u0438″,»width»:600,»height»:450},»email»:{«url»:»mailto:?subject=\u0423\u0447\u0451\u043d\u044b\u043c \u043f\u0440\u0438\u0448\u043b\u043e\u0441\u044c \u043f\u0435\u0440\u0435\u0438\u043c\u0435\u043d\u043e\u0432\u0430\u0442\u044c 27 \u0447\u0435\u043b\u043e\u0432\u0435\u0447\u0435\u0441\u043a\u0438\u0445 \u0433\u0435\u043d\u043e\u0432, \u0447\u0442\u043e\u0431\u044b Excel \u0430\u0432\u0442\u043e\u043c\u0430\u0442\u0438\u0447\u0435\u0441\u043a\u0438 \u043d\u0435 \u043a\u043e\u043d\u0432\u0435\u0440\u0442\u0438\u0440\u043e\u0432\u0430\u043b \u0438\u0445 \u043d\u0430\u0437\u0432\u0430\u043d\u0438\u044f \u0432 \u0434\u0430\u0442\u044b&body=https:\/\/dtf.ru\/life\/185501-uchenym-prishlos-pereimenovat-27-chelovecheskih-genov-chtoby-excel-avtomaticheski-ne-konvertiroval-ih-nazvaniya-v-daty»,»short_name»:»Email»,»title»:»\u041e\u0442\u043f\u0440\u0430\u0432\u0438\u0442\u044c \u043d\u0430 \u043f\u043e\u0447\u0442\u0443″,»width»:600,»height»:450}},»isFavorited»:false}
16 036 просмотров
Как извлечь подстроку в Excel (с использованием текстовых формул)
В Excel есть набор ТЕКСТОВЫХ функций, которые могут творить чудеса. С помощью этих функций вы можете выполнять все виды операций с фрагментами текста и кубиками.
Одной из распространенных задач людей, работающих с текстовыми данными, является извлечение подстроки в Excel (т.е. получение psrt текста из ячейки).
К сожалению, в Excel нет функции подстроки, которая могла бы сделать это легко. Однако это все еще можно сделать с помощью текстовых формул, а также некоторых других встроенных функций Excel.
Давайте сначала взглянем на некоторые текстовые функции, которые мы будем использовать в этом руководстве.
Excel ТЕКСТОВЫЕ ФУНКЦИИ
Excel имеет ряд текстовых функций, которые позволяют очень легко извлечь подстроку из исходного текста в Excel. Вот текстовые функции Excel, которые мы будем использовать в этом руководстве:
- ПРАВАЯ функция: извлекает указанное количество символов справа от текстовой строки.
- Функция LEFT: извлекает указанное количество символов слева от текстовой строки.
- Функция MID: извлекает указанное количество символов из указанной начальной позиции в текстовой строке.
- Функция НАЙТИ: Находит начальную позицию указанного текста в текстовой строке.
- Функция LEN: возвращает количество символов в текстовой строке.
Извлечение подстроки в Excel с использованием функций
Предположим, у вас есть набор данных, показанный ниже:
Это несколько случайных (но супергеройских) идентификаторов электронной почты (кроме моего), и в приведенных ниже примерах я ‘ Я покажу вам, как извлечь имя пользователя и имя домена с помощью текстовых функций в Excel.
Пример 1. Извлечение имен пользователей из идентификаторов электронной почтыПри использовании текстовых функций важно идентифицировать шаблон (если таковой имеется). Это упрощает построение формулы. В приведенном выше случае шаблон представляет собой знак @ между именем пользователя и именем домена, и мы будем использовать его в качестве ссылки для получения имен пользователей.
Вот формула для получения имени пользователя:
= LEFT (A2, FIND ("@", A2) -1) В приведенной выше формуле используется функция LEFT для извлечения имени пользователя путем определения положения знака @ в id.Это делается с помощью функции НАЙТИ, которая возвращает позицию символа @.
Например, в случае [email protected] FIND («@», A2) вернет 11, что является его позицией в текстовой строке.
Теперь мы используем функцию LEFT для извлечения 10 символов слева от строки (на один меньше значения, возвращаемого функцией LEFT).
Пример 2 — Извлечение имени домена из идентификаторов электронной почтыТа же логика, что и в приведенном выше примере, может использоваться для получения имени домена.Небольшое отличие здесь в том, что нам нужно извлекать символы справа от текстовой строки.
Вот формула, которая сделает это:
= RIGHT (A2, LEN (A2) -FIND ("@", A2)) В приведенной выше формуле мы используем ту же логику, но скорректируем ее. чтобы убедиться, что мы получаем правильную строку.
Давайте снова возьмем пример с [email protected]. Функция НАЙТИ возвращает положение знака @, которое в данном случае равно 11. Теперь нам нужно извлечь все символы после @.Таким образом, мы определяем общую длину строки и вычитаем количество символов до символа @. Он дает нам количество символов, которые покрывают доменное имя справа.
Теперь мы можем просто использовать функцию ВПРАВО, чтобы получить имя домена.
Пример 3 — Извлечение доменного имени из идентификаторов электронной почты (без .com)Чтобы извлечь подстроку из середины текстовой строки, вам необходимо определить положение маркера непосредственно перед и после подстроки.
Например, в приведенном ниже примере, чтобы получить доменное имя без части .com, маркером будет @ (который стоит прямо перед доменным именем) и. (что сразу после него).
Вот формула, которая будет извлекать только имя домена:
= MID (A2, FIND ("@", A2) + 1, FIND (".", A2) -FIND ("@", A2) - 1) Функция Excel MID извлекает указанное количество символов из указанной начальной позиции. В приведенном выше примере FIND («@», A2) +1 указывает начальную позицию (которая находится сразу после @), а FIND («.», A2) -FIND (« @ », A2) -1 определяет количество символов между« @ »и« . ‘
Обновление: Один из читателей William19 упомянул, что приведенная выше формула не будет работать, если в идентификаторе электронной почты есть точка (.) (Например, [email protected]). Вот формула для таких случаев:
= MID (A1, FIND ("@", A1) + 1, FIND (".", A1, FIND ("@", A1)) - FIND (" @ ", A1) -1) Использование текста в столбцы для извлечения подстроки в Excel
Использование функций для извлечения подстроки в Excel имеет то преимущество, что является динамическим.Если вы измените исходный текст, формула автоматически обновит результаты.
Если это то, что вам может не понадобиться, то использование функции Text to Columns может быть быстрым и простым способом разбить текст на подстроки на основе указанных маркеров.
Вот как это сделать:
Это мгновенно даст вам два набора подстрок для каждого идентификатора электронной почты, используемого в этом примере.
Если вы хотите дополнительно разделить текст (например, разделить batman.com на batman и com), повторите с ним тот же процесс.
Использование НАЙТИ и ЗАМЕНИТЬ для извлечения текста из ячейки в Excel
НАЙТИ и ЗАМЕНИТЬ может быть мощным методом при работе с текстом в Excel. В приведенных ниже примерах вы узнаете, как использовать НАЙТИ и ЗАМЕНИТЬ с подстановочными знаками, чтобы делать удивительные вещи в Excel.
См. Также: Узнайте все о подстановочных знаках в Excel.
Давайте возьмем те же примеры идентификаторов электронной почты.
Пример 1 — Извлечение имен пользователей из идентификаторов электронной почтыВот шаги по извлечению имен пользователей из идентификаторов электронной почты с помощью функции поиска и замены:
- Скопируйте и вставьте исходные данные.Поскольку функция «Найти и заменить» работает и изменяет данные, к которым она применяется, лучше всего иметь резервную копию исходных данных.
- Выберите данные и перейдите в раздел «Главная» -> «Редактирование» -> «Найти и выделить» -> «Заменить» (или используйте сочетание клавиш Ctrl + H).
- В диалоговом окне «Найти и заменить» введите следующее:
- Найдите что: @ *
- Заменить на: (оставьте это поле пустым)
- Нажмите «Заменить все».
Это мгновенно удалит весь текст перед @ в идентификаторах электронной почты.Вы получите результат, как показано ниже:
Как это работает ?? — В приведенном выше примере мы использовали комбинацию @ и *. Звездочка (*) — это подстановочный знак, обозначающий любое количество символов. Следовательно, @ * будет означать текстовую строку, которая начинается с @ и может иметь любое количество символов после нее. Например, в [email protected] @ * будет @ batman.com. Когда мы заменяем @ * пробелом, удаляются все символы после @ (включая @).
Пример 2 — Извлечение имени домена из идентификаторов электронной почтыИспользуя ту же логику, вы можете изменить критерии «Найти», чтобы получить имя домена.
Вот шаги:
- Выберите данные.
- Перейдите на главную -> Редактирование -> Найти и выбрать -> Заменить (или используйте сочетание клавиш Ctrl + H).
- В диалоговом окне «Найти и заменить» введите следующее:
- Найдите что: * @
- Заменить на: (оставьте это поле пустым)
- Нажмите «Заменить все».
Это мгновенно удалит весь текст перед @ в идентификаторах электронной почты. У вас будет результат, как показано ниже:
Вам также могут понравиться следующие учебные пособия по Excel:
Комбинация функций ИНДЕКС и ПОИСКПОЗ в Excel (10 простых примеров)
Excel имеет множество функций — около 450 + из них.
И многие из них просто потрясающие. Объем работы, которую вы можете выполнить с помощью нескольких формул, все еще удивляет меня (даже после того, как вы использовали Excel более 10 лет).
Среди всех этих удивительных функций выделяется комбинация функций ИНДЕКС ПОИСКПОЗ.
Я большой поклонник комбинации ИНДЕКС МАТЧ и много раз разъяснял ее.
Я даже написал статью о сопоставлении индекса и ВПР, которая вызвала небольшие споры (вы можете проверить раздел комментариев, чтобы увидеть фейерверк).
И сегодня я пишу эту статью, посвященную исключительно Index Match, чтобы показать вам несколько простых и сложных сценариев, в которых вы можете использовать эту мощную комбинацию формул и выполнить свою работу.
Примечание: В Excel есть и другие формулы поиска, такие как ВПР и ГПР, и они великолепны. Многие люди считают, что ВПР проще в использовании (и это тоже правда). Я считаю, что INDEX MATCH — лучший вариант во многих случаях. Но так как людям это сложно, к нему все реже привыкают. Поэтому я пытаюсь упростить его с помощью этого урока.Теперь, прежде чем я покажу вам, как комбинация INDEX MATCH меняет мир аналитиков и исследователей данных, позвольте мне сначала познакомить вас с отдельными частями — функциями INDEX и MATCH.
ИНДЕКС Функция: поиск значений на основе координат
Самый простой способ понять, как работает функция «Индекс», — это подумать о ней как о спутнике GPS.
Как только вы сообщите спутнику координаты широты и долготы, он будет точно знать, куда идти и найти это место.
Итак, несмотря на ошеломляющее количество комбинаций широты и долготы, спутник точно знает, где искать.
Я быстро нашел место работы, и вот что я нашел.
Все равно географии хватит.
Так же, как спутнику требуются координаты широты и долготы, функции ИНДЕКС в Excel потребуется номер строки и столбца, чтобы знать, о какой ячейке вы говорите.
И это функция ИНДЕКС Excel в кратком изложении.
Итак, позвольте мне дать вам определение простыми словами.
Функция ИНДЕКС будет использовать номер строки и номер столбца, чтобы найти ячейку в заданном диапазоне и вернуть значение в ней.
Сам по себе ИНДЕКС — очень простая функция, не имеющая никакой полезности.В конце концов, в большинстве случаев вы вряд ли знаете номера строк и столбцов.
Но…
Тот факт, что вы можете использовать его с другими функциями (подсказка: ПОИСКПОЗ), которые могут найти номер строки и номер столбца, делает ИНДЕКС чрезвычайно мощной функцией Excel.
Ниже приведен синтаксис функции ИНДЕКС:
= ИНДЕКС (массив, номер_строки, [номер_столбца]) = ИНДЕКС (массив; номер_строки; [номер_столбца]; [номер_площади])
- массив — диапазон ячеек или константа массива.
- row_num — номер строки, из которой должно быть извлечено значение.
- [col_num] — номер столбца, из которого должно быть извлечено значение. Хотя это необязательный аргумент, но если row_num не указан, его необходимо указать.
- [area_num] — (Необязательно) Если аргумент массива состоит из нескольких диапазонов, это число будет использоваться для выбора ссылки из всех диапазонов.
Функция ИНДЕКС имеет 2 синтаксиса (только FYI).
В большинстве случаев используется первый. Второй используется только в сложных случаях (например, при трехстороннем поиске), который мы рассмотрим в одном из примеров далее в этом руководстве.
Но если вы новичок в этой функции, просто запомните первый синтаксис.
Ниже приведено видео, в котором объясняется, как использовать функцию ИНДЕКС.
Функция ПОИСКПОЗ: поиск позиции, привязанной к искомому значению
Возвращаясь к моему предыдущему примеру долготы и широты, ПОИСКПОЗ — это функция, которая может найти эти позиции (в мире электронных таблиц Excel).
Простым языком функция ПОИСКПОЗ в Excel может найти положение ячейки в диапазоне.
А по какому принципу он определит положение ячейки?
На основе поискового значения.
Например, если у вас есть список, показанный ниже, и вы хотите найти в нем позицию имени «Отметить», то вы можете использовать функцию ПОИСКПОЗ.
Функция возвращает 3, так как это позиция ячейки с именем Mark в ней.
Функция ПОИСКПОЗ начинает поиск сверху вниз для поискового значения (которым является «Отметка») в указанном диапазоне (в данном примере это A1: A9).Как только он находит имя, он возвращает позицию в этом конкретном диапазоне.
Ниже приведен синтаксис функции ПОИСКПОЗ в Excel.
= MATCH (lookup_value, lookup_array, [match_type])
- lookup_value — Значение, для которого вы ищете совпадение в lookup_array.
- lookup_array — Диапазон ячеек, в котором вы ищете lookup_value.
- [match_type] — (Необязательно) Указывает, как Excel должен искать совпадающее значение.Она может принимать три значения: -1, 0 или 1.
Объяснение аргумента типа соответствия в функции MATCH
Есть еще одна вещь, которую вам нужно знать о функции MATCH, и она о том, как она просматривает данные и находит положение ячейки.
Третий аргумент функции ПОИСКПОЗ может быть 0, 1 или -1.
Ниже приводится объяснение того, как работают эти аргументы:
- 0 — это будет искать точное совпадение значения. Если найдено точное совпадение, функция ПОИСКПОЗ вернет позицию ячейки.В противном случае он вернет ошибку.
- 1 — находит наибольшее значение, которое меньше или равно искомому значению. Чтобы это работало, диапазон данных необходимо отсортировать в порядке возрастания.
- -1 — находит наименьшее значение, которое больше или равно искомому значению. Чтобы это работало, диапазон данных необходимо отсортировать в порядке убывания.
Ниже приведено видео, в котором объясняется, как использовать функцию ПОИСКПОЗ (вместе с аргументом типа соответствия)
Чтобы подвести итог и выразить это простыми словами:
- ИНДЕКС требуется позиция ячейки (строка и столбец число) и дает значение ячейки.
- MATCH находит позицию, используя поисковое значение.
Давайте объединим их, чтобы создать мощный центр (INDEX + MATCH)
Теперь, когда у вас есть базовое понимание того, как функции INDEX и MATCH работают по отдельности, давайте объединим эти две функции и узнаем обо всех замечательных вещах, которые она может делать.
Чтобы лучше понять это, у меня есть несколько примеров, в которых используется комбинация INDEX MATCH.
Я начну с простого примера, а затем покажу вам несколько расширенных вариантов использования.
Щелкните здесь, чтобы загрузить файл примера.
Пример 1. Простой поиск с использованием комбинации INDEX MATCH
Давайте сделаем простой поиск с помощью INDEX / MATCH.
Ниже приведена таблица, в которой у меня есть оценки для десяти студентов.
Из этой таблицы я хочу найти оценки для Джима.
Ниже приведена формула, которая легко может сделать это:
= ИНДЕКС (2 доллара США: 11 долларов США B, MATCH ("Джим", 2 доллара США: 11,0 доллара США), 2) Теперь, Если вы думаете, что это легко сделать с помощью функции ВПР, вы правы! Это не лучшее использование потрясающих возможностей INDEX MATCH.Несмотря на то, что я фанат INDEX MATCH, это немного сложнее, чем VLOOKUP. Если все, что вам нужно сделать, это получить данные из столбца справа, я рекомендую вам использовать ВПР.
Причина, по которой я показал этот пример, который также легко можно сделать с помощью ВПР, — показать вам, как работает INDEX MATCH в простой настройке.
Теперь позвольте мне показать преимущество INDEX MATCH.
Предположим, у вас есть те же данные, но они хранятся не в столбцах, а в строках (как показано ниже).
Знаете что, вы все равно можете использовать комбинацию INDEX MATCH, чтобы получить оценки Джима.
Ниже приведена формула, которая даст вам результат:
= ИНДЕКС ($ B $ 1: $ K $ 2,2, MATCH («Джим», $ B $ 1: $ K $ 1,0))
Примечание. что вам нужно изменить диапазон и переключить части строки / столбца, чтобы эта формула работала и для горизонтальных данных.
Это невозможно сделать с помощью ВПР, но вы все равно можете легко сделать это с помощью ГПР.
Комбинация INDEX MATCH может легко обрабатывать как горизонтальные, так и вертикальные данные.
Щелкните здесь, чтобы загрузить файл примера.
Пример 2: Поиск слева
Это встречается чаще, чем вы думаете.
Часто может потребоваться выборка данных из столбца, который находится слева от столбца, имеющего значение поиска.
Пример, показанный ниже:
Чтобы узнать о продажах Майкла, вам нужно выполнить поиск слева.
Если вы думаете о ВПР, позвольте мне остановиться прямо здесь.
ВПР не предназначена для поиска и получения значений слева.
Можно еще сделать это с помощью ВПР?
Да, можно!
Но это может превратиться в длинную и уродливую формулу.
Итак, если вы хотите выполнить поиск и получить данные из столбцов слева, вам лучше использовать комбинацию INDEX MATCH.
Ниже приведена формула, по которой будет получен торговый номер Майкла:
= ИНДЕКС (2 доллара США: 11 долларов США, ПОИСКПОЗ («Майкл», C2: C11,0), 2)
Еще один пункт для ИНДЕКС МАТЧ.Функция VLOOKUP может извлекать данные только из столбцов, которые находятся справа от столбца, имеющего значение подстановки.
Пример 3: двусторонний поиск
До сих пор мы видели примеры, в которых мы хотели получить данные из столбца, смежного со столбцом, имеющим значение поиска.
Но в реальной жизни данные часто занимают несколько столбцов.
INDEX MATCH может легко обрабатывать двусторонний поиск.
Ниже представлены данные об оценках учащихся по трем различным предметам.
Если вы хотите быстро получить оценки учащегося по всем трем предметам, вы можете сделать это с помощью INDEX MATCH.
Приведенная ниже формула даст вам оценки Джиму для всех трех предметов (скопируйте и вставьте в одну ячейку и перетащите, чтобы заполнить другие ячейки, или скопируйте и вставьте в другие ячейки).
= ИНДЕКС ($ B $ 2: $ D $ 11, ПОИСКПОЗ ($ F $ 3, $ A $ 2: $ A $ 11,0), MATCH (G $ 2, $ B $ 1: $ D $ 1,0))
Позвольте мне также быстро пояснить эту формулу.
В формуле ИНДЕКС в качестве диапазона используется B2: D11.
Первое ПОИСКПОЗ использует имя (Джим в ячейке F3) и выбирает его позицию в столбце имен (A2: A11). Он становится номером строки, из которой необходимо извлечь данные.
Вторая формула ПОИСКПОЗ использует имя субъекта (в ячейке G2), чтобы получить позицию этого конкретного имени субъекта в B1: D1. Например, математика — 1, физика — 2, а химия — 3.
Поскольку эти позиции ПОИСКПОЗ передаются в функцию ИНДЕКС, она возвращает оценку на основе имени ученика и имени предмета.
Эта формула является динамической, что означает, что если вы измените имя учащегося или имена субъектов, она все равно будет работать и получать правильные данные.
Одна замечательная вещь в использовании ИНДЕКС / ПОИСКПОЗ состоит в том, что даже если вы поменяете имена испытуемых местами, это будет продолжать давать правильный результат.
Пример 4: Поиск значения из нескольких столбцов / критериев
Предположим, у вас есть набор данных, как показано ниже, и вы хотите получить метки для «Mark Long».
Так как данные находятся в двух столбцах, я не могу найти Марка и получить данные.
Если я сделаю это таким образом, я собираюсь получить данные оценок для Марка Фроста, а не для Марка Лонга (потому что функция ПОИСКПОЗ даст мне результат для МЕТКИ, которой она соответствует).
Один из способов сделать это — создать вспомогательный столбец и объединить имена. Когда у вас есть вспомогательный столбец, вы можете использовать ВПР и получать данные о отметках.
Если вы хотите узнать, как это сделать, прочтите это руководство по использованию ВПР с несколькими критериями.
Но с комбинацией ИНДЕКС / ПОИСКПОЗ вам не нужен вспомогательный столбец.Вы можете создать формулу, которая обрабатывает несколько критериев в самой формуле.
Следующая формула даст результат.
= ИНДЕКС ($ C $ 2: $ C $ 11, ПОИСКПОЗ ($ E $ 3 & "|" & $ F $ 3, $ A $ 2: A11 & "|" & $ B $ 2: $ B $ 11,0))
Позвольте мне быстро объяснить, что делает эта формула.
Часть формулы MATCH объединяет поисковое значение (Mark и Long), а также весь поисковый массив. Когда $ A $ 2: A11 & ”|” & $ B $ 2: $ B $ 11 используется в качестве массива поиска, он фактически проверяет значение поиска по объединенной строке имени и фамилии (разделенной вертикальной чертой).
Это гарантирует, что вы получите правильный результат без использования каких-либо вспомогательных столбцов.
Такой поиск (при наличии нескольких столбцов / критериев) можно также выполнять с помощью ВПР, но вам необходимо использовать вспомогательный столбец. Комбинация INDEX MATCH позволяет легко сделать это без каких-либо вспомогательных столбцов.
Пример 5: Получить значения из всей строки / столбца
В приведенных выше примерах мы использовали функцию ИНДЕКС для получения значения из определенной ячейки. Вы указываете номер строки и столбца, и он возвращает значение в этой конкретной ячейке.
Но вы можете сделать больше.
Вы также можете использовать функцию ИНДЕКС, чтобы получить значения из всей строки или столбца.
А чем это может пригодиться спросите вы!
Предположим, вы хотите узнать общий балл Джима по всем трем предметам.
Вы можете использовать функцию ИНДЕКС, чтобы сначала получить все оценки Джима, а затем использовать функцию СУММ, чтобы получить общую сумму.
Давайте посмотрим, как это сделать.
Ниже приведены оценки всех студентов по трем предметам.
Приведенная ниже формула даст мне общий балл Джима по всем трем предметам.
= СУММ (ИНДЕКС ($ B $ 2: $ D $ 11, ПОИСКПОЗ ($ F $ 4, $ A $ 2: $ A $ 11,0), 0))
Позвольте мне объяснить, как работает эта формула.
Уловка здесь заключается в том, чтобы использовать 0 в качестве номера столбца.
Когда вы используете 0 в качестве номера столбца в функции ИНДЕКС, он вернет все значения строки. Точно так же, если вы используете 0 в качестве номера строки, он вернет все значения в столбце.
Таким образом, приведенная ниже часть формулы возвращает массив значений — {97, 70, 73}
ИНДЕКС ($ B $ 2: $ D $ 11, ПОИСКПОЗ ($ F $ 4, $ A $ 2: $ A $ 11,0) , 0)
Если вы просто введете эту формулу выше в ячейку Excel и нажмете клавишу ВВОД, вы увидите # ЗНАЧ! ошибка. Это потому, что он возвращает не одно значение, а массив значений.
Но не волнуйтесь, массив значений все еще там. Вы можете проверить это, выбрав формулу и нажав клавишу F9. Он покажет вам результат формулы, которая в данном случае представляет собой массив из трех значений — {97, 70, 73}
Теперь, если вы заключите эту формулу ИНДЕКС в функцию СУММ, она даст вам сумму всех оценки, поставленные Джимом.
Вы также можете использовать то же самое, чтобы получить высшую, низшую и среднюю оценки Джима.
Так же, как мы сделали это для студента, вы также можете сделать это для предмета. Например, если вы хотите получить средний балл по предмету, вы можете оставить номер строки как 0 в формуле ИНДЕКС, и он предоставит вам все значения столбцов этого предмета.
Щелкните здесь, чтобы загрузить файл примера.
Пример 6. Определение оценки учащегося (метод приблизительного сопоставления)
До сих пор мы использовали формулу MATCH для получения точного соответствия искомому значению.
Но вы также можете использовать его для приблизительного совпадения.
Что, черт возьми, такое приблизительное совпадение?
Позвольте мне объяснить.
Когда вы ищете такие вещи, как имена или идентификаторы, вы ищете точное совпадение. Но иногда вам нужно знать диапазон, в котором лежат ваши значения поиска. Обычно так бывает с числами.
Например, будучи классным руководителем, вы можете узнать, какая оценка у каждого ученика по предмету, и оценка будет определяться на основе полученных баллов.
Ниже приведен пример, в котором я хочу получить оценку для всех учащихся, а оценка определяется на основе таблицы справа.
Итак, если ученик получает меньше 33 баллов, он получает оценку F, а если он / она получает меньше 50, но больше 33, это E и так далее.
Ниже приведена формула для этого.
= ИНДЕКС ($ F $ 3: $ F $ 8, ПОИСКПОЗ (B2, $ E $ 3: $ E $ 8,1), 1)
Позвольте мне объяснить, как работает эта формула.
В функции ПОИСКПОЗ мы использовали 1 в качестве аргумента [match_type].Этот аргумент вернет наибольшее значение, которое меньше или равно искомому значению.
Это означает, что формула MATCH проходит через диапазон меток, и как только она находит диапазон меток, который равен или меньше значения меток поиска, она останавливается на этом и возвращает свою позицию.
Итак, если значение поисковой метки равно 20, функция ПОИСКПОЗ вернет 1, а если оно равно 85, она вернет 5.
И функция ИНДЕКС использует это значение позиции для получения оценки.
ВАЖНО: Чтобы это работало, ваши данные должны быть отсортированы в порядке возрастания. В противном случае вы можете получить неверные результаты.
Обратите внимание, что указанное выше также можно сделать с помощью приведенной ниже формулы ВПР:
= ВПР (B2, $ E $ 3: $ F $ 8,2, ИСТИНА)
Но функция ПОИСКПОЗ может пойти дальше, когда дело доходит до приблизительного совпадения .
Вы также можете иметь данные по убыванию и можете использовать комбинацию ИНДЕКС ПОИСКПОЗ, чтобы найти результат. Например, если я изменю порядок в таблице оценок (как показано ниже), я все равно смогу найти оценки учащихся.
Для этого все, что мне нужно сделать, это изменить аргумент [match_type] на -1.
Ниже приведена формула, которую я использовал:
= ИНДЕКС ($ F $ 3: $ F $ 8, ПОИСКПОЗ (B2, $ E $ 3: $ E $ 8, -1), 1)
ВПР также может выполнять приблизительную совпадают, но только когда данные отсортированы в порядке возрастания (но не работает, если данные отсортированы в порядке убывания).
Пример 7: Поиск с учетом регистра
До сих пор все поиски, которые мы выполняли, были без учета регистра.
Это означает, что было ли искомое значение Jim, JIM или jim, не имело значения. Вы получите тот же результат.
Но что, если вы хотите, чтобы поиск был чувствительным к регистру.
Обычно это происходит, когда у вас есть большие наборы данных и возможность повторения или различных имен / идентификаторов (с той лишь разницей, что это так)
Например, предположим, что у меня есть следующий набор данных студентов, где есть два студенты с именем Джим (с той лишь разницей, что один указан как Джим, а другой — как Джим).
Обратите внимание, что есть два студента с одинаковым именем — Джим (ячейки A2 и A5).
Поскольку обычный поиск не работает, вам нужно выполнять поиск с учетом регистра.
Ниже приведена формула, которая даст вам правильный результат. Поскольку это формула массива, вам нужно использовать Control + Shift + Enter.
= ИНДЕКС ($ B $ 2: $ B $ 11, MATCH (TRUE, EXACT (D3, A2: A11), 0), 1)
Позвольте мне объяснить, как работает эта формула.
Функция EXACT проверяет точное совпадение искомого значения (в данном случае это «jim»).Он перебирает все имена и возвращает ЛОЖЬ, если совпадение не совпадает, и ИСТИНА, если совпадение.
Таким образом, на выходе функции EXACT в этом примере будет — {FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}
Обратите внимание, что есть только одно TRUE, то есть когда EXACT функция нашла идеальное соответствие.
Затем функция ПОИСКПОЗ находит позицию ИСТИНА в массиве, возвращаемом функцией ТОЧНОСТЬ, которая в этом примере равна 4.
Когда у нас есть позиция, функция ИНДЕКС использует ее для поиска меток.
Пример 8: Найдите наиболее близкое совпадение
Давайте немного продвинемся вперед.
Предположим, у вас есть набор данных, в котором вы хотите найти человека, у которого опыт работы наиболее близок к требуемому (указан в ячейке D2).
Хотя формулы поиска не предназначены для этого, вы можете комбинировать их с другими функциями (такими как MIN и ABS), чтобы сделать это.
Ниже приведена формула, которая найдет человека с опытом, наиболее близким к требуемому, и вернет имя человека.Обратите внимание, что опыт должен быть самым близким (который может быть как меньше, так и больше).
= ИНДЕКС ($ A $ 2: $ A $ 15, MATCH (MIN (ABS (D2-B2: B15)), ABS (D2- $ B $ 2: $ B $ 15), 0))
Поскольку это массив формулу, вам нужно использовать Control + Shift + Enter.
Уловка в этой формуле состоит в том, чтобы изменить значение поиска и массив поиска, чтобы найти минимальную разницу в опыте между требуемыми и фактическими значениями.
Прежде чем я объясню формулу, давайте разберемся, как это сделать вручную.
Вы просмотрите каждую ячейку в столбце B и найдете разницу в опыте между тем, что требуется, и тем, что есть у человека. Когда у вас есть все различия, вы найдете минимальное и получите имя этого человека.
Именно это мы и делаем с этой формулой.
Позвольте мне объяснить.
Подстановочное значение в формуле MATCH — MIN (ABS (D2-B2: B15)).
Эта часть дает вам минимальную разницу между заданным опытом (то есть 2.5 лет) и все остальные опыты. В этом примере он возвращает 0,3
Обратите внимание, что я использовал ABS, чтобы убедиться, что ищу ближайший (который может быть больше или меньше, чем данный опыт).
Теперь это минимальное значение становится нашим значением поиска.
Подстановочный массив в функции ПОИСКПОЗ — это ABS (D2- $ B $ 2: $ B $ 15).
Это дает нам массив чисел, из которого вычтено 2,5 (необходимый опыт).
Итак, теперь у нас есть значение поиска (0.3) и поисковый массив ({6,8; 0,8; 19,5; 21,8; 14,5; 11,2; 0,3; 9,2; 2; 9,8; 14,8; 0,4; 23,8; 2,9})
Функция ПОИСКПОЗ находит позицию 0,3 в этом массиве, что также является положением имени человека, имеющего самый близкий опыт.
Этот номер позиции затем используется функцией ИНДЕКС для возврата имени человека.
Связанное чтение : Найдите наиболее близкое совпадение в Excel (примеры с использованием формул поиска)
Щелкните здесь, чтобы загрузить файл примера
Пример 9: Использование ИНДЕКСНОГО СОГЛАСОВАНИЯ с подстановочными знаками
Если вы хотите найти значение, когда есть частичное совпадение, вам нужно использовать подстановочные знаки.
Например, ниже представлен набор данных с названием компании и ее рыночной капитализацией, и вы хотите получить рыночную капитализацию. данные для трех компаний справа.
Поскольку это не точные совпадения, в этом случае вы не сможете выполнять обычный поиск.
Но вы все равно можете получить правильные данные, используя звездочку (*), которая является подстановочным знаком.
Ниже приведена формула, которая предоставит вам данные путем сопоставления названий компаний из основного столбца и получения для них значения рыночной капитализации.
= ИНДЕКС ($ B $ 2: $ B $ 10, MATCH (D2 & ”*”, $ A $ 2: $ A $ 10,0), 1)
Позвольте мне объяснить, как работает эта формула.
Поскольку нет точного совпадения значений поиска, я использовал D2 и ”*” в качестве значения поиска в функции ПОИСКПОЗ.
Звездочка — это подстановочный знак, который представляет любое количество символов. Это означает, что Apple * в формуле будет означать любую текстовую строку, которая начинается со слова Apple и может иметь любое количество символов после него.
Таким образом, когда Apple * используется в качестве значения поиска и формула MATCH ищет его в столбце A, она возвращает позицию «Apple Inc.», поскольку она начинается со слова Apple.
Вы также можете использовать подстановочные знаки для поиска текстовых строк, между которыми находится значение поиска. Например, если вы используете * Apple * в качестве значения поиска, он найдет любую строку, в которой есть слово «яблоко».
Примечание. Этот метод хорошо работает, когда у вас есть только один экземпляр соответствия.Но если у вас есть несколько экземпляров сопоставления (например, Apple Inc и Apple Corporation, то функция ПОИСКПОЗ вернет только позицию первого совпадающего экземпляра.
Пример 10: Трехсторонний поиск
Это расширенное использование ПОИСКПОЗ , но я все же расскажу об этом, чтобы показать вам силу этой комбинации.
Помните, я сказал, что функция ИНДЕКС имеет два синтаксиса:
= ИНДЕКС (array, row_num, [col_num]) = ИНДЕКС (array, row_num, [col_num], [area_num])
До сих пор во всех наших примерах мы использовали только первый.
Но для трехстороннего поиска необходимо использовать второй синтаксис.
Позвольте мне сначала объяснить, что означает трехсторонний взгляд.
При двустороннем поиске мы используем формулу ИНДЕКС ПОИСКПОЗ, чтобы получить оценки, когда у нас есть имя учащегося и имя предмета. Например, получение оценок Джима в математике — это двусторонний поиск.
Трехсторонний вид добавит ему еще одного измерения. Например, предположим, что у вас есть набор данных, показанный ниже, и вы хотите узнать оценку Джима по математике на промежуточном экзамене, тогда это будет трехсторонний поиск.
Ниже приведена формула, которая даст результат.
= ИНДЕКС (($ B $ 3: $ D $ 7, $ B $ 11: $ D $ 15, $ B $ 19: $ D $ 23), MATCH ($ F $ 5, $ A $ 3: $ A $ 7,0), MATCH ( G $ 4, $ B $ 2: $ D $ 2,0), (IF (G $ 3 = "Unit Test", 1, IF (G $ 3 = "Mid Term", 2,3))))
Вышеуказанное формула проверяет три вещи — имя студента, предмет и экзамен. Найдя нужное значение, он возвращает его в ячейку.
Позвольте мне объяснить, как работает эта формула, разбив ее на части.
- массив — ($ B $ 3: $ D $ 7, $ B $ 11: $ D $ 15, $ B $ 19: $ D $ 23) : вместо использования одного массива в этом случае я использовал три массива внутри скобка.
- row_num — ПОИСКПОЗ ($ F $ 5, $ A $ 3: $ A $ 7,0) : функция ПОИСКПОЗ используется для поиска позиции имени учащегося в ячейке $ F $ 5 в списке имен учащихся.
- col_num — MATCH (G $ 4, $ B $ 2: $ D $ 2,0) : функция MATCH используется для поиска позиции имени субъекта в ячейке $ B $ 2 в списке имен субъектов.
- [area_num] — IF (G $ 3 = «Unit Test», 1, IF (G $ 3 = «Mid Term», 2,3)) : значение номера области сообщает функции INDEX, какой из трех массивов следует используйте для получения значения. Если экзамен представляет собой единичный термин, функция ЕСЛИ вернет 1, а функция ИНДЕКС будет использовать первый массив для получения значения. Если экзамен среднесрочный, формула ЕСЛИ вернет 2, иначе она вернет 3.
Это продвинутый пример использования ПОИСКПОЗ ПО ИНДЕКСУ, и вы вряд ли найдете ситуацию, когда вам придется его использовать.Но все же полезно знать, на что способны формулы Excel.
Щелкните здесь, чтобы загрузить файл примера
Почему индекс / совпадение лучше, чем ВПР?
ИЛИ Это?
Да, в большинстве случаев.
Я изложу свой случай через некоторое время.
Но прежде чем я это сделаю, позвольте мне сказать вот что: VLOOKUP — чрезвычайно полезная функция, и мне она нравится. Он может многое делать в Excel, и я сам время от времени им пользуюсь. Сказав это, это не означает, что не может быть ничего лучше, а INDEX / MATCH (с большей гибкостью и функциональностью) лучше.
Итак, если вы хотите выполнить базовый поиск, вам лучше использовать ВПР.
ИНДЕКС / МАТЧ — это ВПР на стероидах. И как только вы изучите INDEX / MATCH, вы всегда можете предпочесть его использование (особенно из-за его гибкости).
Не вдаваясь в подробности, позвольте мне быстро объяснить, почему INDEX / MATCH лучше, чем VLOOKUP.
INDEX / MATCH может смотреть влево (а также вправо) от искомого значения
Я рассмотрел его в одном из приведенных выше примеров.
Если у вас есть значение, которое находится слева от значения поиска, вы не можете сделать это с помощью VLOOKUP
По крайней мере, не только с помощью VLOOKUP.
Да, вы можете комбинировать ВПР с другими формулами и делать это, но это становится сложным и беспорядочным.
INDEX / MATCH, с другой стороны, предназначен для поиска повсюду (будь то влево, вправо, вверх или вниз)
INDEX / MATCH может работать с вертикальными и горизонтальными диапазонами
Опять же, с полным уважением к ВПР, он не предназначен для этого.
В конце концов, V в VLOOKUP означает вертикальное.
VLOOKUP может просматривать данные только по вертикали, в то время как INDEX / MATCH может просматривать данные как по вертикали, так и по горизонтали.
Конечно, есть функция ГПР для горизонтального просмотра, но тогда это не ВПР .. верно?
Мне нравится, что комбо INDEX MATCH достаточно гибкое, чтобы работать как с вертикальными, так и с горизонтальными данными.
ВПР не может работать с данными по убыванию
Когда дело доходит до приблизительного совпадения, ВПР и ИНДЕКС / ПОИСКПОЗ находятся на одном уровне.
Но INDEX MATCH имеет значение, поскольку он также может обрабатывать данные в порядке убывания.
Я показываю это в одном из примеров в этом руководстве, где нам нужно найти оценку учащихся на основе таблицы оценок. Если таблица отсортирована в убывающем порядке, функция ВПР не будет работать (но ПОИСКПОЗ будет).
INDEX / MATCH может быть немного быстрее
Буду честен. Я сам не проводил этот тест.
Я полагаюсь на мудрость мастера Excel — Чарли Кида.
Разница в скорости VLOOKUP и INDEX / MATCH практически незаметна при небольших наборах данных. Но если у вас тысячи строк и много столбцов, это может стать решающим фактором.
В своей статье Чарли Кид утверждает:
«В худшем случае метод INDEX-MATCH работает примерно так же быстро, как VLOOKUP; в лучшем случае это намного быстрее «.
ИНДЕКС / СООТВЕТСТВИЕ не зависит от фактического положения столбца
Если у вас есть набор данных, показанный ниже, когда вы получаете оценку Джима по физике, вы можете сделать это с помощью ВПР.
И для этого вы можете указать номер столбца как 3 в ВПР.
Все нормально.
Но что, если я удалю столбец Math.
В этом случае формула ВПР нарушится.
Почему? — Поскольку было жестко запрограммировано использование третьего столбца, и когда я удаляю столбец между ними, третий столбец становится вторым столбцом.
В этом случае лучше использовать INDEX / MATCH, так как вы можете сделать номер столбца динамическим с помощью MATCH.Поэтому вместо номера столбца он проверяет имя субъекта и использует его для возврата номера столбца.
Конечно, вы можете сделать это, объединив VLOOKUP с MATCH, но если вы все равно комбинируете, почему бы не сделать это с INDEX, который намного более гибок.
При использовании INDEX / MATCH вы можете безопасно вставлять / удалять столбцы в вашем наборе данных.
Несмотря на все эти факторы, есть причина, по которой ВПР так популярна.
И это серьезная причина.
ВПР проще использовать
ВПР принимает не более четырех аргументов.Если вы можете осмыслить эти четыре, то все готово.
И поскольку большинство базовых вариантов поиска также обрабатываются с помощью ВПР, она быстро стала самой популярной функцией Excel.
Я называю это королем функций Excel.
INDEX / MATCH, с другой стороны, немного сложнее в использовании. У вас может возникнуть зависание, если вы начнете его использовать, но для новичка VLOOKUP гораздо проще объяснить и изучить.
И это не игра с нулевой суммой.
Итак, если вы новичок в мире поиска и не знаете, как использовать ВПР, лучше научитесь этому.
У меня есть подробное руководство по использованию ВПР в Excel (с большим количеством примеров)
В этой статье я не собираюсь противопоставлять две потрясающие функции друг другу. Я хотел показать вам силу комбинации INDEX MATCH и все замечательные вещи, на которые она способна.
Надеюсь, эта статья оказалась для вас полезной.
Сообщите мне свои мысли в разделе комментариев, и если вы обнаружите какую-либо ошибку в этом руководстве, сообщите мне.
Вам также могут понравиться следующие руководства по Excel:
Загрузить бесплатные файлы примеров и шаблоны Excel
PT0030 — Поворотный стол Маркеры фильтра — в Excel 2003 нет маркеров на повороте. таблица, чтобы показать, какие поля были отфильтрованы. В этом образце файла от AlexJ, над этими полями появляется символ, чтобы помочь вам идентифицировать их. Подробнее см. В статье блога Contextures Добавить Маркеры фильтра в сводной таблице Excel.AlexJ_PivotFilter.zip 23 кб 19-ноя-10
PT0029 — Изменение полей сводной таблицы на определенных листах — изменить любое поле страницы в сводной таблице, и такие же выборки сделаны во всех других сводных таблицах, которые содержать это поле страницы. Укажите, какие рабочие листы нужно изменить, и какие сводные таблицы и сводные поля игнорировать. Использует слайсеры, если версия — Excel 2010 или новее. Пример кода от Джеффа Вейра. PivotMultiPagesChange_JW.zip 45 Кб 27.08.12 Обновлено 21.06.13
PT0028 — Изменение определенных полей страницы с настройками множественного выбора — Создать список полей страницы это должно быть автоматически изменено.Измените любой из указанных поля страницы в сводной таблице, и такие же выборки выполняются в все остальные сводные таблицы, содержащие это поле страницы. Также меняет настройки «Выбор нескольких элементов» для соответствия изменены поля страницы. PivotMultiPagesChangeSet2010.zip 47 кб 15-июл-12
PT0027 — Изменить все поля страницы
— Все листы или активный лист — У этого образца файла есть 3 варианта
в коде «Изменить все поля страницы», который также меняет
настройки «Выбор нескольких элементов» для соответствия изменены
поля страницы.Только для Excel 2007 и Excel 2010.
1) Измените любое поле страницы в сводной таблице и все соответствующие страницы
поля на всех листах изменены.
2) Измените любое поле страницы в сводной таблице и все соответствующие страницы
изменяются поля только на активном листе.
3) Измените определенное поле страницы в сводной таблице и эту страницу
изменяется только на активном листе. PivotMultiPagesChangeAllVar.zip
37 кб 11-апр-12
4) Измените конкретное поле страницы в любой сводной таблице, и эту страницу
изменено во всех сводных таблицах книги.PivotChangeAllSpecField.zip
16 кб 27-авг-12
PT0026 — Деталь слайсера с поворотным столом — С помощью этой подсказки сводной таблицы от AlexJ вы можете увидеть разницу при переходе к деталям в отфильтрованных сводных таблицах с помощью слайсера Excel. Если отфильтрованного поля нет в сводной таблице макет таблицы, результаты могут быть не такими, как вы ожидаете. AlexJ_DoubleClick_Slicer.zip 19 кб 21-мар-12
PT0025 — Изменить все поля страницы с настройками множественного выбора — Измените любое поле страницы в сводная таблица, и такие же выборки сделаны во всех остальных сводных таблицах. таблицы, содержащие одинаковые поля страницы.Также меняет «Множественный «Выбор элемента», чтобы соответствовать измененным полям страницы. PivotMultiPagesChangeAll2010.zip 40 кб 28-дек-11
PT0024 — Сводная таблица из нескольких
Таблицы — ADO версия — Excel шаблон от Excel MVP Кирилл
Лапин (КЛ). Использует код для автоматического создания сводной таблицы из
несколько листов в книге. Аналогичен образцу файла PT0023, но
основан на ADO и менее подвержен ошибкам в разных версиях
Excel.Нет ручного обновления сводной таблицы. VBA_PT_NormalMultipleSheets
EN 06.zip 21 кб 28 декабря 2011 г.
Plug and Play версия: VBA_PT_NormalMultipleSheets
EN 07.zip 28 kb 28 декабря-11
PT0023A — Рабочая тетрадь по бюджету учащихся — Отслеживайте и планируйте расходы студенческого бюджета от Боба Райана, простого изучения Excel. Проверьте расходы наличными и кредитной картой в сводные таблицы, показывающие ежемесячные итоги. Actual_Budget_Workbook_Basic.zip 16 кб 24 августа 2009 г. См. Также обновленную версию — PT0050 — Бюджетная книга Excel
PT0023 — Сводная таблица из нескольких Листы — Excel шаблон от Excel MVPs, Кирилл Лапин (КЛ), с поправками Гектора Мигеля Ороско Диаса.Подробности в блоге Создавать сводная таблица из нескольких листов. Использует код для автоматического создать сводную таблицу из нескольких листов в книге. VBA.MULT_CONS_PIVOT_EN.zip 16 кб 24.08.09
PT0022 — Поворотный стол фильтра для Диапазон дат — — Введите даты начала и окончания на листе, и обновите сводную таблицу в этом шаблоне Excel. Даты с этого Диапазон выбирается в поле Дата фильтра отчета PivotCodeDateFilter2003.застегивать 20 кб 28.01.09. PivotCodeDateFilter2007.zip 27 кб 28.01.09
PT0021 — Изменить все поля страницы — Измените любое поле страницы в основной сводной таблице и те же варианты выбора. создаются во всех сводных таблицах, содержащих одинаковые поля страницы. Excel 2002/03 — PivotMultiPagesChangeAll.zip 17 кб 04.05.08 Excel 2007- PivotMultiPagesChangeAll2007.zip 17 кб 31.07.09
как получить случайную выборку из набора данных
Из этого туториала Вы узнаете несколько быстрых способов случайного выбора имен, чисел или любых других данных.Вы также узнаете, как получить случайную выборку без дубликатов и как случайным образом выбрать указанное количество или процент ячеек, строк или столбцов одним щелчком мыши.
Независимо от того, проводите ли вы исследование рынка для выпуска нового продукта или оцениваете результаты своей маркетинговой кампании, важно использовать для анализа объективную выборку данных. И самый простой способ добиться этого — получить случайный выбор в Excel.
Что такое случайная выборка?
Прежде чем обсуждать методы выборки, давайте предоставим небольшую справочную информацию о случайном выборе и о том, когда вы, возможно, захотите его использовать.
В теории вероятностей и статистике случайная выборка — это подмножество данных, выбранных из более крупного набора данных, также известного как совокупность . Каждый элемент случайной выборки выбирается совершенно случайно и имеет равную вероятность быть выбранным. Зачем он вам нужен? В основном, чтобы получить непредвзятое представление об общей численности населения.
Например, вы хотите провести небольшой опрос среди своих клиентов. Очевидно, было бы неразумно рассылать анкету каждому человеку в вашей многотысячной базе данных.Итак, кого вы проводите? Будет ли это 100 новых клиентов, или первые 100 клиентов, перечисленные в алфавитном порядке, или 100 человек с самыми короткими именами? Ни один из этих подходов не соответствует вашим потребностям, потому что они изначально предвзяты. Чтобы получить беспристрастную выборку, в которой все имеют равные возможности быть избранными, произведите случайный выбор, используя один из методов, описанных ниже.
Случайный выбор в Excel с формулами
В Excel нет встроенной функции для случайного выбора ячеек, но вы можете использовать одну из функций для генерации случайных чисел в качестве временного решения.Их, наверное, нельзя назвать простыми интуитивными формулами, но они работают.
Как выбрать случайное значение из списка
Предположим, у вас есть список имен в ячейках A2: A10, и вы хотите случайным образом выбрать одно имя из списка. Это можно сделать с помощью одной из следующих формул:
= ИНДЕКС ($ A $ 2: $ A $ 10, RANDBETWEEN (1, COUNTA ($ A $ 2: $ A $ 10)), 1)
или
= ИНДЕКС ($ A $ 2: $ A $ 10, RANDBETWEEN (1, ROWS ($ A $ 2: $ A $ 10)), 1)
Вот и все! Средство случайного выбора имени для Excel настроено и готово к работе:
Естественно, эти формулы могут не только выбирать случайные имена, но также выбирать случайные числа, даты или любые другие случайные ячейки.
Как работают эти формулы
Вкратце, вы используете функцию ИНДЕКС для извлечения значения из списка на основе случайного номера строки, возвращаемого RANDBETWEEN.
Более конкретно, функция СЛУЧМЕЖДУ генерирует случайное целое число между двумя указанными вами значениями. Для нижнего значения вы указываете число 1. Для верхнего значения вы используете COUNTA или ROWS, чтобы получить общее количество строк. В результате RANDBETWEEN возвращает случайное число от 1 до общего количества строк в вашем наборе данных.Это число поступает в аргумент row_num функции INDEX, сообщая ему, какую строку выбрать. Для аргумента column_num мы используем 1, поскольку мы хотим извлечь значение из первого столбца.
Примечание. Этот метод хорошо подходит для выбора одной случайной ячейки из списка. Если ваш образец должен включать несколько ячеек, приведенная выше формула может возвращать несколько вхождений одного и того же значения, потому что функция RANDBETWEEN не лишена дубликатов.Это особенно актуально, когда вы выбираете относительно большую выборку из относительно небольшого списка. В следующем примере показано, как сделать случайный выбор в Excel без дубликатов.
Как произвольно выбирать в Excel без дубликатов
Есть несколько способов выбрать случайные данные без дубликатов в Excel. Как правило, вы должны использовать функцию СЛЧИС, чтобы присвоить случайное число каждой ячейке, а затем выбрать несколько ячеек с помощью формулы индекса ранга.
Со списком имен в ячейках A2: A16 выполните следующие действия, чтобы извлечь несколько случайных имен:
- Введите формулу Rand в B2 и скопируйте ее в столбец:
= RAND () - Поместите приведенную ниже формулу в C2, чтобы извлечь случайное значение из столбца A:
= ИНДЕКС (2 доллара США: 16 австралийских долларов, РАНГ (B2, 2 доллара США: 16 долларов США), 1) - Скопируйте приведенную выше формулу в столько ячеек, сколько случайных значений вы хотите выбрать.В нашем случае мы копируем формулу еще в четыре ячейки (C2: C6).
Вот и все! Извлекаются пять случайных имен без дубликатов:
Как работает эта формула
Как и в предыдущем примере, вы используете функцию ИНДЕКС для извлечения значения из столбца A на основе случайной координаты строки. В этом случае для его получения требуются две разные функции:
- Формула RAND заполняет столбец B случайными числами.
- Функция РАНГ возвращает ранг случайного числа в той же строке.Например, RANK (B2, $ B $ 2: $ B $ 16) в ячейке C2 получает ранг числа в B2. При копировании в C3 относительная ссылка B2 изменяется на B3 и возвращает ранг числа в B3 и так далее.
- Число, возвращаемое RANK, передается в аргумент row_num функции INDEX, поэтому он выбирает значение из этой строки. В аргументе column_num вы указываете 1, потому что хотите извлечь значение из первого столбца.
Предупреждение! Как показано на скриншоте выше, наша случайная выборка Excel содержит только уникальные значения.Но теоретически вероятность появления дубликатов в вашем образце мала. Вот почему: в очень большом наборе данных RAND может генерировать повторяющиеся случайные числа, а RANK будет возвращать тот же ранг для этих чисел. Лично у меня во время тестов ни разу не было дубликатов, но теоретически такая вероятность существует.
Если вы ищете надежную формулу для получения случайного выбора только с уникальными значениями, используйте комбинацию RANK + COUNTIF или RANK.EQ + COUNTIF вместо RANK.Подробное объяснение логики см. В разделе Уникальное ранжирование в Excel.
Полная формула немного громоздка, но на 100% без дубликатов:
= ИНДЕКС ($ A $ 2: $ A $ 16, RANK.EQ (B2, $ B $ 2: $ B $ 16) + COUNTIF ($ B $ 2: B2, B2) - 1, 1)
- Как и RANDBETWEEN, функция RAND в Excel также генерирует новые случайные числа при каждом пересчете вашего рабочего листа, что приводит к изменению случайного выбора. Чтобы образец оставался неизменным, скопируйте его и вставьте в другое место в качестве значений ( Вставить Специальный > Значения ).
- Если одно и то же имя (число, дата или любое другое значение) встречается более одного раза в исходном наборе данных, случайная выборка может также содержать несколько вхождений одного и того же значения.
Как выбрать случайные строки в Excel
Если ваш рабочий лист содержит более одного столбца данных, вы можете выбрать случайную выборку таким образом: присвоить случайное число каждой строке, отсортировать эти числа и выбрать необходимое количество строк. Подробные инструкции приведены ниже.
- Вставьте новый столбец справа или слева от таблицы (столбец D в этом примере).
- В первой ячейке вставленного столбца, исключая заголовки столбцов, введите формулу СЛЧИС:
= СЛУЧАЙ () - Дважды щелкните маркер заполнения, чтобы скопировать формулу вниз по столбцу. В результате каждой строке будет присвоено случайное число.
- Сортировка случайных чисел от наибольшего к наименьшему (сортировка в порядке возрастания переместит заголовки столбцов в нижнюю часть таблицы, поэтому обязательно выполните сортировку по убыванию). Для этого перейдите на вкладку Data > Sort & Filter group и нажмите кнопку ZA.Excel автоматически расширит выбор и отсортирует все строки в случайном порядке.
Если вас не совсем устраивает рандомизация таблицы, нажмите кнопку сортировки еще раз, чтобы применить ее. Подробные инструкции см. В разделе Как выполнить произвольную сортировку в Excel.
- Наконец, выберите необходимое количество строк для вашего образца, скопируйте их и вставьте куда хотите.
Чтобы ближе познакомиться с формулами, обсуждаемыми в этом руководстве, вы можете загрузить наш образец книги в Excel Random Selection.
Как произвольно выбирать в Excel с помощью инструмента «Случайный выбор»
Теперь, когда вы знаете несколько формул для получения случайной выборки в Excel, давайте посмотрим, как можно добиться того же результата одним щелчком мыши.
Вот что вы делаете с нашим Ultimate Suite, установленным в Excel:
- Выберите любую ячейку в таблице.
- Перейдите на вкладку Ablebits Tools > Utilities и нажмите Randomize > Select Random :
- На панели надстройки выберите, что выбрать: случайные строки, случайные столбцы или случайные ячейки.
- Укажите число или процент для желаемого размера выборки.
- Нажмите кнопку Выбрать . Выполнено!
Например, вот как мы можем выбрать 5 случайных строк из нашего набора данных образца:
И вы получите случайный выбор через секунду:
Теперь вы можете нажать Ctrl + C, чтобы скопировать случайный образец, а затем нажать Ctrl + V, чтобы вставить его в место на том же или другом листе.
Если вы хотите протестировать инструмент «Случайный выбор» на своих листах, загрузите пробную версию Ultimate Suite for Excel.Если инструмент соответствует вашим потребностям и вы решили получить лицензию, не упустите эту особую возможность, которую мы предоставляем специально для наших читателей блога:
Получите промокод для Ultimate Suite — специальное предложение для посетителей нашего блога!Доступные загрузки
Выбор случайной выборки — примеры формул (файл .xlsx)
Ultimate Suite — пробная версия (файл .zip)
Вас также может заинтересовать
Экономьте время, используя строковые функции Excel влево, вправо и в середине
Не меняйте ключ к тому, что вы можете извлечь с помощью строковой функции Excel.Научитесь использовать три большие строковые функции.
Вы раздражаетесь, когда видите, что кто-то тратит драгоценное время? Да, и я не могу не сунуть нос и предложить лучший способ. Вот что случилось недавно, когда я заметил, что один из моих коллег использует Excel и вводит с нуля что-то, что можно было бы сгенерировать с помощью простой формулы.Вот сенсация.
ПОДРОБНЕЕ: цены и возможности Office 365 для потребителей
Если текст встроен в ячейку, его можно извлечь.
Мой коллега получил от поставщика рабочий лист, содержащий столбец записей, которые выглядели так, как показано на рис. A .
Рисунок A: Мы будем использовать функции для извлечения определенных частей записей в столбце A.
Эти строки содержат три отдельные части:
- Первые три символа (K-числа) представляют код продукта.
- Вторые две цифры (B-числа) представляют собой код цены.
- Последние три цифры представляют собой код клиента.
Моя коллега хотела разделить эти три части на разные столбцы, и она перепечатывала их с нуля! Такой подход настолько расточителен и неэффективен, что у меня мурашки по коже. К счастью, я стал героем, показав ей, как использовать строковые функции Excel для автоматического извлечения кодов.
Развлечение со строковыми функциями
Все вы, опытные пользователи электронных таблиц, знаете это упражнение наизусть.Вот как это работает.
Захват первых трех символов
Чтобы извлечь первые три символа текстовых записей, вы вводите функцию Left следующим образом:
= Left (source_string, number_of_characters)
В этом случае мы ввели в ячейку B2 функцию = Влево (A2,3), а затем скопировал эту формулу в ячейки B3: B8. Рисунок B показывает результаты.
Рисунок B: Функция Left устраняет необходимость повторно вводить первые три буквы из записей в столбце A.
Вытягивание двух символов в середине
Чтобы извлечь два символа в середине строки, мы будем использовать функцию Mid, которая принимает форму:
= Mid (исходная_строка, начальная_позиция, длина)
Поскольку мы знаем, что строка, которую мы хотим извлечь, всегда начинается с позиции 4, мы ввели в ячейку C2 функцию = Mid (A2,4,2), а затем скопировали эту формулу в ячейки C3: C8. Рисунок C показывает результаты.
Рисунок C: Функция Mid позволяет вытащить струну из середины.
Извлечение последних трех символов строки
Чтобы извлечь последние три символа строки, вы используете функцию Right в форме:
= Right (source_string, number_of_characters)
В нашем примере мы ввели в ячейке D2 функция = Right (A2,3) и скопировала ее в ячейки D3: D8. Как показано на рисунке D , эта функция возвращает три крайних правых символа в исходной строке.
Рисунок D: Функция Right упрощает копирование набора символов с правой стороны строки.
После того, как вы извлекли струны, что потом?
После того, как вы использовали строковые функции для синтаксического анализа исходной строки на подстроки, вы можете сортировать или вычислять промежуточные итоги по любому из столбцов, содержащих эти подстроки. Составьте вызов функции и скопируйте его в соответствующие ячейки всего за минуту или две. Этот метод пригодится, когда вы импортируете текстовые файлы, выгруженные из базы данных мэйнфрейма или из какого-либо другого приложения.
Конечно, мы только коснулись поверхности того, что вы можете делать со строковыми функциями «большой тройки».В этом простом примере наша исходная строка всегда содержала одинаковое количество символов.
Знание того, что средняя струна всегда начинается с четвертой позиции, упростило использование функции Mid. Но что, если подстрока, которую вы хотите извлечь, может начинаться где угодно внутри строки? В будущей статье мы покажем вам, как использовать функцию Find в сочетании с функцией Mid для поиска и извлечения строки, независимо от того, сколько символов содержит исходная строка.
Чтобы прокомментировать эту статью или поделиться своим любимым советом по Excel, оставьте комментарий ниже или напишите нам.
Еженедельный бюллетень Microsoft
Будьте инсайдером Microsoft в своей компании, прочитав эти советы, рекомендации и шпаргалки по Windows и Office.Доставка по понедельникам и средам.
Зарегистрироваться СегодняСм. Также
Информация об аффилированных лицах: TechRepublic может получать комиссию от продуктов и услуг, представленных на этой странице.
расширенных формул Excel — 10 формул, которые вы должны знать!
10 расширенных формул Excel, которые вы должны знать
Каждый финансовый аналитик Руководство для аналитиков Trifecta® Окончательное руководство о том, как стать финансовым аналитиком мирового уровня. Вы хотите быть финансовым аналитиком мирового уровня? Вы хотите следовать передовым отраслевым практикам и выделиться из толпы? Наш процесс, который называется The Analyst Trifecta®, состоит из аналитики, презентаций и мягких навыков, он проводит в Excel больше времени, чем они могут себе представить.Основываясь на многолетнем опыте, мы составили самые важные и продвинутые формулы Excel, которые должен знать каждый финансовый аналитик мирового уровня.
1. INDEX MATCH
Формула: = INDEX (C3: E9, MATCH (B13, C3: C9,0), MATCH (B14, C3: E3,0))
Это усовершенствованная альтернатива Формулы ВПР или ГПР (у которых есть несколько недостатков и ограничений). ИНДЕКС ПОИСКПОЗ Формула соответствия индекса Объединение функций ИНДЕКС и ПОИСКПОЗ является более мощной формулой поиска, чем ВПР.Узнайте, как использовать ИНДЕКС ПОИСКПОЗ в этом руководстве по Excel. Index возвращает значение ячейки в таблице на основе номера столбца и строки, а Match возвращает позицию ячейки в строке или столбце. Узнайте, как это сделать, в этом руководстве — это мощная комбинация формул Excel, которая выведет ваш финансовый анализ и финансовое моделирование на новый уровень.
ИНДЕКС возвращает значение ячейки в таблице на основе номера столбца и строки.
ПОИСКПОЗ возвращает положение ячейки в строке или столбце.
Вот пример сочетания формул ИНДЕКС и ПОИСКПОЗ. В этом примере мы ищем и возвращаем рост человека на основе его имени. Поскольку имя и высота являются переменными в формуле, мы можем изменить их обе!
Формула сопоставления индекса Объединение функций ИНДЕКС и ПОИСКПОЗ — более эффективная формула поиска, чем ВПР. Узнайте, как использовать ИНДЕКС ПОИСКПОЗ в этом руководстве по Excel. Index возвращает значение ячейки в таблице на основе номера столбца и строки, а Match возвращает позицию ячейки в строке или столбце.Узнайте, как их использовать в этом руководстве
Для получения пошагового объяснения или того, как использовать эту формулу, см. Наше бесплатное руководство о том, как использовать ИНДЕКС ПОИСКПОЗ в Excel Формула соответствия индексов Объединение функций ИНДЕКС и ПОИСКПОЗ — более эффективный поиск формула, чем ВПР. Узнайте, как использовать ИНДЕКС ПОИСКПОЗ в этом руководстве по Excel. Index возвращает значение ячейки в таблице на основе номера столбца и строки, а Match возвращает позицию ячейки в строке или столбце. Узнайте, как это сделать, в этом руководстве.
2. ЕСЛИ в сочетании с И / ИЛИ
Формула: = ЕСЛИ (И (C2> = C4, C2 <= C5), C6, C7)
Любой, кто потратил много времени на создание различных типов финансовых моделей Типы финансовых моделей Наиболее распространенные типы финансовых моделей включают: модель с 3 отчетами, модель DCF, модель M&A, модель LBO, модель бюджета. Откройте для себя 10 основных типов и знает, что вложенные формулы ЕСЛИ могут быть кошмаром. Объединение IF с функцией AND или OR может быть отличным способом упростить аудит формул и облегчить понимание другими пользователями.В приведенном ниже примере вы увидите, как мы использовали комбинацию отдельных функций для создания более сложной формулы.
Подробное описание выполнения этой функции в Excel см. В нашем бесплатном руководстве по использованию оператора IF с оператором AND / ORIF между двумя числами. Загрузите этот бесплатный шаблон для оператора IF между двумя числами в Excel. В этом руководстве мы покажем вам шаг за шагом, как вычислить IF с помощью оператора AND. Узнайте, как создать оператор IF, который проверяет, содержит ли ячейка значение между двумя числами, а затем выводит желаемый результат, если это условие выполнено.Оператор ЕСЛИ между двумя числамиЗагрузите этот бесплатный шаблон для оператора ЕСЛИ между двумя числами в Excel. В этом руководстве мы покажем вам шаг за шагом, как вычислить IF с помощью оператора AND. Узнайте, как создать оператор IF, который проверяет, содержит ли ячейка значение между двумя числами, а затем выводит желаемый результат, если это условие выполняется
3. СМЕЩЕНИЕ в сочетании с СУММ или СРЕДНИМ
Формула: = СУММ ( B4: СМЕЩЕНИЕ (B4,0, E2-1))
Функция СМЕЩЕНИЕ Функция СМЕЩЕНИЕ Функция СМЕЩЕНИЕ относится к функциям поиска и справочника Excel.СМЕЩЕНИЕ вернет диапазон ячеек. То есть он вернет указанное количество строк и столбцов из указанного начального диапазона. сам по себе не особенно продвинут, но когда мы объединяем его с другими функциями, такими как SUM или AVERAGE, мы можем создать довольно сложную формулу. Предположим, вы хотите создать динамическую функцию, которая может суммировать переменное количество ячеек. С помощью обычной формулы СУММ вы ограничены статическим вычислением, но добавив СМЕЩЕНИЕ, вы можете перемещать ссылку на ячейку.
Как это работает: Чтобы эта формула работала, мы заменяем конечную ссылочную ячейку функции СУММ на функцию СМЕЩЕНИЕ. Это делает формулу динамической, и в ячейке, обозначенной как E2, вы можете указать Excel, сколько последовательных ячеек вы хотите добавить. Теперь у нас есть несколько сложных формул Excel!
Ниже приведен снимок экрана с более сложной формулой в действии.
Как видите, формула СУММ начинается с ячейки B4, но заканчивается переменной, которая является формулой СМЕЩЕНИЯ, начинающейся с B4 и продолжающейся значением в E2 («3») минус один.Это перемещает конец формулы суммы на 2 ячейки, суммируя данные за 3 года (включая начальную точку). Как вы можете видеть в ячейке F7, сумма ячеек B4: D4 равна 15, что дает нам формула смещения и суммы.
Узнайте, как построить эту формулу шаг за шагом в нашем расширенном курсе Excel.
4. ВЫБРАТЬ
Формула: = ВЫБРАТЬ (вариант, вариант1, вариант2, вариант3)
Функция ВЫБРАТЬ Функция ВЫБРАТЬ Функция ВЫБРАТЬ относится к функциям поиска и справочника Excel.Он вернет значение из массива, соответствующее указанному номеру индекса. Функция вернет n-ю запись в данном списке. Как финансовый аналитик, функция ВЫБОР полезна при выборе из заданного набора данных. Например, мы отлично подходят для сценарного анализа в финансовом моделировании. Он позволяет вам выбирать между определенным количеством вариантов и возвращать тот «выбор», который вы выбрали. Например, представьте, что у вас есть три разных предположения относительно роста доходов в следующем году: 5%, 12% и 18%.Используя формулу ВЫБРАТЬ, вы можете вернуть 12%, если скажете Excel, что хотите выбрать вариант №2.
Подробнее об анализе сценария в Excel Анализ сценария Анализ сценария — это процесс изучения и оценки возможных событий или сценариев, которые могут произойти в будущем, и прогнозирования.
Чтобы увидеть демонстрацию видео, посетите наш расширенный курс формул Excel.
5. XNPV и XIRR
Формула: = XNPV (ставка дисконтирования, денежные потоки, даты)
Если вы аналитик, работающий в сфере инвестиционного банкинга, инвестиционный банкинг Карьерный путьРуководство по карьере в инвестиционном банке — спланируйте свой карьерный путь в IB.Узнайте о зарплатах в инвестиционном банке, о том, как устроиться на работу и что делать после карьеры в IB. Подразделение инвестиционного банкинга (IBD) помогает правительствам, корпорациям и учреждениям привлекать капитал и завершать слияния и поглощения (M&A)., Исследование капитала, финансовое планирование и анализ личность и обучение, необходимое для работы FP&A и успешной финансовой карьеры. Аналитики FP&A, менеджеры и директора несут ответственность за предоставление руководителям необходимого анализа и информации) или любой другой области корпоративных финансов, которая требует дисконтирования денежных потоков, затем эти формулы — палочка-выручалочка!
Проще говоря, XNPV и XIRR позволяют применять определенные даты к каждому отдельному дисконтированному денежному потоку.Проблема с основными формулами NPV и IRR в Excel заключается в том, что они предполагают, что периоды времени между денежными потоками равны. Как аналитик, вы обычно будете сталкиваться с ситуациями, когда денежные потоки распределяются неравномерно, и эта формула поможет вам это исправить.
Для получения более подробной информации см. Наше бесплатное руководство по формулам IRR и XIRR. XIRR назначает конкретные даты каждому отдельному денежному потоку, что делает его более точным, чем IRR, при построении финансовой модели в Excel.а также наше руководство по XNPV.
6. СУММЕСЛИ и СЧЁТЕСЛИ
Формула: = СЧЁТЕСЛИ (D5: D12, ”> = 21 ″)
Эти две расширенные формулы отлично подходят для использования условных функций. СУММЕСЛИ добавляет все ячейки, соответствующие определенным критериям, а СЧЁТЕСЛИ подсчитывает все ячейки, соответствующие определенным критериям. Например, представьте, что вы хотите подсчитать все ячейки, которые больше или равны 21 (законный возраст употребления алкоголя в США), чтобы узнать, сколько бутылок шампанского вам нужно для клиентского мероприятия.Вы можете использовать СЧЁТЕСЛИ в качестве расширенного решения, как показано на скриншоте ниже.
В нашем продвинутом курсе Excel мы разбиваем эти формулы на еще более подробную информацию.
7. PMT и IPMT
Формула: = PMT (процентная ставка, количество периодов, приведенная стоимость)
Если вы работаете в коммерческом банковском бизнесе Профиль карьеры в коммерческом банковском бизнесе Карьерный профиль в коммерческом банке предусматривает предоставление клиентам кредитных продуктов, таких как срок займы, возобновляемые кредитные линии, синдицированные кредиты, недвижимость, FP, AFP и AF Финансовое планирование и анализ (FP&A) — важная функция в корпорации.Специалисты FP&A поддерживают принятие управленческих решений или занимают любую должность финансового аналитика, занимающегося графиками долга. Вам необходимо понять эти две подробные формулы.
Формула PMT дает значение равных платежей в течение срока ссуды. Вы можете использовать его вместе с IPMT (который сообщает вам процентные платежи по одному и тому же типу ссуды), а затем разделить основную сумму и процентные платежи.
Вот пример того, как использовать функцию PMT, чтобы получить ежемесячный платеж по ипотеке на 1 миллион долларов под 5% на 30 лет.
8. LEN и TRIM
Формулы: = LEN (текст) и = TRIM (текст)
Приведенные выше формулы немного реже, но, безусловно, очень сложные. Они отлично подходят для финансовых аналитиков. Руководство для аналитиков Trifecta® — полное руководство о том, как стать финансовым аналитиком мирового уровня. Вы хотите быть финансовым аналитиком мирового уровня? Вы хотите следовать передовым отраслевым практикам и выделиться из толпы? Наш процесс, называемый аналитиком Trifecta®, состоит из аналитики, презентаций и навыков межличностного общения, которым необходимо систематизировать большие объемы данных и манипулировать ими.К сожалению, данные, которые мы получаем, не всегда идеально организованы, и иногда могут возникать проблемы, такие как лишние пробелы в начале или конце ячеек.
Формула LEN возвращает заданную текстовую строку как количество символов, что полезно, когда вы хотите подсчитать, сколько символов содержится в некотором тексте.
В приведенном ниже примере вы можете увидеть, как формула TRIM очищает данные Excel.
9. CONCATENATE
Формула: = A1 & «больше текста»
Concatenate не является самостоятельной функцией — это просто инновационный способ объединения информации из разных ячеек и придания большей динамичности рабочим листам.Это очень мощный инструмент для финансовых аналитиков, выполняющих финансовое моделирование. ).
В приведенном ниже примере вы можете увидеть, как текст «Нью-Йорк» плюс «,» соединяется с «Нью-Йорк» для создания «Нью-Йорк, Нью-Йорк». Это позволяет создавать динамические заголовки и метки на листах.Теперь вместо прямого обновления ячейки B8 вы можете обновлять ячейки B2 и D2 независимо. Имея в своем распоряжении большой набор данных, это ценный навык.
10. Функции ЯЧЕЙКА, ВЛЕВО, СРЕДНЯЯ и ПРАВАЯ
Эти расширенные функции Excel можно комбинировать для создания очень сложных и сложных формул. Функция ЯЧЕЙКА может возвращать различную информацию о содержимом ячейки (например, ее имя, расположение, строку, столбец и т. Д.).Функция LEFT может возвращать текст из начала ячейки (слева направо), MID возвращает текст из любой начальной точки ячейки (слева направо), а RIGHT возвращает текст из конца ячейки (справа налево).
Ниже приведена иллюстрация трех формул в действии.
Чтобы увидеть, как их можно эффективно комбинировать с функцией CELL, мы шаг за шагом разберем ее для вас в нашем расширенном классе формул Excel.
Дополнительные тренинги по формулам Excel
Мы надеемся, что эти 10 лучших расширенных формул Excel были для вас полезны.Они должны пройти долгий путь к совершенствованию ваших навыков финансового анализа и финансового моделирования Навыки финансового моделирования Изучите 10 наиболее важных навыков финансового моделирования и то, что требуется для хорошего финансового моделирования в Excel. Самые важные навыки: бухгалтерский учет.
Ниже приведены дополнительные ресурсы CFI, которые помогут вам стать опытным пользователем Excel:
- Шпаргалка по формулам Excel Шпаргалка по формулам Excel Шпаргалка по формулам Excel от CFI предоставит вам все наиболее важные формулы для выполнения финансового анализа и моделирования в таблицах Excel.Если вы хотите стать мастером финансового анализа Excel и экспертом по построению финансовых моделей, вы попали в нужное место.
- Сочетания клавиш Excel Сочетания клавиш Excel Сочетания клавиш MacExcel для ПК — Список наиболее важных и распространенных сочетаний клавиш MS Excel для пользователей ПК и Mac, специалистов в области финансов и бухгалтерского учета. Сочетания клавиш ускоряют ваши навыки моделирования и экономят время. Изучите редактирование, форматирование, навигацию, ленту, специальную вставку, манипулирование данными, редактирование формул и ячеек и другие краткие сведения.
- Бесплатный ускоренный курс Excel
- Расширенный курс Excel
- Excel для финансового моделирования
15 необходимых вам функций анализа данных Excel знать
Если вы когда-либо использовали Excel, то, вероятно, испытали агонию выбора неправильной формулы для анализа набора данных.Возможно, вы работали над этим часами, и в конце концов сдались, потому что вывод данных был неправильным, или функция была слишком сложной, и казалось, что проще подсчитать данные самостоятельно вручную. Если это похоже на вас, то этот анализ данных в топ-15 Excel для вас.
В Excel есть сотни функций, и попытка сопоставить правильную формулу с правильным анализом данных может оказаться непосильной задачей. Самые полезные функции не должны быть сложными. Пятнадцать простых функций улучшат вашу способность анализировать данные, заставляя задуматься, как вы когда-либо жили без них.
Работаете ли вы в Excel или активно пользуетесь им в своей работе, в этом списке найдется функция для каждого.
1. СЦЕПИТЬ
= СЦЕПИТЬ — одна из самых простых в освоении, но наиболее эффективных формул при анализе данных. Объединяйте текст, числа, даты и многое другое из нескольких ячеек в одну. Это отличная функция для создания конечных точек API, SKU продуктов и запросов Java.
Формула:
= СЦЕПИТЬ (ВЫБЕРИТЕ ЯЧЕЙКИ, КОТОРЫЕ ВЫ ХОТИТЕ ОБЪЕДИНЯТЬ)
В этом примере:
= СЦЕПИТЬ (A2; B2)
2.LEN
= LEN быстро предоставляет количество символов в данной ячейке. Как и в приведенном выше примере, вы можете определить два разных типа единиц складского учета (SKU) продукта, используя формулу = LEN, чтобы узнать, сколько символов содержит ячейка. LEN особенно полезен при попытке определить различия между различными уникальными идентификаторами (UID), которые часто бывают длинными и расположены не в правильном порядке.
Формула:
= ЛЕН (ВЫБОР ЯЧЕЙКИ)
В этом примере:
= LEN (A2)
3.COUNTA
= COUNTA определяет, пуста ячейка или нет. В жизни аналитика данных вы будете ежедневно сталкиваться с неполными наборами данных. COUNTA позволит вам оценить любые пробелы в наборе данных без реорганизации данных.
Формула:
= СЧЕТЧИК (ВЫБРАТЬ ЯЧЕЙКУ)
В примере:
= СЧЕТ (A10)
4. ДНЕЙ / ЧИСТЫХ ДНЕЙ
= ДНИ — это именно то, что подразумевается. Эта функция определяет количество календарных дней между двумя датами.Это полезный инструмент для оценки жизненного цикла продуктов и контрактов, а также для оценки доходов в зависимости от продолжительности услуг — важен анализ данных.
= NETWORKDAYS немного более надежен и полезен. Эта формула определяет количество «рабочих дней» между двумя датами, а также возможность учета праздников. Даже трудоголикам время от времени нужен перерыв! Использование этих двух формул для сравнения временных рамок особенно полезно для управления проектами.
Формулы:
= ДНИ (ВЫБРАТЬ ЯЧЕЙКУ, ВЫБРАТЬ ЯЧЕЙКУ)
ИЛИ
= ЧИСТЫЕ ДНИ (ВЫБРАТЬ ЯЧЕЙКУ, ВЫБРАТЬ ЯЧЕЙКУ, [количество праздников])
примечание: [количество праздников] необязательно
В примере:
= ДНИ (C8, B8)
ИЛИ
= ЧИСТЫЕ ДНИ (B7, C7,3)
5.СУММЕСЛИМН
= СУММЕСЛИМН — одна из формул, которую необходимо знать аналитику данных. Обычно используется формула = СУММ, но что, если вам нужно суммировать значения на основе нескольких критериев? СУММЕСЛИМН. В приведенном ниже примере СУММЕСЛИМН используется для определения того, насколько каждый продукт способствует выручке.
Формула:
= СУММЕСЛИ (ДИАПАЗОН; КРИТЕРИИ; [диапазон_суммы])
примечание: [диапазон_суммы] является необязательным
В примере:
= СУММЕСЛИ ($ B $ 2: $ B $ 28, $ A $ 2: $ A $ 28, $ F2)
6.СРЕДНЕНОМН
Как и СУММЕСЛИМН, СРЗНАЧЕСЛИМН позволяет брать среднее значение на основе одного или нескольких критериев.
Формула:
= СРЗНАЧЕСЛИ (ВЫБРАТЬ ЯЧЕЙКУ, КРИТЕРИИ; [СРЕДНИЙ_ДИАПАЗОН])
примечание: [средний_ диапазон] является необязательным
В примере:
= СРЕДНЕЛИ ($ C: $ C, $ A: $ A, $ F2)
7. ВПР
ВПР — одна из самых полезных и узнаваемых функций анализа данных. Как пользователю Excel, вам, вероятно, в какой-то момент придется «объединить» данные.Например, отдел дебиторской задолженности может знать, сколько стоит каждый продукт, но отдел отгрузки может предоставить только отгруженные единицы. Это идеальный вариант использования ВПР.
На изображении ниже мы используем справочные данные (A2) в сочетании с таблицей цен, чтобы Excel находил критерии соответствия в первом столбце и возвращал соседнее значение.
Формула:
= ВПР (ЗНАЧЕНИЕ_ПРОСМОТРА, ТАБЛИЦА_Массив, COL_INDEX_NUM, [ДИАПАЗОН_ПРОС])
В примере:
= ВПР ($ A2, $ G $ 1: $ H $ 5,2,0)
8.НАЙТИ / ПОИСК
= НАЙТИ / = ПОИСК — мощные функции для выделения определенного текста в наборе данных. Оба перечислены здесь, потому что = FIND вернет совпадение с учетом регистра, т.е. если вы используете FIND для запроса «Big», вы вернете только результаты Big = true. Но a = SEARCH для «Big» будет соответствовать значениям «Big» или «big», что сделает запрос немного шире. Это особенно полезно для поиска аномалий или уникальных идентификаторов.
Формула:
= НАЙТИ (ТЕКСТ, WITHIN_TEXT; [START_NUMBER]) ИЛИ = ПОИСК (ТЕКСТ, WITHIN_TEXT, [START_NUMBER])
примечание: [start_number] является необязательным и используется для указания начальной ячейки в тексте для поиска
В примере:
= (НАЙТИ («Большой»; A2,1) »»)
9.ЕСЛИ ОШИБКА
= ЕСЛИ ОШИБКА — это то, чем должен воспользоваться любой аналитик, который активно представляет данные. Используя предыдущий пример, поиск определенного текста / значений в наборе данных не вернет совпадения. Это вызывает ошибку #VALUE и, хотя и безвредно, отвлекает внимание и вызывает раздражение.
Используйте = ЕСЛИОШИБКА, чтобы заменить ошибки #VALUE любым текстом / значением. В приведенном выше примере ячейка пуста, поэтому потребители данных могут легко выбрать, какие строки вернули совпадающее значение.
Формула:
= ЕСЛИ ОШИБКА (НАЙТИ «ЗНАЧЕНИЕ», ВЫБРАТЬ ЯЧЕЙКУ, ЗНАЧЕНИЕ_IF_ERROR)
В примере:
= ЕСЛИ ОШИБКА (НАЙТИ «БОЛЬШОЙ»; A6,1); «»)
10.СЧЕТЕСЛИ
= СЧЁТЕСЛИМН — это самый простой способ подсчитать количество экземпляров набора данных, удовлетворяющих набору критериев. В приведенном выше примере название продукта используется для определения того, какой продукт был самым продаваемым. СЧЕТЕСЛИМН является мощным средством, поскольку вы можете вводить безграничные критерии.
Формула:
= СЧЁТЕСЛИМН (ДИАПАЗОН, КРИТЕРИИ)
В примере:
= СЧЁТЕСЛИМН ($ A: $ A, $ F9)
11. ВЛЕВО / ВПРАВО
= LEFT, = RIGHT — эффективные и простые методы извлечения статических данных из ячеек.= LEFT вернет количество символов «x» от начала ячейки, а = right вернет количество символов «x» от конца ячейки. В приведенном ниже примере = LEFT используется для извлечения кода зоны потребителя из его номера телефона, а = RIGHT используется для извлечения последних четырех цифр.
Формула:
= ЛЕВАЯ (ВЫБОР ЯЧЕЙКИ, НОМЕР)
ИЛИ
= ПРАВАЯ (ВЫБРАТЬ ЯЧЕЙКУ, НОМЕР)
В этом примере:
= ЛЕВЫЙ (A6, 3)
И
= ВПРАВО (A6,4)
12.РАНГ
= RANK — древняя функция Excel, но это не умаляет ее эффективности для анализа данных. = RANK позволяет быстро определить ранжирование значений в наборе данных в порядке возрастания или убывания. В этом примере RANK используется для определения того, какие клиенты заказывают больше всего товаров.
Формула:
= РАНГ (ВЫБРАТЬ ЯЧЕЙКУ, ДИАПАЗОН_TO_RANK_AGAINST, [ПОРЯДОК])
примечание: [заказ] является необязательным
В примере:
= РАНГ ($ B7, $ B $ 2: $ B $ 7,0)
примечание: 0 возвращает наибольшее ранжированное значение # 1
13.MINIFS
= MINIFS очень похожа на функцию min, за исключением того, что она позволяет вам брать минимум набора значений и также соответствовать критериям. В этом примере = MINIFS используется для нахождения самой низкой цены для каждого проданного продукта.
Формула:
= МИНИ-РАЗМН (ДИАПАЗОН1, КРИТЕРИИ1, ДИАПАЗОН2)
В этом примере:
= МИНИ-РАЗ ($ B $ B, $ A: $ A, $ E5)
14. MAXIFS
= MAXIFS, как и его аналоги minifs, позволяет вам сопоставлять критерии, но на этот раз он ищет максимальное число.
Формула:
= МАКСИМАЛЬНЫЕ (ДИАПАЗОН1, КРИТЕРИИ1, ДИАПАЗОН2)
В этом примере:
= MAXIFS ($ B $ B, $ A: $ A, $ E5)
15. СУММПРОИЗВ
= СУММПРОИЗВ — отличная функция для расчета средней доходности, ценовых пунктов и маржи. SUMPRODUCT умножает один диапазон значений на соответствующие ему аналоги строки. Это золото анализа данных. В приведенном ниже примере мы вычисляем среднюю отпускную цену всех наших продуктов, умножая цену на количество, а затем делим на общий проданный объем.
Формула:
= СУММПРОИЗВ (ДИАПАЗОН1, ДИАПАЗОН2) / ВЫБРАТЬ ЯЧЕЙКУ
В этом примере:
= СУММПРОИЗВ (B2: B9, C2: C9) / C10
Надеемся, вы нашли это полезным. Если вас интересует анализ данных в Excel, ознакомьтесь с курсом по Excel, который помог сотням тысяч людей освоить Excel.