Полезные функции в экселе

Программа Microsoft Excel способна в значительной мере облегчить пользователю работу с таблицами и числовыми выражениями, автоматизировав её. Этого удается достичь с помощью инструментария данного приложения, и различных его функций. Давайте рассмотрим наиболее полезные функции программы Microsoft Excel.

Одной из самых востребованных функций в программе Microsoft Excel является ВПР (VLOOKUP). С помощью данной функции, можно значения одной или нескольких таблиц, перетягивать в другую. При этом, поиск производится только в первом столбце таблицы. Тем самым, при изменении данных в таблице-источнике, автоматически формируются данные и в производной таблице, в которой могут выполняться отдельные расчеты. Например, данные из таблицы, в которой находятся прейскуранты цен на товары, могут использоваться для расчета показателей в таблице, об объёме закупок в денежном выражении.

ВПР запускается путем вставки оператора «ВПР» из Мастера функций в ту ячейку, где данные должны отображаться.

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

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

Сводную таблицу можно создать во вкладке «Вставка», нажав на кнопку» которая так и называется «Сводная таблица».

Для визуального отображения данных, размещенных в таблице, можно использовать диаграммы. Их можно применять в целях создания презентаций, написания научных работ, в исследовательских целях, и т.д. Программа Microsoft Excel предоставляет широкий набор инструментов для создания различного типа диаграмм.

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

Более точная настройка диаграмм, включая установку её наименования и наименования осей, производится в группе вкладок «Работа с диаграммами».

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

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

Для того, чтобы применить формулу, нужно в ячейке, куда планируется выводить результат, поставить знак «=». После этого, вводится сама формула, которая может состоять из математических знаков, чисел, и адресов ячеек. Для того, чтобы указать адрес ячейки, из которой берутся данные для расчета, достаточно кликнуть по ней мышкой, и её координаты появится в ячейке для вывода результата.

Также, программу Microsoft Excel можно использовать и в качестве обычного калькулятора. Для этого, в строке формул или в любой ячейки просто вводятся математические выражения после знака «=».

Одной из самых популярных функций, которые используются в Excel, является функция «ЕСЛИ». С её помощью можно задать в ячейке вывод одного результата при выполнении конкретного условия, и другого результата, в случае его невыполнения.

Синтаксис данной функции выглядит следующим образом «ЕСЛИ(логическое выражение; [результат если истина]; [результат если ложь])».

С помощью операторов «И», «ИЛИ» и вложенной функции «ЕСЛИ», можно задать соответствие нескольким условиям, или одному из нескольких условий.

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

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

Также, запись макросов можно производить, используя язык разметки Visual Basic, в специальном редакторе.

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

Для того, чтобы перейти к условному форматированию, нужно, находясь во вкладке «Главная», выделить диапазон ячеек, который вы собираетесь отформатировать. Далее, в группе инструментов «Стили» нажать на кнопку, которая так и называется «Условное форматирование». После этого, нужно выбрать тот вариант форматирования, который считаете наиболее подходящим.

Форматирование будет выполнено.

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

Делается это просто. Для начала, выделяем нужный диапазон с данными, а затем, находясь во вкладке «Главная», кликаем по кнопке «Форматировать как таблицу». После этого, появляется список с различными вариантами стилей оформления таблицы. Выбираем наиболее подходящий из них.

Также, таблицу можно создать, нажав на кнопку «Таблица», которая расположена во вкладке «Вставка», предварительно выделив определенную область листа с данными.

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

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

Для того, чтобы использовать эту функцию, нужно находиться во вкладке «Данные». Затем, требуется нажать на кнопку «Анализ «что если»», которая располагается в блоке инструментов «Работа с данными». Потом, выбрать в появившемся списке пункт «Подбор параметра…».

Отрывается окно подбора параметра. В поле «Установить в ячейке» вы должны указать ссылку на ячейку, которая содержит нужную формулу. В поле «Значение» должен быть указан конечный результат, который вы хотите получить. В поле «Изменяя значения ячейки» нужно указать координаты ячейки с корректируемым значением.

Возможности, которые предоставляет функция «ИНДЕКС», в чем-то близки к возможностям функции ВПР. Она также позволяет искать данные в массиве значений, и возвращать их в указанную ячейку.

Синтаксис данной функции выглядит следующим образом: «ИНДЕКС(диапазон_ячеек;номер_строки;номер_столбца)».

Это далеко не полный перечень всех функций, которые доступны в программе Microsoft Excel. Мы остановили внимание только на самых популярных, и наиболее важных из них.

Отблагодарите автора, поделитесь статьей в социальных сетях.

источник

Добрый день уважаемый пользователь!

Эту статью я решил сделать обзорной, и описать в ней ТОП 10 самых полезных функций Excel. Эти знания позволят, вам ознакомится и научится работать с самыми полезными функциями, что значительно увеличит вашу производительность и уменьшит нагрузку на вас, а также сэкономит вам много свободно времени, которое вы можете посвятить всему, что вас вдохновляет. Не стоит недооценивать мощность и силу MS Excel, он ваш верный помощник и товарищ, доверьтесь ему, найдите с ним общий язык и вы удивитесь открытым горизонтам.

Очень многие пользователи игнорируют полезность Excel, и они глубоко заблуждаются, не делайте их ошибок. Приделите, пожалуйста, немного вашего времени для освоения этого вычислительного инструмента, и работа станет просто удовольствием ведь весь груз аналитики и вычислений вы переложите на мощные плечи MS Excel.

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

1. Функция СУММ. Самой первой функцией стоит изучить функцию СУММ, без нее просто не обходится ни одно математическое действие в таблицах, просуммировать ячейки, диапазон ячеек или даже множество разбросанных значений в документе, это всё во власти функции СУММ. Также для удобства использования Excel предоставляет вам возможность использования инструмента «автосумма», что еще более упрощает работу, вам стоит нажать одну кнопочку и весь диапазон чисел будет посчитан в одно мгновение от 2 до миллиона значений, увы, на калькуляторе это будет дольше и не гарантирует правильный результат. Функция таит в себе свои хитрости и секреты, которые вам очень пригодятся.
2. Функция ЕСЛИ. Второй по важности изучения, стоит функция ЕСЛИ. Эта логическая функция позволит вам производит множество логических вычислений по многим условиям. Функция имеет возможность вложения, а это позволит вам работать с вариантами условий. Может быть, вы и испугаетесь некой сложности функции, но не стоит пугаться ее обманчивости – она очень проста и доступна. Максимально полезна она будет для экономистов и любых аналитиков.
3. Функция СУММЕСЛИ. Третьей важной функцией в моем обзоре станет функция СУММЕСЛИ. Эта функция соединяет математические и логические разделы в одном лице и позволит вам собрать и просуммировать значения со всего диапазона по заданному критерию, а это очень поможет, когда строк и столбцов в таблице великое множество. Конечно, есть альтернативы по получению аналогичного результата, но всё же, все остальные варианты будут сложнее. Функция будет очень полезна и бухгалтерам и экономистам.
4. Функция ВПР. Четвёртой по счёту рассмотрим функцию ВПР. Эта функция с раздела «Ссылки и массивы», является одной из самых полезных и мощных функций при работе с массивами. Поиск и работа с полученными данными из массива ваших данных будет эффективным при использовании функции ВПР, но у нее есть одно ограничение, она ищет только в вертикальных списках, хотя данные списки используются в 95%, это компенсирует ее недостаток. А если вам нужно горизонтальный поиск, вам поможет функция ГПР. Аналогом этой функции может стать соединение других функций, таких как ПОИСКПОЗ и ИНДЕКС, но о них отдельно. Очень полезная функция для анализа любых финансовых результатов и построений «дашбордов».
5. Функция СУММЕСЛИМН. Пятой функцией нашего топ списка самых полезных функций Excel станет функция СУММЕСЛИМН. Эта функция может все, что умеет третья функция нашего списка, но только немножко больше, а именно суммировать не по одному критерию, а по многим, всё же 127 поддерживаемых критериев это очень сильно. Не стоит забывать, что для корректной работы со многими критериями и диапазонами необходимо пользоваться абсолютными ссылками. Станет полезной многим бухгалтерам и экономистам при работе с большими объемами данных.
6. Функция ЕОШИБКА. Эта простая функция, которую я предоставил под номером шесть в моем списке ТОП 10, часто спасала меня и помогала получить результат. Достаточно часто мы можем предугадать, что возникнет та или иная ошибка, а если она возникает в средине вычислений, то ломается вся наша вычислительная линейка. Эта функция позволит нам проигнорировать ошибку и подставить вместо нее нужный результат, что позволит сделать намного больше полезных и точных вычислений, особенно актуально применение совместно с логическими функциями. Очень-очень полезная функция, особенно для экономистов и аналитиков, так как при их работе частенько приходится работать с ошибками, которые возникают.
7. Функция ПОИСКПОЗ. Седьмую ступеньку нашей пирамиды занимает функция ПОИСКПОЗ, которая, как и функция ВПР работает с массивами, ищет и возвращает значения согласно заданным критериям. По большому счёту эта функция часто является альтернативой функции ВПР, особенно когда ее совместить в гармоничный симбиоз с функцией ИНДЕКС. В этом случае вы сможете получить ряд преимуществ, как то поиск с левой стороны, поиск значения более чем 255 символов, а также добавлять и удалять столбики в таблицу поиска, а также многое другое. Пригодится любым специалистам, которые работают с большими объемами информации.
8. Функция СЧЁТЕСЛИ. На восьмом месте я разместил функцию СЧЁТЕСЛИ, которая совмещает математическое начало и логическое, своеобразное соединение функции СЧЁТ и функции ЕСЛИ. Эта функция самое-то в случае, когда вам нужно будет сосчитать что-либо и где-либо, это могут быть и текстовые значения, и даты, и числовые значения в массивах и многое другое. Несмотря на то, что функция СЧЁТЕСЛИ производит подсчёт только по одному критерию, всё же ее польза большая, да и этого зачастую с головой хватает. Функция в работе достаточно проста и неприхотлива, да и пригодится в работе специалисту любой финансовой специальности.
9. Функция СУММПРОИЗВ. Предпоследней из списка функций моего топ списка станет функция СУММПРОИЗВ, не стоить думать, что она имеет также последние значение в работе, как раз наоборот, многие из специалистов считают ее одним из первых в работе хороших экономистов. Она отлично работает с массивами данных, несмотря на простоту ее синтаксиса, ее функциональность огромна, и осуществлять поиск и выборку данных с массивов она делает легко, быстро и чётко. Функция станет незаменима в работе для экономических специальностей.
10. Функция ОКРУГЛ. Ну, вот добрались и до конца нашего списка самых полезных функций в Excel, который предоставлен, функцией ОКРУГЛ, с раздела статистических функций. Почему именно ее я включил ее, потому что взял во внимание работу бухгалтера, который когда делает расчёт и у него пропадает копейка, это уже личная трагедия и головная боль. Так что, несмотря на ее простоту и непритязательность, ее польза в правильном предоставлении данных станет очень полезной и нужной. Является важной для бухгалтерских вычислений и получения точного результата.

Ну, вот я вам и описал ТОП 10 самых полезных функций Excel, с помощью которых вы можете значительно упростить свою работу и улучшить ее эффективность. Вы можете перейти по ссылке в описании каждой из функций для получения более детальной информации, изучить примеры работы с нужной вам функцией.

Если же вам нужно еще информация о функциях, вам доступен «Справочник функций», который я регулярно обновляю по количеству и описанию функций. В нем вы сможете расширить свои познания функций MS Excel.

Если статья вам понравилась, ставьте лайки, делитесь с друзьями в социальных сетях, пусть нас станет больше. Если вопросы возникли, жду ваши комментарии!

Сбалансировать бюджет — все равно что попасть в рай. Каждый этого хочет, но не желает делать то, что для этого нужно.
Ф. Грэм

источник

Общее количество функций для работы с электронными таблицами великое множество. Однако среди них есть наиболее полезные для повседневного использования. Мы составили десять самых важных формул Excel 2016 на каждый день.

Для объединения ячеек с текстовым значением можно использовать разные формулы, однако они имеют свои нюансы. Например, команда =СЦЕПИТЬ(D4;E4) успешно объединит две ячейки, равно как и более простая функция =D4&E4, однако никакого разделителя между словами добавлено не будет – они отобразятся слитно.

Избежать данного недочета можно добавляя пробелы, либо в конце текста каждой ячейки, что вряд ли можно назвать оптимальным решением, либо непосредственно в самой формуле, куда в любое место можно вставить набор символов в кавычках, в том числе и пробел. В нашем случае формула =СЦЕПИТЬ(D4;E4) получит вид =СЦЕПИТЬ(D4;” “;E4). Впрочем, если вы объединяете большое количество текстовых ячеек, то аналогичным образом пробел вручную придется прописывать после адреса каждой ячейки.

Другой типовой формулой для склеивания ячеек с текстом является команда ОБЪЕДИНИТЬ. По своему синтаксису она по умолчанию содержит два дополнительных параметра – сначала идет конкретный символ разделения, затем команда ИСТИНА или ЛОЖЬ (в первом случае пустые ячейки из указанного интервала будут игнорироваться, во втором – нет), и потом уже список или интервал ячеек. Между ячейками также можно использовать и обычные текстовые значения в кавычках. Например, формула =ОБЪЕДИНИТЬ(” “;ИСТИНА;D4:F4) склеит три ячейки, пропустив пустые, если таковые имеется, и добавит между словами по пробелу.

Применение: Данная опция часто используется для склеивания ФИО, когда отдельные составные части находятся в разных колонках и есть общая сводная колонка с полным именем человека.

Простой оператор ИЛИ определяет выполнение заданного в скобках условия и на выходе возвращает одно из значений ИСТИНА или ЛОЖЬ. В дальнейшем данная формула может использоваться в качестве составного элемента более сложных условий, когда в зависимости от того, что выдаст значение ИЛИ будет выполняться то или иное действие.

При этом сравниваться могут как численные показатели, применяя знаки >, B2; “Превышение бюджета”; “В пределах бюджета”).

Кроме того, в качестве условия может использоваться другая функция, например, условие ИЛИ и даже еще одно условие ЕСЛИ. При этом у воженных функций ЕСЛИ может быть от 3 до 64 возможных результатов). Как пример, =ЕСЛИ(D4=1; “ДА”;ЕСЛИ(D4=2; “Нет”; “Возможно”)).

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

Для значения чисел можно использовать формулу РАНГ, которая выдаст величину каждого числа относительно других в заданном списке. При этом ранжирование может быть как от меньшего значения в сторону увеличения, так и обратно.

Для данной функции используется три параметра – непосредственно число, массив или ссылка на список чисел и порядок. При этом если порядок не указан или стоит значение 0, то ранг определяется в порядке убывание. Любое другое значение для порядка будет отсортировывать значения по возрастанию.

Применение: Для таблицы с доходами по месяцам можно добавить столбец с ранжированием, а в дальнейшем по этому столбцу сделать сортировку.

Простая, но очень полезная формула МАКС выдает наибольшее значение из списка значений. Сам список может состоять как из ячеек и/или их диапазона, так и вручную введенных чисел. Всего максимальное значение можно искать среди списка из 255 чисел.

Применение: Возвращаясь к примеру с ранжированием, вместо ранга можно выводить значение лучшего показателя за выбранный период.

Аналогичным образом действует формула поиска минимальных значений. Идентичный синтаксис, обратный результат на выходе.

Для получения среднего арифметического из выбранного списка значений также есть своя формула. Однако написание ее в русском языке не столь очевидно. Звучит она как СРЗНАЧ, после чего в скобках указываются либо конкретные значения, либо ссылки на ячейки.

Напоследок, самая ходовая функция, которую знает каждый, когда-либо использовавший электронные таблицы Excel. Сложение производится по формуле СУММ, а в скобках задается интервал или интервалы ячеек, значения которых требуется суммировать.

Куда более интересным вариантом является суммирование ячеек, отвечающих конкретным критериям. Для этого используется оператор СУММЕСЛИ с аргументами диапазон, условие, диапазон суммирования.

Применение: Например, есть список школьников, согласившихся поехать на экскурсию. У каждого есть статус – оплатил он мероприятие или нет. Таким образом, в зависимости от содержимого столбца «Оплатил» значение из столбца «Стоимость» будет считаться или нет. =СУММЕСЛИ(E5:E9; “Да”; F5:F9)

Примечание: Подробную информацию об использовании каждой функции Excel можно найти на официальном сайте Microsoft Office.

источник

В этой ста­тье Вла­ди­мир Шван­ский рас­ска­зы­вает о том, как эффек­тивно исполь­зо­вать Excel в нашей seo-работе.

Когда меня впер­вые посе­тила мысль напи­сать ста­тью о связке Excel + SEO , передо мной встала дилемма: о чём писать, чтобы не про­слыть «капи­та­ном Оче­вид­ность» и в то же время не углуб­ляться в нюансы спе­ци­фи­че­ских инстру­мен­тов, кото­рые мно­гие SEO-спе­ци­а­ли­сты не исполь­зуют в прин­ципе. Я решил пойти самым вер­ным путем: опи­сать методы реше­ния с помо­щью Excel тех SEO-задач, кото­рые я сам решаю еже­дневно.

Но сперва — несколько слов о том, почему важно исполь­зо­вать пра­виль­ные инстру­менты для реше­ния тех или иных задач. Пер­вое, что бро­са­ется в глаза, когда ты захо­дишь на про­филь­ный форум или SEO-блог — про­блема низ­кой тех­ни­че­ской под­ко­ван­но­сти моло­дых спе­ци­а­ли­стов. Такие рас­про­стра­нён­ные в прак­ти­че­ском SEO про­блемы, как сор­ти­ровка и ана­лиз мас­си­вов дан­ных, раз­лич­ные вари­анты работы со стро­ками, агре­га­ция дан­ных и, наобо­рот, их раз­би­тие — всё это боль­шин­ство веб-масте­ров выпол­няет вруч­ную, тратя огром­ное коли­че­ство вре­мени на моно­тон­ные, одно­об­раз­ные и легко авто­ма­ти­зи­ру­е­мые задачи.

Одни пыта­ются найти гото­вое узко­функ­ци­о­наль­ное реше­ние для своей про­блемы: «Помо­гите найти про­грамму для услов­ного сло­же­ния зна­че­ний строк», «Под­ска­жите про­грамму, чтобы выде­лить домен со списка» и т. д. Дру­гие пишут скрипты-реше­ния для всех про­блем, с кото­рыми стал­ки­ва­ются. Тре­тьи исполь­зуют доро­гие про­фес­си­о­наль­ные про­граммы (Deductor для фор­ми­ро­ва­ния сре­зов дан­ных, TextPipe для работы со стро­ками и т.п.) для довольно-таки базо­вых опе­ра­ций.

А ведь боль­шин­ство наших про­блем решает Microsoft Excel (как и Google SpreadSheet, и LibreOffice). Далее — яркие тому дока­за­тель­ства.

При­ме­ня­ется для опре­де­ле­ния длины тек­сто­вого содер­жи­мого ячейки (или тек­ста, задан­ного в фор­муле). При­ме­не­ний, как вы пони­ма­ете, масса. Напри­мер, изме­ре­ние длины анко­ров или мета-тегов на пред­мет пре­вы­ше­ния лимита (для при­мера возь­мём 70 зна­ков для title)

Доба­вим услов­ное фор­ма­ти­ро­ва­ние для нагляд­но­сти:

Строки с дли­ной меньше допу­сти­мого зна­че­ния выде­ляем одним цве­том, больше — дру­гим.

Не очень худо­же­ственно, зато наглядно. Осо­бенно когда дело каса­ется несколь­ких сотен/тысяч мета-тегов. По такому же прин­ципу можно добав­лять новые пра­вила для пара­мет­ров description.

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

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

Транс­фор­ми­рует содер­жи­мое строки (или задан­ного фраг­мента) в про­пис­ные или строч­ные буквы.

Пре­об­ра­зует пер­вые буквы каж­дого слова в строке в про­пис­ные.

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

Как известно, при про­верке основ­ными сер­ви­сами (как след­ствие — и про­грам­мами) все буквы запроса при­во­дятся в строч­ный вид. Итог: таб­лица на несколько тысяч строк вида ЗАПРОС + КОМПАНИЯ, где назва­ние ком­па­нии при­ве­дено с малень­кой буквы. Для даль­ней­шего исполь­зо­ва­ния было необ­хо­димо при­ве­сти всё в чело­ве­че­ский вид.

1. Рас­ще­пил мас­сив по 2-м столб­цам (запрос и назва­ние) с помо­щью функ­ции Дан­ные > Текст по столб­цам.
2. При­ме­нил функ­цию ПРОПНАЧ к столбцу с назва­ни­ями ком­па­ний.
3. Про­из­вёл сцепку с пер­вым столб­цом.

Дан­ное реше­ние про­блемы не един­ствен­ное из воз­мож­ных, но точно самое про­стое.

По-моему, это наи­бо­лее полез­ная в прак­ти­че­ском SEO функ­ция. СЦЕПИТЬ поз­во­ляет объ­еди­нить содер­жи­мое отдель­ных тек­сто­вых бло­ков в одну строку. Это может быть как про­стая сцепка 2-х ячеек, так и более слож­ный вари­ант с под­став­ле­нием тек­сто­вых бло­ков непо­сред­ственно в фор­мулу.

При­мер: допу­стим, вам нужно отпра­вить ссылки с 500 не совсем каче­ствен­ных доме­нов в инстру­мент Disavow Links. Син­так­сис инстру­мента пред­по­ла­гает фор­мат вида domain:ваш_домен.com.ua. Что делать? Про­пи­сы­вать все 500 строк руками? Конечно же, нет. Всё, что вам нужно — это напи­сать:

А затем рас­тя­нуть фор­мулу на весь стол­бец.

Еще один при­мер: у вас есть стол­бец с URL и стол­бец с анко­рами. Нам нужно сфор­ми­ро­вать пол­но­цен­ную ссылку сле­ду­ю­щего вида:

Это несложно, однако тут есть свои нюансы. Заклю­ча­ются они в исполь­зо­ва­нии кавы­чек в тек­сто­вом блоке, пред­ше­ству­ю­щем ссылке (и в блоке, иду­щем сразу за ней). Фор­мула из преды­ду­щего при­мера не сра­бо­тает из-за пута­ницы в одинарных/двойных кавыч­ках.

1. Несе­рьез­ный (отсут­ствует про­фес­си­о­наль­ный вызов)

Делаем два допол­ни­тель­ных столбца (или ячейки) с дан­ными (см. скрин­шот ниже):

Вме­сто пер­вого тек­сто­вого блока в фор­муле исполь­зуем ссылку на первую ячейку, вме­сто вто­рого — на вто­рую. В резуль­тате полу­чаем:

В слу­чае, если вы ука­зы­вали кон­крет­ные ячейки, а не столбцы, не забудьте задать абсо­лют­ные адреса:

2. Серьез­ные (при­сут­ствует про­фес­си­о­наль­ный вызов)

2.1 Исполь­зуем оди­нар­ные кавычки

Хотя син­так­сис ссы­лок с оди­нар­ными кавыч­ками и явля­ется валид­ным, его при­ме­не­ние не совсем кано­нично.

2.2 Исполь­зуем сим­вол кавы­чек (chr(34), символ(34))

У двой­ных кавы­чек есть циф­ро­вой код, а зна­чит, мы можем выве­сти их с помо­щью функ­ции chr (в рус­ской вер­сии «сим­вол»).

Под­счи­ты­вает коли­че­ство ячеек внутри диа­па­зона, удо­вле­тво­ря­ю­щих задан­ному кри­те­рию. Напри­мер, вы хотите поверх­ностно оце­нить раз­бав­лен­ность анкор­ного листа сайта URL ’ами. Чтобы никого не оби­жать, возь­мём не реаль­ный анкор лист, а выду­ман­ный. Напри­мер:

Чтобы при­ки­нуть про­цент URL-раз­бавки анкор-листа, посчи­таем все вхож­де­ния домена нашего сайта (а именно domen.ru) в анкоры. Для этого вве­дем фор­мулу:

Странно, пока­зы­вает ноль. Хоть вроде бы вхож­де­ние домена в анко­рах встре­ча­ется. Дело в том, что, в отли­чие от функ­ции ПОИСК (о ней — далее), кри­те­рий для СЧЁТЕСЛИ необ­хо­димо зада­вать явно и чётко. В нашем слу­чае в списке нет анкора domen.ru. Для ослаб­ле­ния кри­те­риев исполь­зу­ется либо звёз­дочка (любое коли­че­ство сим­во­лов), либо знаки вопроса (одна про­из­воль­ная буква). Для наших целей больше подой­дёт звёз­дочка (она же «асте­риск»).

Полу­чи­лось! Ну, и раз уж мы нашли этот пока­за­тель, заодно можем посчи­тать и отно­си­тель­ный вес анко­ров с вхож­де­нием URL по отно­ше­нию к общему кол-ву анко­ров.

Вни­ма­тель­ный чита­тель, конечно, заме­тит, что функ­ция СЧЁТЗ счи­тает только непу­стые ячейки. В слу­чае выгрузки с сер­виса ана­лиза беклин­ков и боль­шого анкор-листа, полу­чен­ный нами резуль­тат будет некор­рект­ным. К сча­стью, в Excel также есть функ­ция под­счёта и пустых ячеек в диа­па­зоне, нося­щая кра­си­вое назва­ние СЧИТАТЬПУСТОТЫ (англ. COUNTA ).

Итого, наш финаль­ный вари­ант:

Прин­цип такой же, как и в преды­ду­щем при­мере. Глав­ное отли­чие: два пара­метра с диа­па­зо­нами. Пер­вый — для при­ме­не­ния кри­те­рия, вто­рой — для при­ме­не­ния сло­же­ния зна­че­ний.

Воз­вра­щают задан­ное коли­че­ство зна­ков слева (или справа). Как пра­вило, исполь­зу­ются в усто­яв­шейся связке с функ­цией ПОИСК.

Воз­вра­щает номер вхож­де­ния иско­мой под­строки в общую строку. Напри­мер, при­ме­не­ние сле­ду­ю­щей фор­мулы воз­вра­тит «2», так как буква «п» вхо­дит в слово «опти­ми­за­ция» на вто­рой пози­ции:

Оче­видно, что само по себе зна­ние о пози­ции вхож­де­ния под­строки явля­ется мало­по­лез­ным даже в SEO ?

В моей прак­тике исполь­зо­ва­ние связки ЛЕВСИМ + ПОИСК (или ПРАВСИМВ + ПОИСК) встре­ча­лось доста­точно редко. Более того, пока я пишу опи­са­ния и при­меры этих функ­ций, в голове то и дело мель­кает афо­ризм:

У вас есть про­блема. Вы решили исполь­зо­вать регу­ляр­ные выра­же­ния, чтобы её решить. Теперь у вас две про­блемы.

Ведь, как известно, «нет ничего более бес­по­мощ­ного, без­от­вет­ствен­ного и испор­чен­ного, чем сеош­ник, при­бег­нув­ший к функ­циям поиска по под­строке».

Тем не менее, рас­смот­рим при­мер: у нас есть спи­сок URL-ов, и нам необ­хо­димо выде­лить из них непо­сред­ственно домен.

Будем сле­до­вать такой логике: нам надо «найти» точку непо­сред­ственно на слеше после домена, после этого вырвать кусок строки слева — с нуле­вой точки до най­ден­ной нами точки конца домена. Разо­бьем задачу на под­за­дачи.

Что ищем? Слеш. Где ищем? В ячейке с URL . С какой пози­ции ищем? Как мини­мум, с вось­мой, чтобы исклю­чить началь­ные слеши.

Выде­лим под­строку с доме­ном: с начала строки до точки вхож­де­ния слеша.

При опре­де­лен­ной сно­ровке с тек­сто­выми функ­ци­ями Excel можно тво­рить насто­я­щие чудеса.

Кратко суть функ­ции опи­сать сложно, а в офи­ци­аль­ной справке при­ве­дено абсо­лютно непо­нят­ное объ­яс­не­ние. По сути, это «состы­ковка» зна­че­ний раз­ных таб­лиц на осно­ва­нии ана­лиза дан­ных в ячей­ках. Рас­смот­рим, как это рабо­тает на оче­ред­ном вымыш­лен­ном при­мере. Пусть у нас будет спи­сок ссы­ла­ю­щихся на наш сайт доме­нов, анко­ров их ссы­лок, ТИЦ и PR этих сай­тов.

Как мы видим, поря­док сай­тов в этих двух таб­ли­цах раз­нится. Без исполь­зо­ва­ния функ­ций пере­не­сти дан­ные из вто­рой таб­лицы в первую, кроме как «руками», невоз­можно. Попро­буем исполь­зо­вать функ­цию ВПР.

Пер­вый пара­метр, А2, опре­де­ляет, по какому зна­че­нию мы ищем сов­па­де­ния. В нашем слу­чае нам надо «состы­ко­вать» таб­лицу по отдель­ным доме­нам.

• Вто­рой пара­метр, F2 : H11 — это таб­лица с «эта­ло­нами». То есть та, где мы ищем.
• Тре­тий пара­метр, 2 — номер столбца в этой «эта­лон­ной» таб­лице, из кото­рого мы берем зна­че­ния. Слева-направо, в слу­чае с «ТИЦ», зна­че­ние «2».
• Чет­вёр­тый пара­метр (самое важ­ное), ЛОЖЬ — тип сов­па­де­ния. Здесь таится одна из самых боль­ших слож­но­стей этой функ­ции.

ЛОЖЬ озна­чает, что мы ищем точ­ное сов­па­де­ние содер­жи­мого ячейки в таб­лице с эта­ло­нами. ИСТИНА же озна­чает, что при отсут­ствии точ­ного сов­па­де­ния будет исполь­зо­вано бли­жай­шее к нему по убы­ва­нию. Также при исполь­зо­ва­нии ИСТИНЫ реко­мен­дую про­из­во­дить сор­ти­ровку столбца по воз­рас­та­нию, иначе резуль­тат может быть некор­рект­ным. Кстати, в том слу­чае, если в эта­лон­ной ячейке иско­мая ячейка встре­ча­ется несколько раз, будет исполь­зо­вано пер­вое зна­че­ние.

Рабо­тает! Рас­тя­нем фор­мулу на весь стол­бец и дело в шляпе? Нет. Мы задали адрес таб­лицы как отно­си­тель­ный, то есть при рас­тя­ги­ва­нии фор­мулы фокус с эта­лон­ной таб­лицы будет сме­щаться вниз на пустые ячейки. Чтобы это испра­вить, исполь­зуем:

Рабо­тает. Теперь для сосед­него столбца:

Готово. А теперь перей­дём непо­сред­ственно к встро­ен­ному функ­ци­о­налу про­граммы.

Здесь без­услов­ными лиде­рами по полез­но­сти для SEO-спе­ци­а­ли­ста явля­ются 2 функ­ции: очистка от дуб­лей и раз­би­тие дан­ных по столб­цам по раз­де­ли­телю.

Поз­во­ляет очи­стить спи­сок от дуб­лей.

Допу­стим, у нас есть спи­сок доме­нов на 1200 строк. Как вари­ант можно попро­бо­вать найти и убрать дубли «руками», можно отсор­ти­ро­вать спи­сок по алфа­виту и уда­лить «руками» с уже намного мень­шими уси­ли­ями, исполь­зо­вать мак­рос для Excel, исполь­зо­вать софт по работе с клю­че­выми сло­вами (по умол­ча­нию уда­ляет дубли), исполь­зо­вать паб­лик-скрипты или онлайн-сер­висы. Понятно, что если коли­че­ство строк боль­шое (напри­мер, более 1 048 576 строк для Excel), вари­ант со спе­ци­а­ли­зи­ро­ван­ным соф­том или скрип­тами явля­ется един­ственно воз­мож­ным. Но если строк меньше гра­нич­ного мак­си­мума, Excel рабо­тает на ура.

Итак, на старте имеем 1266 доме­нов + aweb.ua:

Кли­каем на шапке столбца, чтобы выде­лить его цели­ком (как вари­ант — тянем выде­ле­ние руками или, клик­нув на пер­вой ячейке с содер­жи­мым, нажи­маем Ctrl+A). Весь наш спи­сок дол­жен быть выде­лен.

Пере­хо­дим во вкладку «Дан­ные» и нахо­дим пункт меню «Уда­лить дуб­ли­каты».

То же самое можно сде­лать и с помо­щью абсо­лютно бес­плат­ного инстру­мента Google Docs Spreadsheet. Также возь­мём спи­сок доме­нов, часть из кото­рых дуб­ли­ру­ется. Для уда­ле­ния дуб­лей исполь­зуем функ­цию:

Так как мас­сив дан­ных у нас лежит в столбце A, в ячейку сосед­него столбца вста­вим фор­мулу:

Готово. В стол­бец B авто­ма­ти­че­ски зальётся мас­сив уни­каль­ных строк. Фор­мулу рас­тя­ги­вать не надо, всё реа­ли­зо­вано через функ­цию CONTINUE .

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

Допу­стим, у нас есть мас­сив дан­ных с раз­де­ли­те­лем вида «пайп» (вер­ти­каль­ная черта).

Нахо­дим во вкладке «Дан­ные» пункт «Текст по столб­цам». Кли­каем, пред­ва­ри­тельно выде­лив нуж­ный нам мас­сив дан­ных. Появ­ля­ется «Мастер рас­пре­де­ле­ния тек­стов по столб­цам»

Жмём «Далее». На вто­ром шаге отме­чаем тип раз­де­ли­теля «Дру­гой» и встав­ляем туда сим­вол вер­ти­каль­ной черты.

На сле­ду­ю­щем шаге не забудьте выста­вить зна­че­ние в поле «Поме­стить в», иначе стол­бец с дан­ными пере­за­пи­шется (хотя в 99% слу­чаев именно это нам и нужно).

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

На этом всё. В даль­ней­шем я пла­ни­рую напи­сать боль­шую ста­тью по исполь­зо­ва­нию свод­ных таб­лиц Excel в SEO — тема не менее инте­рес­ная и объ­ем­ная, чем затро­ну­тая сего­дня. А пока наде­юсь, что дан­ный мате­риал спа­сёт не один деся­ток веб-масте­ров от бес­смыс­лен­ной траты вре­мени на рутин­ные задачи и не только откроет для вас дру­же­ствен­ный мир Excel, но и вдох­но­вит на даль­ней­шие поиски реше­ний по авто­ма­ти­за­ции работы.

источник

Знание основных функций Microsoft Excel – от сводных таблиц до Power View – поможет вам влиться в ряды специалистов по электронным таблицам.

ANTHONY DOMANICO. 11 tricks for Excel power users. PCWorld.

Знание этих функций – от сводных таблиц до Power View – поможет вам влиться в ряды специалистов по электронным таблицам.

Пользователи Microsoft Excel делятся на две категории: представителям первой кое-как удается справляться с маленькими табличками, те же, кто относится ко второй, поражают коллег сложными диаграммами, мощным анализом данных и волшебством эффективного применения формул и макросов. Одиннадцать приемов, которые мы рассмотрим в этой статье, помогут вам стать полноправным членом второй группы.

Функция «ВПР» помогает собрать данные, разбросанные на различных листах или хранящиеся в различных рабочих книгах Excel и разместить их в одном месте для создания отчетов и подсчета итогов.

Предположим, вы оперируете товарами, продаваемыми в розничном магазине. Каждому товару обычно присваивается уникальный инвентаризационный номер, который можно использовать в качестве связующего звена для «ВПР». Формула «ВПР» ищет соответствующий идентификатор на другом листе и подставляет оттуда в указанное место рабочей книги описание товара, его цену, уровень запасов и другие данные.

Функция «ВПР» помогает находить информацию в больших таблицах, содержащих, например, перечень имеющегося ассортимента.

Вставьте в формулу функцию «ВПР», указав в первом ее аргументе искомое значение, по которому осуществляется связь (1). Во втором аргументе задайте диапазон ячеек, в которых следует производить выборку (2), в третьем – номер столбца, из которого будут подставляться данные, а в четвертом введите значение ЛОЖЬ, если хотите найти точное соответствие, или ИСТИНА, если нужен ближайший приблизительный вариант (4).

Создание диаграмм

Для создания диаграммы введите в Excel данные с указанием заголовков столбцов (1), выберите на вкладке «Вставка» пункт «Диаграммы» (2) и укажите требуемый тип диаграммы. В Excel 2013 имеется вкладка «Рекомендуемые диаграммы» (3), на которой присутствуют типы, соответствующие введенным вами данным. После определения общего характера диаграммы Excel открывает вкладку «Конструктор», где производится более точная ее настройка. Огромное количество присутствующих здесь параметров позволяет придать диаграмме тот внешний вид, который вам нужен.

В версии Excel 2013 присутствует вкладка Рекомендуемые диаграммы, на которой отображаются типы диаграмм, соответствующие введенным вами данным.

Функции «ЕСЛИ» и «ЕСЛИОШИБКА»

«ЕСЛИ» и «ЕСЛИОШИБКА» относятся к числу наиболее популярных функций Excel. Функция ЕСЛИ позволяет определить условную формулу, которая при выполнении условия вычисляет одно значение, а при его невыполнении другое. Например, студентам, получившим за экзамен 80 баллов и больше (оценки выставлены в столбце C), можно присвоить признак «Сдал», а тем, кто получил 79 баллов и меньше – признак «Не сдал».

Функция «ЕСЛИОШИБКА» представляет собой частный случай более общей функции «ЕСЛИ». Она возвращает какое-то конкретное значение (или пустое значение), если в процессе вычисления формулы произошла ошибка. К примеру, при выполнении функции ВПР над другим листом или таблицей, функция «ЕСЛИОШИБКА» может возвращать пустое значение в тех случаях, когда «ВПР» не находит искомого параметра, задаваемого первым аргументом.

Функция «ЕСЛИ» вычисляет результат в зависимости от задаваемого вами условия.

Сводная таблица

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

Например, для подсчета среднего балла студентов в зависимости от их возраста, переместите поле «Возраст» в раздел Строки (1), а поля с оценками в раздел «Значения» (2). В меню значений выберите пункт «Параметры полей значений» и в качестве операции укажите «Среднее» (3). Таким же образом можно подсчитывать итоги и по другим категориям, например, вычислять число сдавших и не сдавших экзамен в зависимости от пола.

Сводная таблица – это инструмент для проведения над таблицей различных итоговых расчетов в соответствии с выбранными опорными точками.

Сводная диаграмма

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

В Excel 2013 появились «Рекомендуемые сводные диаграммы». Откройте вкладку «Вставка», перейдите в раздел «Диаграммы» и выберите пункт «Рекомендуемые диаграммы». Переместив указатель мыши на выбранный вариант, вы увидите, как он будет выглядеть. Для создания сводной диаграммы вручную нажмите на вкладке «Вставка» кнопку «Сводная диаграмма».

Сводные диаграммы помогают получать простое для восприятия представление сложных данных.

Мгновенное заполнение

Лучшая, пожалуй, новая функция Excel 2013 – «Мгновенное заполнение» – позволяет эффективно решать повседневные задачи, связанные с быстрым переносом нужных блоков информации из смежных ячеек. В прошлом, при работе со столбцом, представленным в формате «Фамилия, Имя», пользователю приходилось извлекать из него имена вручную или искать какие-то очень сложные обходные пути.

Предположим теперь, что тот же самый столбец с фамилиями и именами присутствует в Excel 2013. Достаточно ввести имя первого человека в ближайшую справа ячейку (1) и на вкладке «Главная» выбрать «Заполнить» и «Мгновенное заполнение». Excel автоматически извлечет все прочие имена и заполнит ими ячейки справа от исходных.

«Мгновенное заполнение» позволяет извлекать нужные блоки информации и заполнять ими смежные ячейки.

Быстрый анализ

Новый инструмент быстрого анализа Excel 2013 помогает ускорить создание диаграмм из простых наборов данных. После выделения данных рядом с правым нижним углом выделенной области появляется характерный значок (1). Щелкнув по нему, вы переходите в меню «Быстрого анализа» (2).

В меню представлены инструменты «Форматирования», «Диаграмм», «Итогов», «Таблиц» и «Спарклайнов». Щелкая мышью по этим инструментам, вы сможете увидеть поддерживаемые ими возможности.

Быстрый анализ ускоряет работу с простыми наборами данных.

Интерактивный инструмент исследования и визуализации данных Power View предназначен для извлечения и анализа больших объемов данных из внешних источников. В Excel 2013 для вызова функции Power View перейдите на вкладку «Вставка» (1) и нажмите кнопку «Отчеты» (2).

Отчеты, созданные с помощью Power View, уже готовы к презентации и поддерживают режимы чтения и полноэкранного представления. Интерактивную их версию можно даже экспортировать в PowerPoint. Руководства по бизнес-анализу, представленные на сайте Microsoft, помогут вам в кратчайшие сроки стать специалистом в этой области.

Режим Power View позволяет создавать интерактивные отчеты готовые к презентации.

Условное форматирование

Расширенные функции условного форматирования Excel позволяют легко и быстро выделять нужные данные. Соответствующий элемент управления находится на вкладке «Главная». Выделите диапазон ячеек, которые требуется отформатировать и нажмите кнопку «Условное форматирование» (2). В подменю «Правила выделения ячеек» (3) перечислены условия форматирования, которые встречаются чаще всего.

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

Транспонирование столбцов в строки и наоборот

Иногда возникает потребность поменять в таблице местами строки и столбцы. Чтобы проделать это, скопируйте нужную область в буфер обмена, щелкните правой кнопкой мыши на левой верхней ячейке области в которую осуществляется вставка и выберите в контекстном меню пункт «Специальная вставка». В появившемся на экране окне установите флажок «Транспонировать» и нажмите OK. Все остальное за вас сделает Excel.

Функция Специальной вставки позволяет транспонировать столбцы и строки.

Важнейшие комбинации клавиш

Приведенные здесь комбинации клавиш особенно полезны для быстрого перемещения по электронным таблицам Excel и выполнения часто встречающихся операций.

источник

Ребята, мы вкладываем душу в AdMe.ru. Cпасибо за то,
что открываете эту красоту. Спасибо за вдохновение и мурашки.
Присоединяйтесь к нам в Facebook и ВКонтакте

Microsoft Excel на сегодняшний день просто незаменим, особенно когда дело касается обработки больших объемов данных. Однако у этой программы столько функций, что непросто разобраться, какие их них действительно нужные и полезные.

И поэтому сегодня AdMe.ru расскажет, какими способами можно эффективно систематизировать информацию и разложить все по полочкам.

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

Как применять:

1. Выберите Вставка > Рекомендуемые сводные таблицы.
2. В диалоговом окне Рекомендуемые сводные таблицы щелкните любой макет сводной таблицы, чтобы увидеть его в режиме предварительного просмотра, а затем выберите тот из них, в котором данные отображаются нужным вам образом. Нажмите кнопку ОК.
3. Excel добавит сводную таблицу на новый лист и отобразит список полей, с помощью которого можно упорядочить данные в таблице.

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

Как применять:

1. Выберите Данные > Работа с данными > Анализ «что если» > Подбор параметра.
2. В поле Установить в ячейке введите ссылку на ячейку, в которой находится нужная формула.
3. В поле Значение введите нужный результат формулы.
4. В поле Изменяя значениеячейки введите ссылку на ячейку, в которой находится корректируемое значение, и нажмите кнопку ОК.

Условное форматирование позволяет быстро выделить на листе важные сведения.

Как применять:

На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование и выберите формулу, которая вам понадобится.

Например, если вам нужно выделить все значения меньше 100, выберите Правила выделения ячеек > Меньше, а затем наберите 100. Перед тем как нажать ОК, можно выбрать формат, который будет применяться для подходящих значений.

Если ВПР помогает находить нужные данные только в первом столбце, то, благодаря функциям ИНДЕКС и ПОИСКПОЗ, можно искать информацию внутри таблицы.

Как применять:

1. Убедитесь, что ячейки с данными образуют сетку, где есть заголовки и названия строк.
2. Используйте функцию ПОИСКПОЗ: сначала, чтобы найти столбец, в котором расположен искомый элемент, и затем еще раз, чтобы перейти к строке с ответом.
3. Вставьте ответы в ИНДЕКС, и Excel сможет указать на ячейку, где эти значения пересекаются.

Например: ИНДЕКС (array, ПОИСКПОЗ (lookup_value, lookup_array, 0), ПОИСКПОЗ (lookup_value, lookup_array, 0)).

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

Как применять:

В версии Excel 2016 необходимо выделить нужные данные и выбрать Вставка > Водопад или Диаграмма > Водопад.

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

источник

Время чтения: 17 минут Нет времени читать? Нет времени?

Excel – программа, которой мы пользуемся практически каждый день, и о том, как она облегчает жизнь большинству пользователей, можно даже не говорить. Но чем же она полезна для интернет-маркетологов? Мы рассмотрим 21 функцию Excel и попробуем ответить на этот вопрос.

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

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

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

Функция ВПР позволяет найти данные в текстовой строке таблицы или диапазоне ячеек и добавить их в другую таблицу. Аббревиатура ВПР расшифровывается как «вертикальный просмотр».

Данная функция состоит из 4 аргументов и представлена следующей формулой:

Рассмотрим каждый из аргументов:

• «Искомое значение» указывают в первом столбце рассматриваемого диапазона ячеек. Данный аргумент может являться значением или ссылкой на ячейку.
• «Таблица». Группа ячеек, в которой выполняется поиск искомого значения и возвращаемого. Диапазон ячеек должен содержать искомое значение в первом столбце и возвращаемое значение – в любом месте.
• «Номер столбца». Номер столбца, содержащий возвращаемое значение.
• «Интервальный просмотр» – необязательный аргумент. Это логическое выражение, определяющее – насколько точное совпадение должна обнаружить функция. В связи с этим условием выделяют 2 функции:

1. ИСТИНА. Эта функция, вводимая по умолчанию, ищет ближайшее к искомому значение. Данные первого столбца должны быть упорядочены по возрастанию или в алфавитном порядке.
2. ЛОЖЬ. Данная функция ищет точное значение в первом столбце.

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

Функции нужно найти данные, соответствующие значению «планшет», которое указано в отдельной ячейке (С3) и выступает в роли искомого значения. Аргумент «таблица» здесь – диапазон поиска от A1:B6; номер столбца, содержащий возвращаемое значение – «2». В итоге получаем следующую формулу: =ВПР(С3;А1:B6;2). Результат – 31325 просмотров в месяц.

В следующих двух примерах применен интервальный просмотр с двумя вариантами функций: ИСТИНА и ЛОЖЬ.

Функция ВПР является одной из самых популярных функций Excel, достаточно сложной для понимания, но чрезвычайно полезной.

Функция ЕСЛИ выполняет проверку заданных условий, выбирая один из двух возможных результатов: 1) Если сравнение истинно; 2) Если сравнение ложно.

Формула функции состоит из трех аргументов и выглядит следующим образом:

• «логическое выражение» – формула;
• «значение если истина» – значение, при котором логическое выражение выполняется;
• «значение если ложь» – значение, при котором логическое выражение не выполняется.

Рассмотрим пример использования обычной функции ЕСЛИ.

Для того чтобы узнать, кто из продавцов выполнил план, а кто нет, нужно ввести следующую формулу:

=ЕСЛИ(B2>30000;«План выполнен»;«План не выполнен»)

Логическое выражение здесь – формула «B2>30000».

«Значение если истина» – «План выполнен».

«Значение если ложь» – «План не выполнен».

Помимо обычной функции ЕСЛИ, которая выдает всего 2 результата – «истина» и «ложь», существуют вложенные функции ЕСЛИ, выдающие от 3 до 64 результатов. В данном случае формула может вмещать в себя несколько функций.

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

Существует еще один способ использования функции ЕСЛИ – для проверки, пуста ячейка или нет. Для этого ее можно использовать вместе с функцией ЕПУСТО.

В этом случае формула будет такой: =ЕСЛИ(ЕПУСТО(номер ячейки);«Пустая»;«Не пустая».

Вместо функции ЕПУСТО также можно использовать другую формулу: «номер ячейки=«» (ничего).

ЕСЛИ – одна из самых популярных функций в Excel, простая и удобная в использовании. Она помогает определить истинность тех или иных значений, получить результаты по разным данным и выявить пустые ячейки, к тому же ее можно использовать в сочетании с другими функциями.

Функция ЕСЛИ является основой других формул: СУММЕСЛИ, СЧЁТЕСЛИ, ЕСЛИОШИБКА, СРЕСЛИ. Мы рассмотрим три из них – СУММЕСЛИ, СЧЁТЕСЛИ и ЕСЛИОШИБКА.

Функция СУММЕСЛИ позволяет суммировать данные, соответствующие определенному условию, находящиеся в указанном диапазоне.

Функция состоит из 3 аргументов и имеет формулу:

«Условие» – аргумент, определяющий какие именно ячейки нужно суммировать. Это может быть текст, число, ссылка на ячейку или функция. Обратите внимание на то, что условия с текстом и математическими знаками необходимо заключать в кавычки.

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

В приведенном ниже примере функция суммировала данные запросов, количество переходов по которым больше 100000.

Если нужно суммировать ячейки в соответствии с несколькими условиями, можно воспользоваться функцией СУММЕСЛИМН.

Формула данной функции имеет следующий вид:

=СУММЕСЛИМН(диапазон_суммирования; диапазон_условия1; условие1; [диапазон_условия2; условие2]; …)

«Диапазон условия 1» и «условие 1» – обязательные аргументы, остальные – необязательные.

Функция СЧЁТЕСЛИ считает количество непустых ячеек, соответствующих заданному условию внутри указанного диапазона.

«Диапазон» – группа ячеек, которые нужно подсчитать.

«Критерий» – условие, согласно которому выбираются ячейки для подсчета.

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

В функции СЧЁТЕСЛИ можно использовать только один критерий. Если же нужно сделать подсчет по нескольким условиям, можно применить функцию СЧЁТЕСЛИМН.

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

«Диапазон условия 1» и «условие 1» – обязательные аргументы, остальные же аргументы необязательны. Можно использовать до 127 пар диапазонов и условий.

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

Функция имеет 2 аргумента и представлена формулой: =ЕСЛИОШИБКА(значение;значение_если_ошибка), где:

• «значение» – формула, которая проверяется на наличие ошибки;
• «значение_если_ошибка» – значение, появляющееся в ячейке в том случае, если вычисление в формуле выдало ошибку.

Предположим, что у вас сломался счетчик аналитики, и в ячейке, в которой нужно указать число посетителей, стоит ноль, а число покупок – 32. Как такое может быть? Функция в данном случае указывает на ошибку и вводит значение, соответствующее ей – «перепроверить».

Функция ЛЕВСИМВ позволяет выделить необходимое количество знаков с левой стороны строки.

Функция состоит из 2 аргументов и представлена формулой: =ЛЕВСИМВ(текст;[число_знаков]), где:

• «текст» – текстовая строка, содержащая знаки, которые необходимо извлечь;
• «число знаков» необязательный аргумент, указывает на количество извлекаемых знаков.

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

К примеру, если вы хотите, чтобы тайтлы были максимально лаконичными и состояли из 60 знаков, функция отсчитает первые 60 символов и покажет, как будет выглядеть тот или иной тайтл. Для этого необходимо составить формулу: =ЛЕВСИМВ(А5;60), где А5 – адрес рассматриваемой ячейки, «60» – число извлекаемых символов.

Функция ПСТР позволяет извлечь необходимое количество символов внутри текста, начиная с указанной позиции.

Формула функции состоит из 3 аргументов:

«Текст» – строка, содержащая символы, которые нужно извлечь.

«Начальная позиция» – позиция знака, с которого начинается извлекаемый текст.

«Число знаков» – количество извлекаемых символов.

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

Функция ПРОПИСН делает все буквы в тексте прописными.

«Текст» здесь – текстовый элемент или ссылка на ячейку.

Функция СТРОЧН делает все буквы в тексте строчными.

Аргумент «текст» – текстовый элемент или адрес ячейки.

Функция ПОИСКПОЗ помогает найти указанный элемент в массиве ячеек и определяет его положение.

«Искомое значение» и «просматриваемый массив» – обязательные аргументы, «тип сопоставления» – необязательный.

Рассмотрим подробнее аргумент «тип сопоставления». Он указывает, каким образом сопоставляется найденное значение с искомым. Существует 3 типа сопоставления:

1 – значение меньше или равно искомому (при указании данного типа нужно учитывать, что просматриваемый массив должен быть упорядочен по возрастанию);

-1 – наименьшее значение, которое больше или равно искомому.

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

Формула функции здесь: =ПОИСКПОЗ(900;B2:B6;1). 900 – искомое значение, B2:B6 – просматриваемый массив, 1 – тип сопоставления (меньше или равно искомому). Результат – «3», то есть третья позиция в указанном диапазоне.

Функция ДЛСТР позволяет определить длину текста, содержащегося в указанной ячейке.

Формула функции имеет всего один аргумент – текст (номер ячейки):

Данную функцию можно использовать для проверки длины символов в description.

Функция СЦЕПИТЬ позволяет объединить несколько текстовых элементов в одну строку. В формуле для объединения элементов указываются как номера ячеек, содержащих текст, так и сам текст. Можно указать до 255 элементов и до 8192 символов.

Для того чтобы объединить текстовые элементы без пробелов, используются следующие формулы:

Аргумент «текст» – текстовый элемент или ссылка на ячейку.

В приведенном ниже примере введена следующая формула: =СЦЕПИТЬ(А2;B2;С2)

Для того же, чтобы слова в строке разделялись пробелами, в формулу необходимо вставить знаки пробелов в кавычках:

=СЦЕПИТЬ(текст1;« »;текст2;« »;текст3;« »)

В следующем примере функция представлена формулой: =СЦЕПИТЬ(A2;» «;B2;» «;C2)

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

=СЦЕПИТЬ(«текст1 »;«текст2 »;«текст3 »)

Функция ПРОПНАЧ преобразует заглавные буквы всех слов в тексте в прописные (верхний регистр), а все остальные буквы – в строчные (нижний регистр).

Функция очень проста в использовании и представлена короткой формулой, имеющей всего один аргумент:

Рассмотрим пример, в котором представлены образцы с различными вариантами написания букв. Функция быстро привела их в читабельное состояние.

Эту функцию очень удобно использовать при составлении списков с именами собственными, для преобразования текстовых элементов, напечатанных строчными, прописными или различными по размеру буквами.

Функция ПЕЧСИМВ позволяет удалить все непечатаемые знаки из текста.

В приведенном примере текст в ячейке A1 содержит непечатаемые знаки конца абзаца.

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

Данная функция удаляет все лишние пробелы между словами.

Формула функции проста: =СЖПРОБЕЛЫ(номер_ячейки)

Функция простая и полезная. Единственный минус состоит в том, что она не различает границ слов, и если внутри него стоят пробелы, функция этого не поймет и не удалит их.

Функция НАЙТИ позволяет обнаружить искомый текст внутри текстовой строки и указывает на начальную позицию этого текста относительно начала просматриваемой строки.

Функция НАЙТИ состоит из 3 аргументов и представлена формулой:

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

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

В данном примере функция представлена следующей формулой: =НАЙТИ(«чай»;A4)

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

Формула функции ИНДЕКС имеет следующий вид:

=ИНДЕКС(массив; номер_строки; [номер_столбца])

«Номер столбца» – необязательный аргумент.

Функцию ИНДЕКС можно использовать вместе с функцией ПОИСКПОЗ с целью замены функции ВПР.

Данная функция проверяет идентичность двух текстов, и, если они совпадают, выдает значение ИСТИНА, если же различаются – значение ЛОЖЬ.

Формула функции: =СОВПАД(текст1;текст2)

Пары слов из строк 1 (A1 и B1) и 2 (A2 и B2) различны по написанию, поэтому функция выдает значение ЛОЖЬ, а слова из 3-й строки абсолютно идентичны, поэтому определяются как ИСТИНА.

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

Логическая функция ИЛИ возвращает значение ИСТИНА, если хотя бы один аргумент в формуле имеет значение ИСТИНА, и значение ЛОЖЬ, если все аргументы имеют значение ЛОЖЬ.

Здесь «логическое значение1» – обязательный аргумент, остальные аргументы – необязательные. В формулу можно добавлять от 1 до 255 логических значений.

Формула в данном примере выдает значение ИСТИНА, так как 2 из 3 аргументов имеют значение ИСТИНА.

Функция И возвращает значение ИСТИНА, если все аргументы в формуле имеют значение ИСТИНА, и значение ЛОЖЬ, если хотя бы один из аргументов имеет значение ЛОЖЬ.

Функция может содержать множество аргументов и имеет формулу:

«Логическое_значение1» – обязательный аргумент, остальные аргументы – необязательные.

В этом примере все аргументы имеют значение ИСТИНА, поэтому и результат ее соответствующий.

Функции И и ИЛИ очень просты в использовании, но если сочетать их вместе или в комбинации с другими функциями (ЕСЛИ и НЕ), можно вывести более сложные и интересные формулы.

Функция СМЕЩ возвращает ссылку на диапазон, отстоящий от ячейки или группы ячеек на указанное число строк и столбцов.

Функция состоит из 5-ти аргументов и представлена следующей формулой:

Рассмотрим каждый из аргументов:

1. «Ссылка». Данный аргумент представляет собой ссылку на ячейку или диапазон ячеек, от которых вычисляется смещение.
2. «Смещение по строкам». Этот аргумент показывает количество строк, которые необходимо отсчитать, чтобы переместить левую верхнюю ячейку массива или одну ячейку в нужное место. Значение аргумента может быть положительным (если отсчет строк ведется вниз) и отрицательным числом (если отсчет строк ведется вверх).
3. «Смещение по столбцам». Здесь указывается количество столбцов, которые нужно отсчитать для того, чтобы переместить ячейку или группу ячеек влево или вправо. Левая верхняя ячейка диапазона при этом должна находиться в указанном месте. Значение аргумента может быть положительным (если отсчет столбца ведется вправо) и отрицательным числом (если отсчет столбца ведется влево).
4. «Высота» – необязательный аргумент. Здесь указывается число строк возвращаемой ссылки. Значение данного аргумента должно быть положительным числом.
5. «Ширина» – необязательный аргумент. Здесь указывается число столбцов возвращаемой ссылки. Значение аргумента должно быть положительным числом.

Рассмотрим пример использования функции СМЕЩ, имеющую следующую формулу: =СМЕЩ(А4;-2;2).

В данной формуле A4 – ссылка на ячейку, от которой вычисляется смещение, С2 – ячейка, на которую ссылается ячейка А4, а в ячейке E2 введена формула с результатом «27» – возвращаемая ссылка.

Итак, мы рассмотрели самые интересные и популярные функции Excel. Могут ли они быть полезны интернет-маркетологу? Безусловно. Они помогут при анализе данных страниц сайта, подсчете количества символов в тайтле и description, преобразовании текста, поиске различных элементов в таблице. Несмотря на то, что некоторые из представленных функций очень просты и понятны, это не умаляет их ценности ни для обычного пользователя, ни для интернет-маркетолога.

источник