9.6 Основные функции Excel
Функция – это специальная заранее подготовленная формула, которая выполняет операции над заданными значениями и возвращает результат. Значения, над которыми функция выполняет операции, называются аргументами. В качестве аргументов могут выступать числа, текст, логические значения, ссылки. Аргументы могут быть представлены константами или формулами. Формулы в свою очередь могут содержать другие функции, т.е. аргументы могут быть представлены функциями. Функция, которая используется в качестве аргумента, является вложенной функцией. Excel допускает до семи уровней вложения функций в одной формуле.
В общем виде любая функция может быть записана в виде:
=<имя_функции>(аргументы)
Существуют следующие правила ввода функций:
имя функции всегда вводится после знака «=»;
аргументы заключаются в круглые скобки, указывающие на начало и конец списка аргументов;
между именем функции и знаком «(« пробел не ставится;
вводить функции рекомендуется строчными буквами. Если ввод функции осуществлен правильно, Excel сам преобразует строчные буквы в прописные.
Для ввода функций можно использовать Мастер функций, вызываемый нажатием пиктограммы Вставить функцию группы Библиотека функций вкладки ФОРМУЛЫ. Мастер функций (рис. 9.7) позволяет выбрать нужную функцию из списка и выводит для нее панель формул. На панели формул отображаются имя и описание функции, количество и тип аргументов, поле ввода для формирования списка аргументов, возвращаемое значение.
Excel содержит широкий набор функций, позволяющих выполнять стандартные вычисления. К основным категориям функций относятся:
1). Математические позволяют производить простые и сложные математические вычисления, например вычисление суммы диапазона ячеек, вычисление суммы ячеек диапазона, удовлетворяющих указанному условию, округление чисел и прочее.
Рис.9.7. Диалоговое окно Мастера функций.
2). Инженерные, предназначенные для выполнения инженерного анализа (функции для работы с комплексными переменными; преобразования чисел из одной системы счисления в другую; преобразование величин из одной системы мер в другую).
3). Логические, предназначенные для проверки выполнения условия или нескольких условий (ЕСЛИ, И, ИЛИ, НЕ, ИСТИНА, ЛОЖЬ).
4).Статистические, предназначенные для выполнения статистического анализа данных.
5).Финансовые, предназначенные для осуществления типичных финансовых расчетов, таких как вычисление суммы платежа по ссуде, объема периодической выплаты по вложению или ссуде, стоимости вложения или ссуды по завершении всех платежей.
6).Текстовые функции, предназначенные для обработки текста (преобразование, сравнение, сцепление строк текста и т.д.).
7).Функции работы с датой и временем
8).Функции ссылки и массивы осуществляют поиск в списках или таблицах. Например, для поиска значения в таблице используйте функцию ВПР, а для поиска положения значения в списке — функцию ПОИСКПОЗ.
9). Функции проверки свойств и значений позволяют перехватывать ошибки и значения #Н/Д и предотвращать их распространение по рабочему листу.
10).Аналитические функции предназначены для работы с аналитическими кубами, которые позволяют создавать отчеты по базе данных оперативной аналитической обработки (OLAP).
11).Функции работа с базой данных предназначены для анализа данных из списков или баз данных.
12) Функции надстроек и автоматизации позволяют расширить возможности Microsoft Excel за счет добавления пользовательских команд и новых функций.
studfiles.net
Как работают основные функции Эксель
Функция – это специальная, заранее созданная, формула для сложных вычислений, в которую пользователю следует ввести лишь необходимые аргументы. Она имеет имя, описывающее ее предназначение (например, СУММ – это сложение нескольких чисел) и аргументы. Последних может быть несколько, но они всегда заключаются в круглые скобки. Далее мы рассмотрим основные функции Эксель и кратко расскажем об их предназначении.
Самые популярные функции
- СУММ – используется в Эксель для сложения значений в полях.
- ЕСЛИ – позволяет вернуть разные значения в зависимости от соблюдаемых условий.
- ПРОСМОТР – необходима для поиска значений, находящихся на идентичной позиции, но в другом столбце или строчке.
- ВПР – задействуется для поиска информации во всей таблице Эксель или её отдельных диапазонах. С её помощью легко отыскать номер телефона по фамилии либо, наоборот (по подобию телефонной книги).
- ПОИСКПОЗ – применяется для поиска заданного элемента и возвращения его относительной позиции в диапазон.
- ВЫБОР – предназначена для выбора значений из списка.
- ДАТА – может вернуть определенному периоду его порядковый номер.
- ДНИ – позволяет возвратить число дней между заданными датами.
- НАЙТИБ, НАЙТИ – обнаруживают вхождение текста в другую текстовую строку.
- ИНДЕКС – находит в диапазоне или таблице Эксель значение или ссылку на него и возвращает обратно.
Как вводить функции в таблицу
Добавление необходимого значения на рабочий лист можно выполнять непосредственно с клавиатуры либо через специальную команду программы Эксель, находящуюся в меню «Вставка». После выделения ячейки и выбора пункта «Вставка/Функция» на экране появится диалоговое окно Мастера.
Для начала вам следует выбрать категорию, а после функцию Эксель из алфавитного списка. Программа введет в ячейку знак равенства, название, круглые скобки и откроет следующее диалоговое окно. В него следует внести аргументы (координаты ячеек, в которых располагаются необходимые данные). После нажатия на кнопку «Ок» или Enter на клавиатуре, полученная функция появится в строчке с формулами.
free-office.net
Функции и формулы в Excel с примерами
Описание функций в Excel с картинками и готовыми примерами их применения на практике.Функции для эффективных расчетов
Пример функции РАЗНДАТ для расчета времени между датами в Excel.Как посчитать количество пройденных лет, месяцев и дней между двумя датами? Примеры формул с использование функции РАЗНДАТ для расчета возраста, сроков и стажа. Примеры функции РАНГ для ранжирования списков по условию в Excel.
Примеры работы функции ВСД при использовании в формулах для финансовых расчетов и сравнительных анализов кредитов или инвестиционных вкладов. Как определить реальную годовую процентную ставку? Пример функции ПЛТ для расчета аннуитетного платежа в Excel.
Примеры формул с использованием функции ПЛТ для финансовых расчетов погашения кредитов. Какой самый оптимальный размер ежемесячного аннуитетного платежа по кредиту с минимальной переплатой? Функция СУММПРОИЗВ для вычисления произведения суммы в Excel.
Функция СЧЁТЕСЛИМН предназначена для подсчета данных, соответствующих установленным критериям. Как посчитать количество значений по нескольким условиям? Функция ДВССЫЛ позволяет преобразовать текст в ссылку Excel.
Примеры работы функции ДВССЫЛ для извлечения данных по ссылке преобразованной из текста с помощью формулы. Как вставить часть текста в адрес ссылки на ячейку? Функция СУММЕСЛИМН и суммирование по нескольким условиям в Excel.
Примеры работы функции СУММЕСЛИМН при выборочном суммировании динамических диапазонов по условию указанному в критериях функции. Как суммировать ячейки по нескольким критериям? Пример функция ПСТР для разделения текста на части в Excel.
Примеры вычисления квартилей с использованием 3-х функций КВАРТИЛЬ, КВАРТИЛЬ.ВКЛ и КВАРТИЛЬ.ИСКЛ. Данные функции активно используются в статистических анализах данных. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
exceltable.com
12 функций Excel, о которых должен знать каждый SEO-специалист
В этой статье Владимир Шванский рассказывает о том, как эффективно использовать 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;адрес_замыкающей ячейки;адрес_ячейки_анкора;"")
В случае, если вы указывали конкретные ячейки, а не столбцы, не забудьте задать абсолютные адреса:
$A$1
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-ов, и нам необходимо выделить из них непосредственно домен.
Будем следовать такой логике: нам надо «найти» точку непосредственно на слеше после домена, после этого вырвать кусок строки слева — с нулевой точки до найденной нами точки конца домена. Разобьем задачу на подзадачи.
1.
Что ищем? Слеш. Где ищем? В ячейке с URL. С какой позиции ищем? Как минимум, с восьмой, чтобы исключить начальные слеши.
Итог:
=ПОИСК("/";ячейка_с_URL;8)
2.
Выделим подстроку с доменом: с начала строки до точки вхождения слеша.
=ЛЕВСИМВ(ячейка_URL;ПОИСК("/";ячейка_URL;8))
При определенной сноровке с текстовыми функциями Excel можно творить настоящие чудеса.
Функция № 10: ВПР (искомое_значение, таблица, номер_столбца, тип_совпадения) (англ. VLOOKUP)
Кратко суть функции описать сложно, а в официальной справке приведено абсолютно непонятное объяснение. По сути, это «состыковка» значений разных таблиц на основании анализа данных в ячейках. Рассмотрим, как это работает на очередном вымышленном примере. Пусть у нас будет список ссылающихся на наш сайт доменов, анкоров их ссылок, ТИЦ и PR этих сайтов.
Как мы видим, порядок сайтов в этих двух таблицах разнится. Без использования функций перенести данные из второй таблицы в первую, кроме как «руками», невозможно. Попробуем использовать функцию ВПР.
=ВПР(A2;F2:h21;2;ЛОЖЬ)
Первый параметр, А2, определяет, по какому значению мы ищем совпадения. В нашем случае нам надо «состыковать» таблицу по отдельным доменам.
- Второй параметр, F2:h21 — это таблица с «эталонами». То есть та, где мы ищем.
- Третий параметр, 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, но и вдохновит на дальнейшие поиски решений по автоматизации работы.
Источник
Поделитесь материалом с друзьями
[share-buttons]
Читайте также
Навигация по записям
← 7 незаменимых инструментов для работы с текстом Почему контент-маркетинг не работает и как это исправить →blog.contentmonster.ru
Функции Excel – простые примеры для начинающего | Info-Comp.ru
Microsoft Excel самая популярная офисная программа для работы с данными в табличным виде, и поэтому практически каждый пользователь, даже начинающий, просто обязан уметь работать в данной программе. Работа в Excel подразумевает не только просмотр данных, но и оперирование этими данными, а для этого на помощь Вам приходят функции, о которых мы сегодня и поговорим.
Сразу хотелось бы отметить, что все примеры будем рассматривать в Microsoft office 2010.
Сегодня мы рассмотрим несколько одних из самых распространенных функций Excel, которыми очень часто приходится пользоваться. Они на самом деле очень простые, но почему-то некоторые даже и не подозревают об их существовании.
Примечание! Сегодняшний материал посвящен встроенным функциям, которые присутствуют в Excel по умолчанию, рассматривать макросы или программки на VBA сегодня мы не будем, однажды на этом сайте мы уже затрагивали тему VBA Excel в статье – Запрет доступа к листу Excel с помощью пароля, если интересно можете посмотреть.
Приступим.
Функция Excel – Сцепить
Данная функция соединяет несколько столбцов в один, например, у Вас фамилия имя отчество расположены в отдельном столбце, а Вам хотелось бы соединить их в один. Также Вы можете использовать эту функцию и для других целей, но надеюсь, смысл ее понятен, пример ниже. Для того чтобы вызвать эту функцию необходимо написать в отдельной ячейке =сцепить(столбец1; столбец2 и т.д.), или на панели нажать кнопку «вставить функцию» и набрать сцепить в поиске, и уже потом в графическом интерфейсе выбрать поля.
Функция Excel – ВПР
Эта функция расшифровывается как «Вертикальный просмотр» и полезна она тем, что с помощью нее можно искать данные в других листах или документах Excel по определенному ключевому полю. Например, у Вас есть две таблицы, содержащие одно одинаковое поле, но остальные колонки другие и Вам хотелось бы скопировать данные из одной таблицу в другую по этому ключевому полю:
Таблица 1
Таблица 2
Вы действуете также как и в предыдущем примере, или пишите или выбираете через графический интерфейс, например:
С описанием полей проблем не должно возникнуть, там все написано. Далее жмете «ОК» и получаете результат:
Функции Excel – Правсимв и Левсимв
Данные функции просто вырезают указанное количество знаков справа или слева (я думаю из названия понятно). Например, требуется тогда когда нужно, например, получить из адреса индекс в отдельное поле, а индекс подразумевается идти в начале строки или любой другой номер или лицевой счет у кого какие нужды, для примера:
Функция Excel – Если
Это обычная функция на проверку выражения или значения. Иногда бывает полезна. Например, нам необходимо в столбец C записывать значение «Больше» или «Меньше» на основании сравнения полей A и B т.е. например, если A больше B то записываем «Больше» если меньше то соответственно записываем «Меньше»:
На сегодня я думаю достаточно, да и принцип я думаю, понятен, т.е. в окне выбора функций все функции сгруппированы по назначению (категории) и с подробным описанием, как вызывается окно функций, Вы уже знаете, но все равно напомню, на панели жмем «Вставить функцию» и ищем нужную Вам функции и все.
Надеюсь, все выше перечисленные примеру окажутся Вам полезны.
Похожие статьи:
info-comp.ru
Работа с функциями в Excel на примерах
Функции в Excel – это вычислительные инструменты пригодны для самых разных отраслей деятельности: финансы, статистика, банковское дело, бухгалтерский учет, инженерство и проектирование, анализ исследовательских данных и т.д.
Лишь мизерная часть возможностей вычислительных функций включено в данный учебник с уроками по Excel. На простых примерах и далее будем рассматривать практическое применение функций.
Функция вычисления среднего числа
Как найти среднее арифметическое число в Excel? Создайте таблицу, так как показано на рисунке:
В ячейках D5 и E5 введем функции, которые помогут получить среднее значение оценок успеваемости по урокам Английского и Математики. Ячейка E4 не имеет значения, поэтому результат будет вычислен из 2 оценок.
- Перейдите в ячейку D5.
- Выберите инструмент из выпадающего списка: «Главная»-«Сумма»-«Среднее». В данном выпадающем списке находятся часто используемые математические функции.
- Диапазон определяется автоматически, остается только нажать Enter.
- Функцию, которую теперь содержит ячейка D5, скопируйте в ячейку E5.
Функция среднее значение в Excel: =СРЗНАЧ() в ячейке E5 игнорирует текст. Так же она проигнорирует пустую ячейку. Но если в ячейке будет значение 0, то результат естественно измениться.
В Excel еще существует функция =СРЗНАЧА() – среднее значение арифметическое число. Она отличается от предыдущей тем, что:
- =СРЗНАЧ() – пропускает ячейки которые не содержат чисел;
- =СРЗНАЧА() – пропускает только пустые ячейки, а текстовые значения воспринимает как 0.
Функция подсчета количества значений в Excel
- В ячейки D6 и E6 нашей таблицы введите функцию подсчета количества числовых значений. Функция позволит нам узнать количество поставленных оценок.
- Перейдите в ячейку D6 и выберите инструмент из выпадающего списка: «Главная»-«Сумма»-«Число».
- На этот рас нам не подходит автоматическое определение диапазона ячеек, поэтому его нужно исправить на D2:D4. После чего жмем Enter.
- Из D6 в ячейку E6 скопируйте функцию =СЧЕТ() – это функция Excel для подсчета количества не пустых ячеек.
На данном примере хорошо видно, что функция =СЧЕТ() игнорирует ячейки, которые не содержат числа или пусты.
Функция ЕСЛИ в Excel
В ячейки D7 и E7 введем логическую функцию, которая позволит нам проверить, все ли студенты имеют оценки. Пример использования функции ЕСЛИ:
- Перейдите в ячейку D7 и выберите инструмент: «Формулы»-«Логические»-«ЕСЛИ».
- Заполняем аргументы функции в диалоговом окне как показано на рисунке и жмем ОК (обратите внимание вторая ссылка $A$4 – абсолютная):
- Функцию из D7 копируем в E7.
Описание аргументов функции: =ЕСЛИ(). В ячейке A4 у нас количество всех студентов, а в ячейке D6 и E6 – количество оценок. Функция ЕСЛИ() проверяет, совпадают ли показатели D6 и E6 со значением A4. Если совпадают, то получаем ответ ДА, а если нет – ответ НЕТ.
В логическом выражении для удобства применяется 2 типа ссылок: относительная и абсолютная. Это позволяет нам скопировать формулу без ошибок в результатах ее вычисления.
Примечание. Закладка «Формулы» предоставляет доступ только к наиболее часто используемым функциям. Больше можно получить, вызвав окно «Мастер функций» нажав на кнопку «Вставить функцию» вначале строки формул. Или нажать комбинацию клавиш SHIFT+F3.
Как округлять числа в Excel
Функция =ОКРУГЛ() более точна и полезнее чем округление с помощью формата ячеек. В этом легко убедиться на практике.- Создайте исходную таблицу так как показано на рисунке:
- Ячейку B2 отформатируйте так, чтобы были отображены только 2 знака после запятой. Это можно сделать с помощью диалогового окна «Формат ячеек» или инструментом расположенном на закладке «Главная»-«Уменьшить разрядность»
- В столбце C проставьте формулу вычитания 1,25 из значений столбца B: =B-1,25.
Теперь вы знаете, как пользоваться функцией ОКРУГЛ в Excel.
Описание аргументов функции =ОКРУГЛ():
- Первый аргумент – это ссылка на ячейку значение, которой нужно округлить.
- Второй аргумент – это количество знаков после запятой, которое нужно оставить после округления.
Внимание! Форматирование ячеек только отображает округление но не изменяет значение, а =ОКРУГЛ() – округляет значение. Поэтому для вычислений и расчетов нужно использовать функцию =ОКРУГЛ(), так как форматирование ячеек приведет к ошибочным значениям в результатах.
exceltable.com
5 основных функций Excel, облегчающих работу с ним
Excel для некоторых – любимый инструмент манипулирования неграмотными мужчинами (да и женщинами). К примеру, программистам не нужно беспокоиться о множестве столбцов, строк, и правильности формул, потому что у них есть и другие возможности. Для всех же остальных, в особенности находящихся на руководящих позициях, Excel является важным инструментом работы. Несмотря на это, многие часто пугаются, когда приходится впервые начать с ним работать.
В самом деле, если вы изучите Excel достаточно хорошо, то сможете использовать его для экономии времени в записях, автоматически обновлять их, использовать для хранения данных и даже проводить опросы. Позвольте представить пять, можно сказать, основных функций Excel, о которых должен знать каждый начинающий пользователь.
Автозавершение
Ввод последовательных данных, особенно с длинным рядом, занимает много времени и имеет отличные шансы человеческой ошибки. Вот почему, когда это возможно, Excel предсказывает текст, который вы пытаетесь ввести и дает вам возможность дополнить его для вас.
К примеру, вы хотите ввести серийные номера, которые идут друг за другом. Вместо того, чтобы вводить их вручную, можно ввести первые три или четыре цифры и выделить заполненные ячейки. Подведите курсор к нижней правой части последней ячейки подождите пока он превратится в знак плюса.
Перетащите его и сможете увидеть значение каждой из новых ячеек. Excel автоматически увеличит их значение. Этот трюк лучше всего использовать с формулами, потому что их значения автоматически обновляются и не приходится их копировать/вставлять.
Автозаполнение
Этот трюк пригодится при вводе данных. Excel может автоматически заполнять ячейки, если были введены похожие данные в предыдущих клетках того же столбца. Просто нажмите Enter, чтобы заполнить его.
Умная сортировка по алфавиту
В Excel есть такая возможность, но новичок может постесняться использовать её, так как данные в соседних колонках, связанные с ними, могут поменять соотношения. Например, у вас есть имена, отделы, адреса, номера телефонов и группы крови в отдельных самостоятельных столбцах.
Вам нужно отсортировать имена в алфавитном порядке, но при этом хотелось бы оставить соседние клетки в таком виде, чтобы прочитать правильный адрес, номер телефона, и группу крови для этого человека.
Вы можете пойти дальше и использовать сортировку от А до Я, потому что Excel спросит хотите ли вы расширить выбор для включения данных в смежных колонках. Это позволяет сортировать одну колонку в алфавитном порядке, а соответствующие данные в других колонках разместить в необходимом порядке. Такая умная сортировка работает для любого типа правил, которые вы сможете использовать.
Преобразование данных из строки в столбец (и обратно)
Зачастую обнаруживается неправильное расположение данных уже после того, как проведена некоторая работа. К примеру, формат был создан для какой-то конкретной цели, но обнаружилось, что нужно перевести столбец в строку или наоборот. Вместо копирования и вставки каждого отдельного значения, вы можете использовать специальную вставку «Транспонирование».
Выделите и скопируйте столбец, который необходимо вставить в строку. Затем выберите первую ячейку строки, в которую требуется вставить данные. Нажмите на маленькую стрелку под кнопкой Вставить и выберите команду Специальная вставка. В появившемся окошке отметьте галочкой «транспонировать» и нажмите ОК.
Отдельные имена и фамилии
В Excel 2013 есть умная функция автоматического заполнения, которая работает между колонками. Возьмите, к примеру, столбец, который содержит как имя, так и фамилию. В прежних, более ранних, версиях Excel приходилось использовать сложную функцию связывания.
Теперь же все, что вам нужно сделать – это начать печатать имя в следующей колонке. Сделайте это для первых двух или трех элементов и получите критерии разделения и список данных для всех соответствующих значений в столбце. Все, что вам нужно будет сделать, это нажать кнопку Enter на клавиатуре.Это работает для любого типа данных.
Знаете ли вы полезные хитрости Excel, которые часто используете на работе, и помогающие устранить лишние телодвижения? Поделитесь с нами в комментариях.
[sotmarket_popular_3columns_3_both_default___]
← Как убрать пароль блокировки на устройcтвах Android Как скачать торрент на Android смартфон или планшет →raznosolie.ru