Самые полезные функции эксель

Программа 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.

источник

Ребята, мы вкладываем душу в 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 необходимо выделить нужные данные и выбрать Вставка > Водопад или Диаграмма > Водопад.

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

источник

В этой ста­тье Вла­ди­мир Шван­ский рас­ска­зы­вает о том, как эффек­тивно исполь­зо­вать 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, но и вдох­но­вит на даль­ней­шие поиски реше­ний по авто­ма­ти­за­ции работы.

источник

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

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

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

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

Другой вариант предполагает создание таблицы, потом ее заполнение.

То есть, чтобы создать таблицу на базе заполненной информации, надо:

Преобразование данных в таблицу

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

Выделяются все ячейки двумя способами:

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

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

Кнопка для выделения всех ячеек

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

Можно упростить этот процесс и не кликать дважды на каждый из них для открытия.

Для этого все нужные файлы выделяют (с помощью одной мышки или с зажатием Control ) и нажимают Enter .

Как результат, автоматически открываются файлы.

Для быстрого перехода с одного окошка к другому можно использовать Control + Tab .

Открыть два и более файла сразу

Изначально на панели быстрого доступа располагается до трех значков.

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

Для этого нажимают «файл» (располагается чуть ниже самой панели). Появляется окно, где в нижней части левой колонки имеется пункт «параметры».

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

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

Теперь значок на панели быстрого доступа.

При некоторых видах деятельности бывает необходимо вставить диагональную линию.

Это может разделить показатели времени и даты, а также вертикальные и горизонтальные шапки в таблицах.

Чтобы сделать это по отношению к одной ячейке, надо поставить курсор на нее и клацнуть правой кнопкой мыши.

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

Часто также необходимо быстро переместить информацию в другое место документа.

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

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

Используя этот значок, нужно перемещать ячейки до места назначения.

Выделение таблицы с информацией

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

Последующие действия — по предыдущей схеме перемещения (+ зажать Control ) — переносим до нужной локации.

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

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

Для избавления от пустых ячеек в Excel надо:

Процесс удаления пустых ячеек

Для осуществления стандартного поиска по документу достаточно использовать комбинацию Control + F и ввести искомое слово (фразу).

А вот для расширенного поиска необходимы дополнительные знания. То есть, при неуверенности пользователя в каком-то символе можно поставить знак вопроса.

Если надо найти сам знак вопроса, то перед ним ставится знак «⁓».

При необходимости осуществить поиск слова или фразы, где неизвестных символов несколько, то используется знак «*».

При необходимости найти сам знак «*» надо перед ним поставить «⁓».

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

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

Там будет возможность делать настройки и способ сообщения не подходящему участнику об ошибке.

Окно настроек проверки вводимых значений

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

Лайфхаки Excel включают в себя и транспортировку данных.

Преимущество этого метода в исключительной скорости и простоте применения.

Для этого выделяется и копируется ( Control + C ) определенное количество строк в столбце. Затем выбирается ячейка, с которой будет начинаться строка с этими данными, на нее надо клацнуть правой кнопкой мыши.

Появится список, вверху него иконки, надо выбрать ту, что транспортирует (см. рисунок ниже).

Теперь информация полностью перенесена в строку.

Транспортировка данных из столбца в строку

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

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

Затем во вкладке на верхней панели необходимо найти «формат» (на рисунке справа). При его выборе появится список, где стоит навести курсор на пункт «скрыть и отобразить».

В списке по стрелочке предлагается: скрыть, строки, столбцы или лист (точно так же можно отобразить их заново здесь).

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

Как скрыть строки, столбцы или лист

Для объединения ячеек не обязательно действовать по старинке.

Можно просто поставить знак «=» в той ячейке, с которой объединение начнется, затем последовательно нажимать следующие клетки, ставя перед каждой знак «&».

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

Объединяется несколько ячеек с помощью «&»

Это делают с помощью вкладки под названием «формулы» на верхней панели экрана.

Там надо клацнуть на пункт со стрелочкой «текстовые».

Вкладка «формулы» пункт «текстовые»

Здесь появится предлагаемый список текстовых формул, также в нижнем пункте можно вставить свою функцию. Так, «ПРОПИСН» настроит программу на преобразование текста прописными буквами, а «СТРОЧН» — строчными.

Если же включить пункт «ПРОПНАЧ», то начало каждого слова будет написано с заглавной буквы.

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

Но при необходимости оставить ноль(и) непосредственно перед цифрами можно поставить знак апострофа «’».

Примеры ввода нулей перед числовым рядом с апострофом и без него

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

Замена символов происходит после преодоления следующего пути:

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

Значения там уже есть, поэтому приходится вручную регулировать строку, чтобы открыть символы.

Но гораздо проще и быстрее сделать это при помощи автоматического подбора.

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

Это приведет к автоматическому расширению ячейки под заданный размер ее содержимого.

То же самое можно сделать с группой ячеек или столбцов.

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

Часто получается, что надо ввести формулу, пользователь при помощи маркера заполнения тянет результаты вниз (как показано на рисунке выше).

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

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

Это приведет к автоматическому аналогичному заполнению выделенного пространства.

Здесь действует принцип того, что программа видит значение слева.

Если слева нет символов (пробел), то на этой строке процесс и остановится.

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

Тянется результат выражений

Нередко получается, что пользователь заносит в таблицу какие-то аналогичные значения.

Выпадающие списки создаются в качестве своеобразного шаблона.

Он заполняется только изменяющимися данными.

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

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

Появится окошко, где надо будет ввести данные. «Тип данных» предполагает в данном случае название «список».

А там, где источник, обязательно через знак равно пишется имя, которое было дано диапазону. Жмем «Ок» .

Теперь появляется во второй таблице выпадающий список. Он выглядит как стрелочка на шапке таблицы.

Нажимая на нее, пользователь выбирает подходящее значение.

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

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

Сцепление информации с помощью знака равно

Для расцепления необходимо выделить объем информации, который надо расцепить. Затем во вкладке «данные» на верхней панели найти пункт «таблицы по столбцам».

Открывается окно, в котором за три шага надо указать, что хочешь сделать:

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

Часто при этом надо видеть день недели, но пользователь его не увидит в обычном формате.

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

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

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

В строке под словом «тип» есть надпись, впереди которой дописываем «ДДД.», тем самым изменяем формат.

Вверху окна отображается результат. А вот «ДДДД.» дало бы результат в виде полного названия дня недели впереди даты. Жмем «Ок».

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

выбрать ячейку, нажать на нее правой кнопкой и в списке выбрать «вставить примечание».

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

Теперь вверху надо нажать на стрелочку около слова «цвет», а внизу появившегося окошка – «способы заливки».

В способах заливки уже выбирается вкладка «рисунок».

Уже тут можно найти через «рисунок» картинку, которую пользователь хочет вставить в примечание.

Это делается с помощью того файла, где располагается рисунок, жмем на него мышкой и «открыть».

Теперь рисунок появился в окошке с заливкой, осталось нажать «Ок» .

Список с «вставкой примечания»

Существует встроенная функция, называется ДОЛЯ ГОДА.

Чтобы высчитать день рождения — надо взять начальную дату в ячейке 1 и конечную дату в ячейке 2 (то есть, последнюю существующую).

Далее необходимо выбрать базис «фактический».

Третья колонка покажет фактическую цифру, означающую возраст.

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

При помощи функции «подставить» мы можем заменить точку на запятую.

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

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

А через точку с запятой надо ввести в кавычках новый текст, то есть, «,».

Таким образом, состоялась подстава, вместо точки теперь запятая и программа сможет правильно распознать текст.

Но этот текст все еще не является значением.

Поэтому между знаком равно и самой функцией надо вставить ЗНАЧЕН.

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

Значения не с точкой, а запятой

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

Справа от них располагается стрелка вправо, которая позволяет листать их один за другим.

Стрелки для пролистывания листов в программе

А если на эту стрелку нажать правой кнопкой мыши, то появится окошко со списком всех имеющихся листов и можно быстро выбрать нужный.

Скопирован лист 2 в третий

Здесь очень простой трюк, как можно скопировать один лист в другой.

Для этого надо нажать правой кнопкой мыши на открытый лист и одновременно зажать клавишу Control .

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

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

Многие из них значительно помогают в процессе, некоторые просто незаменимы.

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

ТОП-25 секретов и возможностей Excel: Самые полезные лайфхаки для работы

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

источник

Время чтения: 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, преобразовании текста, поиске различных элементов в таблице. Несмотря на то, что некоторые из представленных функций очень просты и понятны, это не умаляет их ценности ни для обычного пользователя, ни для интернет-маркетолога.

источник