Что такое категория в excel. Функции в Excel. Общее описание, назначение. Минимум из выбранных значений
Введение
1: MicrosoftExcel
1.1 Понятие и возможности MS Excel
1.2 Основные элементы окна MS Excel
1.4 Возможные ошибки при использовании функций в формулах
2: Анализ данных. Использование сценариев
2.1 Анализ данных в MS Excel
2.2 Сценарии
2.3 Пример расчета внутренней скорости оборота инвестиций
Заключение
Список литературы
Введение
Microsoft Office , самое популярное семейство офисных программных продуктов, включает в себя новые версии знакомых приложений, которые поддерживают технологии Internet, и позволяют создавать гибкие интернет-решения
Microsoft Office - семейство программных продуктов Microsoft, которое объединяет самые популярные в мире приложения в единую среду, идеальную для работы с информацией. В Microsoft Office входят текстовый процессор Microsoft Word, электронные таблицы Microsoft Excel, средство подготовки и демонстрации презентаций Microsoft PowerPoint и новое приложение Microsoft Outlook. Все эти приложения составляют Стандартную редакцию Microsoft Office. В Профессиональную редакцию входит также СУБД Microsoft Access.
Microsoft Excel – программа предназначенная для организации данных в таблице для документирования и графического представления информации.
Программа MSExcel применяется при создании комплексных документов в которых необходимо:
· использовать одни и те же данные в разных рабочих листах;
· изменить и восстанавливать связи.
Преимуществом MSExcel является то, что программа помогает оперировать большими объемами информации. рабочие книги MSExcel предоставляют возможность хранения и организации данных, вычисление суммы значений в ячейках. MsExcel предоставляет широкий спектр методов позволяющих сделать информацию простой для восприятия.
В наше время, каждому человеку важно знать и иметь навыки в работе с приложениями Microsoft Office, так как современный мир насыщен огромным количеством информацией, с которой просто необходимо уметь работать.
Более подробно в этой курсовой будет представлено приложение MSExcel, его функции и возможности. А также использование сценариев с их практическим применением.
1. Microsoft Excel
1.1 . Microsoft Excel . Понятия и возможности
Табличный процессор MS Excel (электронные таблицы) – одно из наиболее часто используемых приложений пакета MS Office, мощнейший инструмент в умелых руках, значительно упрощающий рутинную повседневную работу. Основное назначение MS Excel – решение практически любых задач расчетного характера, входные данные которых можно представить в виде таблиц. Применение электронных таблиц упрощает работу с данными и позволяет получать результаты без программирования расчётов. В сочетании же с языком программирования Visual Basic for Application (VBA), табличный процессор MS Excel приобретает универсальный характер и позволяет решить вообще любую задачу, независимо от ее характера.
Особенность электронных таблиц заключается в возможности применения формул для описания связи между значениями различных ячеек. Расчёт по заданным формулам выполняется автоматически. Изменение содержимого какой-либо ячейки приводит к пересчёту значений всех ячеек, которые с ней связаны формульными отношениями и, тем самым, к обновлению всей таблицы в соответствии с изменившимися данными.
Основные возможности электронных таблиц:
1. проведение однотипных сложных расчётов над большими наборами данных;
2. автоматизация итоговых вычислений;
3. решение задач путём подбора значений параметров;
4. обработка (статистический анализ) результатов экспериментов;
5. проведение поиска оптимальных значений параметров (решение оптимизационных задач);
6. подготовка табличных документов;
7. построение диаграмм (в том числе и сводных) по имеющимся данным;
8. создание и анализ баз данных (списков).
1.2. Основные элементы окна MS Excel
Основными элементами рабочего окна являются:
1. Строка заголовка (в ней указывается имя программы) с кнопками управления окном программы и окном документа (Свернуть, Свернуть в окно или Развернуть во весь экран, Закрыть);
2. Строка основного меню (каждый пункт меню представляет собой набор команд, объединенных общей функциональной направленностью) плюс окно для поиска справочной информации.
3. Панели инструментов (Стандартная, Форматирование и др.).
4. Строка формул, содержащая в качестве элементов поле Имя и кнопку Вставка функции (fx), предназначена для ввода и редактирования значений или формул в ячейках. В поле Имя отображается адрес текущей ячейки.
5. Рабочая область (активный рабочий лист).
6. Полосы прокрутки (вертикальная и горизонтальная).
7. Набор ярлычков (ярлычки листов) для перемещения между рабочими листами.
8. Строка состояния.
1.3 Структура электронных таблиц
Файл, созданный средствами MS Excel, принято называть рабочей книгой. Рабочих книг создать можно столько, сколько позволит наличие свободной памяти на соответствующем устройстве памяти. Открыть рабочих книг можно столько, сколько их создано. Однако активной рабочей книгой может быть только одна текущая (открытая) книга.
Рабочая книга представляет собой набор рабочих листов, каждый из которых имеет табличную структуру. В окне документа отображается только текущий (активный) рабочий лист, с которым и ведётся работа. Каждый рабочий лист имеет название, которое отображается на ярлычке листа в нижней части окна. С помощью ярлычков можно переключаться к другим рабочим листам, входящим в ту же рабочую книгу. Чтобы переименовать рабочий лист, надо дважды щёлкнуть мышкой на его ярлычке и заменить старое имя на новое или путём выполнения следующих команд: меню Формат, строка Лист в списке меню, Переименовать. А можно и, установив указатель мышки на ярлык активного рабочего листа, щёлкнуть правой кнопкой мыши, после чего в появившемся контекстном меню щёлкнуть по строке Переименовать и выполнить переименование. В рабочую книгу можно добавлять (вставлять) новые листы или удалять ненужные. Вставку листа можно осуществить путём выполнения команды меню Вставка, строка Лист в списке пунктов меню. Вставка листа произойдёт перед активным листом. Выполнение вышеизложенных действий можно осуществить и с помощью контекстного меню, которое активизируется нажатием правой кнопки мышки, указатель которой должен быть установлен на ярлычке соответствующего листа. Чтобы поменять местами рабочие листы нужно указатель мышки установить на ярлычок перемещаемого листа, нажать левую кнопку мышки и перетащить ярлычок в нужное место.
Рабочий лист (таблица) состоит из строк и столбцов. Столбцы озаглавлены прописными латинскими буквами и, далее, двухбуквенными комбинациями. Всего рабочий лист содержит 256 столбцов, поименованных от A до IV. Строки последовательно нумеруются числами от 1 до 65536.
На пересечении столбцов и строк образуются ячейки таблицы. Они являются минимальными элементами, предназначенными для хранения данных. Каждая ячейка имеет свой адрес. Адрес ячейки состоит из имени столбца и номера строки, на пересечении которых расположена ячейка, например, A1, B5, DE324. Адреса ячеек используются при записи формул, определяющих взаимосвязь между значениями, расположенными в разных ячейках. В текущий момент времени активной может быть только одна ячейка, которая активизируется щелчком мышки по ней и выделяется рамкой. Эта рамка в Excel играет роль курсора. Операции ввода и редактирования данных всегда производятся только в активной ячейке.
На данные, расположенные в соседних ячейках, образующих прямоугольную область, можно ссылаться в формулах как на единое целое. Группу ячеек, ограниченную прямоугольной областью, называют диапазоном. Наиболее часто используются прямоугольные диапазоны, образующиеся на пересечении группы последовательно идущих строк и группы последовательно идущих столбцов. Диапазон ячеек обозначают, указывая через двоеточие адрес первой ячейки и адрес последней ячейки диапазона, например, B5:F15. Выделение диапазона ячеек можно осуществить протягиванием указателя мышки от одной угловой ячейки до противоположной ячейки по диагонали. Рамка текущей (активной) ячейки при этом расширяется, охватывая весь выбранный диапазон.
Для ускорения и упрощения вычислительной работы Excel предоставляет в распоряжение пользователя мощный аппарат функций рабочего листа, позволяющих осуществлять практически все возможные расчёты.
В целом MS Excel содержит более 400 функций рабочего листа (встроенных функций). Все они в соответствии с предназначением делятся на 11 групп (категорий):
1. финансовые функции;
2. функции даты и времени;
3. арифметические и тригонометрические (математические) функции;
4. статистические функции;
5. функции ссылок и подстановок;
6. функции баз данных (анализа списков);
7. текстовые функции;
8. логические функции;
9. информационные функции (проверки свойств и значений);
10.инженерные функции;
11.внешние функции.
Запись любой функции в ячейку рабочего листа обязательно начинается с символа равно (=). Если функция используется в составе какой-либо другой сложной функции или в формуле (мегаформуле), то символ равно (=) пишется перед этой функцией (формулой). Обращение к любой функции производится указанием её имени и следующего за ним в круглых скобках аргумента (параметра) или списка параметров. Наличие круглых скобок обязательно, именно они служат признаком того, что используемое имя является именем функции. Параметры списка (аргументы функции) разделяются точкой с запятой (;). Их количество не должно превышать 30, а длина формулы, содержащей сколько угодно обращений к функциям, не должна превышать 1024 символов. Все имена при записи (вводе) формулы рекомендуется набирать строчными буквами, тогда правильно введённые имена будут отображены прописными буквами.
1.4 Возможные ошибки при использовании функций в формулах
Использование программы Microsoft Excel давным-давно стало обязательным во многих отраслях нашей жизни. Будь вы студентом, математиком, бухгалтером, вам в любом случае понадобится данная программа. При устройстве на работу умение пользоваться офисными программами является обязательным практически для любой должности. Сегодня мы разберём функции в Excel. Считайте данную публикацию небольшим вводным уроком на данную тему.
Определение
Так что же такое функции в Excel? Если говорить кратко, то это выражения, способные заменить целый ряд математических и логических операций. Они предназначены для упрощения вычислений и для создания массивных формул. Все функции разделены и упорядочены по категориям, в зависимости от их назначения. Несомненным плюсом является то, что даже если вы не знаете, как именно пишется функция и в каком порядке необходимо поставить аргументы, программа сама вам подскажет, что именно и как должно быть написано. Если же вы примерно помните, как звучат функции в Excel, то можете воспользоваться поиском. Для этого нажмите на "Вставка", затем "Функция" и в строке поиска введите первые буквы.
Основы
Если вы воспользуетесь вставкой фунции в Excel, то первое, что вам предложат на выбор - это последние 10 функций, которые вы использовали. Очень удобно для тех, кто применяет обычно несколько формул, но с особо сложными в написании функциями.
По умолчанию, там будут простейшие выражения, которыми зачастую пользуются люди. Это сумма чисел, вычисление среднего значения, подсчет, ссылки, условия.
Пользуясь "Вставкой функций", вы могли увидеть, что внизу окна с выбором интересующей вас команды пишется её синтаксис, то есть что и в каком порядке должно быть указано для конкретной функции. Разберём пару примеров.
- СУММ (число1; число2; ...) - данное выражение возвращает готовую сумму всех аргументов, перечисленных за скобкой. Конечно, в обычной ситуации это не очень удобно, но вместо чисел вы можете указать отдельные ячейки таблицы или целые блоки. Для этого зажмите Ctrl и щелкайте указателем мышки по нужным ячейкам. Или же, зажав Shift, растяните рамку на нужный вам диапазон.
- МАКС (значение1; значение2; ...) - вернет вам максимальное значение среди всех чисел под скобкой. Синтаксис такой же, как и в предыдущем случае.
Все функции в Excel разделены по подгруппам, чтобы их было проще искать пользователю. Поскольку их слишком много, мы не будем приводить весь список, но учтите, что зачастую их можно запросто найти через поиск, ведь пишутся они всего двумя способами: либо так же как в математике (например, Log, Cos), либо по описанию команды (пример: ОКРУГЛ (число; количество_разрядов)).
Условия
Условные функции стоят отдельным столпом в Excel. Они предназначены для того, чтобы выполнять определённые действия. Функции с условиями в Excel могут использоваться в качестве аргументов других функции. Применяются также числа и даже целые выражения. Давайте разберем это на примере.
Итак, у нас есть ячейка А1=ЕСЛИ(В1<С1;"Истина";"Ложь"). Что мы видим? Первым идет логическое выражение. Это условие, от которого зависит, что же будет напечатано в ячейке. Далее "истина" - то значение, которое примет ячейка в случае, когда логическое выражение верно. Как нетрудно догадаться, "ложь" - это обратное значение, принимаемое ячейкой при неверности логического выражения.
Сразу стоит отметить, если вы хотите, чтобы функция вернула вам какой-либо текст, то его в обязательном порядке нужно брать в кавычки. Кроме текста, условная функция может вернуть как просто число, так и провести сложнейшие вычисления, для этого надо просто вместо аргумента указать необходимые данные.
Итог
При работе с функциями помните, что аргументы под скобками должны разделяться исключительно точкой с запятой, иначе программа может не распознать команду или одно из выражений, и потом будет очень непросто найти, где же была совершена ошибка. В целом значение функций в Excel сложно переоценить. Они облегчают жизнь каждому, кто работает с программой.
В этой статье Владимир Шванский рассказывает о том, как эффективно использовать Excel в нашей seo-работе.
Когда меня впервые посетила мысль написать статью о связке Excel + SEO , передо мной встала дилемма: о чём писать, чтобы не прослыть «капитаном Очевидность» и в то же время не углубляться в нюансы специфических инструментов, которые многие SEO-специалисты не используют в принципе. Я решил пойти самым верным путем: описать методы решения с помощью Excel тех SEO-задач, которые я сам решаю ежедневно.
Но сперва - несколько слов о том, почему важно использовать правильные инструменты для решения тех или иных задач. Первое, что бросается в глаза, когда ты заходишь на профильный форум или SEO-блог - проблема низкой технической подкованности молодых специалистов. Такие распространённые в практическом SEO проблемы, как сортировка и анализ массивов данных, различные варианты работы со строками, агрегация данных и, наоборот, их разбитие - всё это большинство веб-мастеров выполняет вручную, тратя огромное количество времени на монотонные, однообразные и легко автоматизируемые задачи.
Одни пытаются найти готовое узкофункциональное решение для своей проблемы: «Помогите найти программу для условного сложения значений строк», «Подскажите программу, чтобы выделить домен со списка» и т. д. Другие пишут скрипты-решения для всех проблем, с которыми сталкиваются. Третьи используют дорогие профессиональные программы (Deductor для формирования срезов данных, TextPipe для работы со строками и т.п.) для довольно-таки базовых операций.
А ведь большинство наших проблем решает Microsoft Excel (как и Google SpreadSheet, и LibreOffice). Далее - яркие тому доказательства.
Функция № 1: ДЛСТР (англ LEN )
Применяется для определения длины текстового содержимого ячейки (или текста, заданного в формуле). Применений, как вы понимаете, масса. Например, измерение длины анкоров или мета-тегов на предмет превышения лимита (для примера возьмём 70 знаков для title)
Добавим условное форматирование для наглядности:
Строки с длиной меньше допустимого значения выделяем одним цветом, больше - другим.
И получаем:
Не очень художественно, зато наглядно. Особенно когда дело касается нескольких сотен/тысяч мета-тегов. По такому же принципу можно добавлять новые правила для параметров description.
Функция № 2: СЖПРОБЕЛЫ (TRIM )
Удаляет все пробелы, кроме одинарных между словами из содержимого ячейки или заданного фрагмента текста.
На практике функция полезна, когда при копировании всего массива текста появляются пробелы до/после/между слов, создающие проблемы при дальнейшей обработке.
Функции № 3: ПРОПИСН (UPPER ), СТРОЧН (LOWER )
Трансформирует содержимое строки (или заданного фрагмента) в прописные или строчные буквы.
Функция № 4: ПРОПНАЧ (PROPER )
Преобразует первые буквы каждого слова в строке в прописные.
Забавно, изначально я не хотел добавлять эту функцию. Казалось бы, кому нужно трансформировать первую букву каждого слова? А параллельно с написанием статьи возникла необходимость проверить частотность группы ключей, содержащих названия компаний.
Как известно, при проверке основными сервисами (как следствие - и программами) все буквы запроса приводятся в строчный вид. Итог: таблица на несколько тысяч строк вида ЗАПРОС + КОМПАНИЯ, где название компании приведено с маленькой буквы. Для дальнейшего использования было необходимо привести всё в человеческий вид.
- Расщепил массив по 2-м столбцам (запрос и название) с помощью функции Данные > Текст по столбцам .
- Применил функцию ПРОПНАЧ к столбцу с названиями компаний.
- Произвёл сцепку с первым столбцом.
Данное решение проблемы не единственное из возможных, но точно самое простое.
Функция № 5: СЦЕПИТЬ (текст1;текст2;текст3…) (англ. CONCATENATE )
По-моему, это наиболее полезная в практическом SEO функция. СЦЕПИТЬ позволяет объединить содержимое отдельных текстовых блоков в одну строку. Это может быть как простая сцепка 2-х ячеек, так и более сложный вариант с подставлением текстовых блоков непосредственно в формулу.
Пример: допустим, вам нужно отправить ссылки с 500 не совсем качественных доменов в инструмент Disavow Links . Синтаксис инструмента предполагает формат вида domain:ваш_домен.com.ua. Что делать? Прописывать все 500 строк руками? Конечно же, нет. Всё, что вам нужно - это написать:
СЦЕПИТЬ("domain:";адрес_ячейки)
А затем растянуть формулу на весь столбец.
Еще один пример: у вас есть столбец с URL и столбец с анкорами. Нам нужно сформировать полноценную ссылку следующего вида:
Это несложно, однако тут есть свои нюансы. Заключаются они в использовании кавычек в текстовом блоке, предшествующем ссылке (и в блоке, идущем сразу за ней). Формула из предыдущего примера не сработает из-за путаницы в одинарных/двойных кавычках.
Варианты решения
1. Несерьезный (отсутствует профессиональный вызов)
Делаем два дополнительных столбца (или ячейки) с данными (см. скриншот ниже):
Вместо первого текстового блока в формуле используем ссылку на первую ячейку, вместо второго - на вторую. В результате получаем:
СЦЕПИТЬ(адрес_ячейки_с_началом;адрес_ячейки_с_URL;адрес_замыкающей ячейки;адрес_ячейки_анкора;"")
В случае, если вы указывали конкретные ячейки, а не столбцы, не забудьте задать абсолютные адреса:
2. Серьезные (присутствует профессиональный вызов)
2.1 Используем одинарные кавычки
Хотя синтаксис ссылок с одинарными кавычками и является валидным , его применение не совсем канонично.
2.2 Используем символ кавычек (chr(34), символ(34))
У двойных кавычек есть цифровой код, а значит, мы можем вывести их с помощью функции chr (в русской версии «символ»).
Функция № 6: СЧЁТЕСЛИ (диапазон;критерий) (англ. COUNTIF )
Подсчитывает количество ячеек внутри диапазона, удовлетворяющих заданному критерию. Например, вы хотите поверхностно оценить разбавленность анкорного листа сайта URL ’ами. Чтобы никого не обижать, возьмём не реальный анкор лист , а выдуманный. Например:
Чтобы прикинуть процент URL-разбавки анкор-листа, посчитаем все вхождения домена нашего сайта (а именно domen.ru) в анкоры. Для этого введем формулу:
СЧЁТЕСЛИ(A1:A9;"domen.ru")
Странно, показывает ноль. Хоть вроде бы вхождение домена в анкорах встречается. Дело в том, что, в отличие от функции ПОИСК (о ней - далее), критерий для СЧЁТЕСЛИ необходимо задавать явно и чётко. В нашем случае в списке нет анкора domen.ru. Для ослабления критериев используется либо звёздочка (любое количество символов), либо знаки вопроса (одна произвольная буква). Для наших целей больше подойдёт звёздочка (она же «астериск»).
СЧЁТЕСЛИ(A1:A9;"*domen.ru*")
Получилось! Ну, и раз уж мы нашли этот показатель, заодно можем посчитать и относительный вес анкоров с вхождением URL по отношению к общему кол-ву анкоров.
СЧЁТЕСЛИ(A1:A9;"*domen.ru*")/СЧЁТЗ(A1:A9)
Внимательный читатель, конечно, заметит, что функция СЧЁТЗ считает только непустые ячейки. В случае выгрузки с сервиса анализа беклинков и большого анкор-листа, полученный нами результат будет некорректным. К счастью, в Excel также есть функция подсчёта и пустых ячеек в диапазоне, носящая красивое название СЧИТАТЬПУСТОТЫ (англ. COUNTA ).
Итого, наш финальный вариант:
СЧЁТЕСЛИ(A1:A9;"*domen.ru*")/(СЧЁТЗ(A1:A9)+СЧИТАТЬПУСТОТЫ(A1:A9))
Функция № 7: СУМЕСЛИ (диапазон;критерий;диапазон_для_сложения) (англ. SUMIF )
Принцип такой же, как и в предыдущем примере. Главное отличие: два параметра с диапазонами. Первый - для применения критерия, второй - для применения сложения значений.
Функции № 8: ЛЕВСИМВ (текст;количество знаков) (англ. (LEFT ), ПРАВСИМВ (текст;количество знаков) (англ. RIGHT )
Возвращают заданное количество знаков слева (или справа). Как правило, используются в устоявшейся связке с функцией ПОИСК.
Функция № 9: ПОИСК (искомый фрагмент, просматриваемый текст,начальная позиция) (англ. SEARCH )
Возвращает номер вхождения искомой подстроки в общую строку. Например, применение следующей формулы возвратит «2», так как буква «п» входит в слово «оптимизация » на второй позиции:
ПОИСК ("п";"оптимизация")
Очевидно, что само по себе знание о позиции вхождения подстроки является малополезным даже в SEO 🙂
В моей практике использование связки ЛЕВСИМ + ПОИСК (или ПРАВСИМВ + ПОИСК) встречалось достаточно редко. Более того, пока я пишу описания и примеры этих функций, в голове то и дело мелькает афоризм:
У вас есть проблема. Вы решили использовать регулярные выражения, чтобы её решить. Теперь у вас две проблемы.
Ведь, как известно, «нет ничего более беспомощного, безответственного и испорченного, чем сеошник , прибегнувший к функциям поиска по подстроке».
Тем не менее, рассмотрим пример: у нас есть список URL-ов, и нам необходимо выделить из них непосредственно домен.
Будем следовать такой логике: нам надо «найти» точку непосредственно на слеше после домена, после этого вырвать кусок строки слева - с нулевой точки до найденной нами точки конца домена. Разобьем задачу на подзадачи.
Что ищем? Слеш. Где ищем? В ячейке с URL . С какой позиции ищем? Как минимум, с восьмой, чтобы исключить начальные слеши.
ПОИСК("/";ячейка_с_URL;8)
Выделим подстроку с доменом: с начала строки до точки вхождения слеша.
ЛЕВСИМВ(ячейка_URL;ПОИСК("/";ячейка_URL;8))
При определенной сноровке с текстовыми функциями Excel можно творить настоящие чудеса.
Функция № 10: ВПР (искомое_значение, таблица, номер_столбца, тип_совпадения) (англ. VLOOKUP )
Кратко суть функции описать сложно, а в официальной справке приведено абсолютно непонятное объяснение. По сути, это «состыковка» значений разных таблиц на основании анализа данных в ячейках. Рассмотрим, как это работает на очередном вымышленном примере. Пусть у нас будет список ссылающихся на наш сайт доменов, анкоров их ссылок, ТИЦ и PR этих сайтов.
Как мы видим, порядок сайтов в этих двух таблицах разнится. Без использования функций перенести данные из второй таблицы в первую, кроме как «руками», невозможно. Попробуем использовать функцию ВПР.
ВПР(A2;F2:H11;2;ЛОЖЬ)
Первый параметр, А2, определяет, по какому значению мы ищем совпадения. В нашем случае нам надо «состыковать» таблицу по отдельным доменам.
- Второй параметр, F2 :H11 - это таблица с «эталонами». То есть та, где мы ищем.
- Третий параметр, 2 - номер столбца в этой «эталонной» таблице, из которого мы берем значения. Слева-направо, в случае с «ТИЦ», значение «2».
- Четвёртый параметр (самое важное), ЛОЖЬ - тип совпадения. Здесь таится одна из самых больших сложностей этой функции.
ЛОЖЬ означает, что мы ищем точное совпадение содержимого ячейки в таблице с эталонами. ИСТИНА же означает, что при отсутствии точного совпадения будет использовано ближайшее к нему по убыванию. Также при использовании ИСТИНЫ рекомендую производить сортировку столбца по возрастанию, иначе результат может быть некорректным. Кстати, в том случае, если в эталонной ячейке искомая ячейка встречается несколько раз, будет использовано первое значение.
Работает! Растянем формулу на весь столбец и дело в шляпе? Нет. Мы задали адрес таблицы как относительный, то есть при растягивании формулы фокус с эталонной таблицы будет смещаться вниз на пустые ячейки. Чтобы это исправить, используем:
ВПР(A2;$F$2:$H$11;2;ЛОЖЬ)
Работает. Теперь для соседнего столбца:
Готово. А теперь перейдём непосредственно к встроенному функционалу программы.
Здесь безусловными лидерами по полезности для SEO-специалиста являются 2 функции: очистка от дублей и разбитие данных по столбцам по разделителю.
Функция № 11: Данные > Удаление дубликатов (Data > Remove Duplicates)
Позволяет очистить список от дублей.
Допустим, у нас есть список доменов на 1200 строк. Как вариант можно попробовать найти и убрать дубли «руками», можно отсортировать список по алфавиту и удалить «руками» с уже намного меньшими усилиями, использовать макрос для Excel, использовать софт по работе с ключевыми словами (по умолчанию удаляет дубли), использовать паблик-скрипты или онлайн-сервисы. Понятно, что если количество строк большое (например, более 1 048 576 строк для Excel), вариант со специализированным софтом или скриптами является единственно возможным. Но если строк меньше граничного максимума, Excel работает на ура.
Итак, на старте имеем 1266 доменов + aweb.ua:
Кликаем на шапке столбца, чтобы выделить его целиком (как вариант - тянем выделение руками или, кликнув на первой ячейке с содержимым, нажимаем Ctrl+A). Весь наш список должен быть выделен.
Переходим во вкладку «Данные» и находим пункт меню «Удалить дубликаты».
Кликаем «Ок».
То же самое можно сделать и с помощью абсолютно бесплатного инструмента Google Docs Spreadsheet. Также возьмём список доменов, часть из которых дублируется. Для удаления дублей используем функцию:
UNIQUE (массив)
Так как массив данных у нас лежит в столбце A, в ячейку соседнего столбца вставим формулу:
UNIQUE(A1:A841)
Готово. В столбец B автоматически зальётся массив уникальных строк. Формулу растягивать не надо, всё реализовано через функцию CONTINUE .
Функция № 12: Данные > Текст по столбцам (Data > Text to Columns)
Крайне полезная функция, которая позволяет разбивать различные массивы на составляющие по отдельным столбцам. Также позволяет задать любой разделитель на ваш выбор (слеш, точку, запятую и т.п.). Например, мы можем без использования регулярных выражений и функций поиска по строке легко и быстро извлечь домены из списка различных URL .
Допустим, у нас есть массив данных с разделителем вида «пайп» (вертикальная черта).
Находим во вкладке «Данные» пункт «Текст по столбцам». Кликаем, предварительно выделив нужный нам массив данных. Появляется «Мастер распределения текстов по столбцам»
На следующем шаге не забудьте выставить значение в поле «Поместить в», иначе столбец с данными перезапишется (хотя в 99% случаев именно это нам и нужно).
Готово! Несмотря на всю кажущуюся простоту, разбивка на столбцы по заданному разделителю является одной из наиболее часто используемых и полезных SEO-функций программы.
На этом всё. В дальнейшем я планирую написать большую статью по использованию сводных таблиц Excel в SEO - тема не менее интересная и объемная, чем затронутая сегодня. А пока надеюсь, что данный материал спасёт не один десяток веб-мастеров от бессмысленной траты времени на рутинные задачи и не только откроет для вас дружественный мир Excel, но и вдохновит на дальнейшие поиски решений по автоматизации работы.
Использование функций в Excel
1.Функции в Excel. Мастер функций 2
2.Математические функции. 4
2.1.Задание для самостоятельной работы 1. 4
2.2.Задание для самостоятельной работы 2. 5
3.Статистические функции. 6
3.1.Задание для самостоятельной работы 3. 6
4.Логические функции. 7
4.1. Описание некоторых логических функций. Примеры. 7
4.1.1.Сложные условия. 9
4.2. Задание для самостоятельной работы 4 14
5.1.Задание для самостоятельной работы 5. 15
5.2.Задание для самостоятельной работы 6. 15
6.Печать рабочего листа Excel. 16
7.Вопросы к защите лабораторной работы. 16
1.Функции в Excel. Мастер функций
При проведении расчетов в электронных таблицах часто необходимо использовать функции. В пакете Excel функции объединены в категории (группы) по назначению и характеру выполняемых операций:
математические;
финансовые;
статистические;
даты и времени;
логические;
работа с базой данных;
проверки свойств и значений; ... и другие.
Любая функция имеет вид:
ИМЯ (СПИСОК АРГУМЕНТОВ)
ИМЯ- это фиксированный набор символов, выбираемый из списка функций;
СПИСОК АРГУМЕНТОВ (или только один аргумент)- это величины, над которыми функция выполняет операции. Аргументами функции могут быть адреса ячеек, константы, формулы, а также другие функции. В случае, когда аргументом является другая функция, мы имеем дело со вложенной функцией.
Например, запись СУММ(С7:C10;D7:D10) содержит функцию СУММ с двумя аргументами, каждый из которых является диапазоном ячеек, а запись КОРЕНЬ(ABS(А2)) содержит функцию КОРЕНЬ, аргументом которой является функция ABC, у которой в свою очередь аргументом является адрес ячейки А2.
Пакет Excel предоставляет удобный инструмент ввода функций- Мастер функций. Инструмент Мастер функций можно вызвать:
командой Вставить функцию во вкладке Формулы из группы Библиотека функций (Рис.1)
Рис.1 Команда Вставить функцию во вкладке Формулы
командой Вставить функцию в строке формул (Рис.2).
Рис.2 Команда Вставить функцию в строке формул
После вызова Мастера функций появляется диалоговое окно (Рис.3):
Рис.3 Диалоговое окно Мастера функций
В этом окне нужно выбрать категорию функции и в списке ниже необходимую функцию.
Во втором появившемся окне ввести в соответствующие поля аргументы функции, при этом для каждого текущего аргумента выводится его описание и справа от поля аргумента отображается текущее значение этого аргумента. При вводе ссылок на ячейки достаточно выделить эти ячейки в электронной таблице (Рис.4).
Рис.4 Окно математической функции КОРЕНЬ
Когда в качестве аргумента функции используется также функция, то функцию аргумента (т.е. вложенную, или внутреннюю, функцию) следует выбирать, раскрывая список функций слева от строки формул (Рис.5).
Рис.5 Выбор вложенной (внутренней) функции
Если в появившемся списке отсутствует требуемая функция, то следует активизировать строку «Другие функции…» и работать далее с диалоговым окном Мастер функций , как описано выше.
После ввода аргументов вложенной функции не следует щелкать на кнопке ОК, а нужно активизировать (щелкнуть мышью) имя соответствующей внешней функции в поле ввода строки формул. Т.е. нужно перейти на окно Мастера функций соответствующей внешней функции. Так следует повторять для всех вложенных функций. В формулах может быть до 64 уровней вложения функций.
Функция – это специальная, заранее созданная, формула для сложных вычислений, в которую пользователю следует ввести лишь необходимые аргументы. Она имеет имя, описывающее ее предназначение (например, СУММ – это сложение нескольких чисел) и аргументы. Последних может быть несколько, но они всегда заключаются в круглые скобки. Далее мы рассмотрим основные функции Эксель и кратко расскажем об их предназначении.
Самые популярные функции
- СУММ – используется в Эксель для сложения значений в полях.
- ЕСЛИ – позволяет вернуть разные значения в зависимости от соблюдаемых условий.
- ПРОСМОТР – необходима для поиска значений, находящихся на идентичной позиции, но в другом столбце или строчке.
- ВПР – задействуется для поиска информации во всей таблице Эксель или её отдельных диапазонах. С её помощью легко отыскать номер телефона по фамилии либо, наоборот (по подобию телефонной книги).
- ПОИСКПОЗ – применяется для поиска заданного элемента и возвращения его относительной позиции в диапазон.
- ВЫБОР – предназначена для выбора значений из списка.
- ДАТА – может вернуть определенному периоду его порядковый номер.
- ДНИ – позволяет возвратить число дней между заданными датами.
- НАЙТИБ, НАЙТИ – обнаруживают вхождение текста в другую текстовую строку.
- ИНДЕКС – находит в диапазоне или таблице Эксель значение или ссылку на него и возвращает обратно.
Как вводить функции в таблицу
Добавление необходимого значения на рабочий лист можно выполнять непосредственно с клавиатуры либо через специальную команду программы Эксель, находящуюся в меню «Вставка». После выделения ячейки и выбора пункта «Вставка/Функция» на экране появится диалоговое окно Мастера.
Для начала вам следует выбрать категорию, а после функцию Эксель из алфавитного списка. Программа введет в ячейку знак равенства, название, круглые скобки и откроет следующее диалоговое окно. В него следует внести аргументы (координаты ячеек, в которых располагаются необходимые данные). После нажатия на кнопку «Ок» или Enter на клавиатуре, полученная функция появится в строчке с формулами.