В excel формула содержит

В excel формула содержит

Функция ЕСЛИ СОДЕРЖИТ

Наверное, многие задавались вопросом, как найти функцию в EXCEL«СОДЕРЖИТ» , чтобы применить какое-либо условие, в зависимости от того, есть ли в текстовой строке кусок слова , или отрицание, или часть наименования контрагента, особенно при нестандартном заполнении реестров вручную.

Такой функционал возможно получить с помощью сочетания двух обычных стандартных функций – ЕСЛИ и СЧЁТЕСЛИ .

Рассмотрим пример автоматизации учета операционных показателей на основании реестров учета продаж и возвратов (выгрузки из сторонних программ автоматизации и т.п.)

У нас есть множество строк с документами Реализации и Возвратов .

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

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

Выражение должно быть универсальным , для того, чтобы обрабатывать новые добавляемые данные .

Для того, чтобы это сделать, необходимо:

    Начинаем с ввода функции ЕСЛИ (вводим «=» , набираем наименование ЕСЛИ , выбираем его из выпадающего списка, нажимаем fx в строке формул).

В открывшемся окне аргументов, в поле Лог_выражение вводим СЧЁТЕСЛИ() , выделяем его и нажимаем 2 раза fx.



Далее в открывшемся окне аргументов функции СЧЁТЕСЛИ в поле «Критерий» вводим кусок искомого наименования *реализ* , добавляя в начале и в конце символ * .

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

  • Аргумент «Диапазон» – это соответствующая ячейка с наименованием документа.
  • Далее нажимаем ОК , выделяем в строке формул ЕСЛИ и нажимаем fx и продолжаем заполнение функции ЕСЛИ.

  • В Значение_если_истина вводим « Реализация », а в Значение_если_ложь – можно ввести прочерк « – »
  • Далее протягиваем формулу до конца таблицы и подключаем сводную.


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

    Если материал Вам понравился или даже пригодился, Вы можете поблагодарить автора, переведя определенную сумму по кнопке ниже:
    (для перевода по карте нажмите на VISA и далее “перевести”)

    Источник: excelspb.ru

    Что такое формула?

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

    Функция – встроенный вычислительный инструмент Excel, способный вернуть значение, в зависимости от переданного ему параметра и предназначенный для расчетов, вычислений и анализа данных. Каждая функция может включать в себя константу, оператор, ссылку, имя ячейки(диапазона) и формулу.
    Формула – специальный инструмент Excel, предназначенный для расчетов, вычислений и анализа данных. Формула может включать в себя константу, оператор, ссылку, имя ячейки (диапазона) и функцию. Главное отличие формулы от функции это то, что формула не обязательно должна включать в себя одну из встроенных функций и может быть самостоятельным вычисляемым выражением( =12+34 ). В повседневном использовании чаще всего используют слово формула, а не функция. Не думаю, что это принципиально и надо вдумываться. Это уже устоявшееся выражение и никакой ошибки и недопонимания со стороны окружающих явно не будет, если функцию назвать формулой
    Константа – это фиксированное значение, представляющее собой число либо текст и не изменяющееся в процессе расчетов.
    Операторы бывают трех видов:

    • Арифметический оператор – предназначен для выполнения арифметических действий и возвращающий числовое значение;
    • Оператор сравнения – предназначен для сравнения данных и возвращающий логическое значение ИСТИНА или ЛОЖЬ(TRUE или FALSE);
    • Текстовый оператор – применяется для объединения данных(в Excel им является амперсанд – &).

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

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

    Выбрать категорию и нужную функцию. При просмотре функций внизу окна мастера функций появляется описание выделенной функции. Через данный мастер доступен просмотр всех имеющихся в вашей версии Excel функций. Так же перечень функций с описанием можно посмотреть на данном сайте: Функции Excel.

    Вкладка Формулы (Formulas)

    На вкладке Формулы (Formulas) все функции так же разбиты на категории. После нажатия на кнопку категории появляется выпадающий список, из которого можно выбрать нужную функцию. Если задержать курсор на названии функции более 2-х секунд появится всплывающая подсказка, кратко описывающая функцию.

    Непосредственный ввод в ячейку
    Если в любую ячейку ввести знак равно ( = ) и начать набирать название функции – появится выпадающий список со всеми функциями, начинающимися на введенные буквы.

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

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

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

    Excel так же будет распознавать как формулу данные в ячейке, если они будут начинаться с – или +. Если далее будет идти текст – Excel вернет в ячейку #ИМЯ?. Если числа – Excel постарается совершить математические действия над числами( сложить, вычесть, умножить, разделить и т.д. – в зависимости от того, будут ли соответствующие символы +-*/ ). Но это больше недокументированная особенность, чем правило. Просто в данном случае Excel сам подставит перед математическим знаком оператор равенства (=), посчитав, что планируется что-то вычислить.
    Так же непосредственно функцию можно записать, начиная не со знака равно, а с “собаки” – @ТДАТА(). Excel сам заменить @ на =. Это применимо исключительно к встроенным функциям и объясняется обратной совместимостью(такой ввод функций применялся еще в Lotus), чтобы документы, созданные в старых версиях Excel могли работать в более поздних без потери функциональности.

    Аргументы функции
    Практически все функции требуют указания аргументов.
    Аргумент – ссылка на ячейку, текст или число, которые необходимы для выполнения функцией расчетов. Например, функция ЕНЕЧЁТ (ISODD) требует указания в качестве аргумента числа, которое необходимо проверить. Результатом функции будет логическое значение, указывающее чётное число или нет. Аргументом в данном случае можно указать как непосредственно число:
    =ЕНЕЧЁТ(5) – вернет ИСТИНА
    =ISODD (5) – вернет TRUE
    Так и ссылку на ячейку, содержащую число:
    =ЕНЕЧЁТ( C4 ) – в C4 должно быть число

    Читайте также:  Excel количество дней в месяце формула

    Или возьмем функцию СУММ (SUM) – аргументами функции являются числа, которые необходимо суммировать. Без них функция работать не будет, т.к. суммировать нечего.

    Если функция требует в качестве аргумента число или текст – то это всегда может быть так же и ссылкой на ячейку. Если в качестве аргумента требуется диапазон – то всегда необходимо указывать именно ссылку на ячейку/диапазон ячеек
    Разделителем аргументов в русской локализации является точка-с-запятой (;). В английской локализации это запятая (,)
    Однако не все функции требуют обязательного ввода параметров. Приведенные ниже функции не имеют ни одного параметра:

    • ТДАТА() – возвращает текущее время и дату в формате даты и времени – 01.01.2001 10:00
    • СЕГОДНЯ() – возвращает текущую дату в формате даты – 01.01.2001
    • ИСТИНА() – возвращает логическое значение ИСТИНА
    • ЛОЖЬ() – возвращает логическое значение ЛОЖЬ
    • НД() – возвращает неопределенное значение #Н/Д
    • ПИ() – возвращает округленное до 15 знаков число Пи – 3,14159265358979
    • СЛЧИС() – возвращает равномерно распределенное случайное число большее или равное нулю и меньше единицы – 0,376514074162531

    Особенности формул/функций

    • Формулы обновляют свой результат(вычисляются) как только ячейка, задействованная в формуле(влияющая ячейка) поменяет значение. Например, если в ячейке А1 записать такую формулу: = D1 , то при изменении в ячейке D1 значения оно так же поменяется и в А1. Ссылка на ячейки может быть не только в таком виде, но и в составе более сложных формул и функций и правило пересчета будет действовать для них так же
    • Функции не могут изменять значения и форматы других ячеек, они могут вернуть результат только в ту ячейку, в которую записаны

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

    Статья помогла? Поделись ссылкой с друзьями!

    Источник: www.excel-vba.ru

    ЕСЛИ: функция Эксель для дополнительной проверки содержимого

    Здравствуйте. Сегодня учимся проверять тип содержимого ячейки и комбинировать полученную информацию с функцией ЕСЛИ . Будем определять, содержится ли в ячейке текст, число или формула и т.п.

    Если вы еще не знаете, как работает функция ЕСЛИ – прочтите в этой статье и возвращайтесь сюда!

    Проверка в Excel, если содержит текст

    Что делать, если нужно проверить, содержит ли ячейка строку текста. Используем функцию: =ЕТЕКСТ(ссылка) . Функция вернет ИСТИНА, когда ссылка указывает на ячейку с текстом, ЛОЖЬ – в противном случае. Вот несколько примеров работы:

    Получили такие результаты:

    Тип проверяемого значения Результат
    Число ЛОЖЬ
    Дата ЛОЖЬ
    Текст ИСТИНА
    Число в текстовом формате ИСТИНА
    Пустая ячейка ЛОЖЬ
    ИСТИНА
    Формула Зависит от типа вычисленного значения
    Ссылка Зависит от типа значения в ячейке, на которую ссылаемся

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

    Обратный результат даст функция =ЕНЕТЕКСТ(ссылка) , которая проверяет, чтобы в ячейке было нетекстовое значение:

    Таблица с итогами:

    Тип проверяемого значения Результат
    Число ИСТИНА
    Дата ИСТИНА
    Текст ЛОЖЬ
    Число в текстовом формате ЛОЖЬ
    Пустая ячейка ИСТИНА
    Непечатаемый символ ЛОЖЬ
    Формула Зависит от типа вычисленного значения
    Ссылка Зависит от типа значения в ячейке, на которую ссылаемся

    Как будет выглядеть формула в Excel: если ячейка содержит текст, то значение, в противном случае – пустая строка.

    Вот так: =ЕСЛИ(ЕТЕКСТ(A1);»Это текст»;»») . Т.е. аргументы:

    • Условие: ЕТЕКСТ(А1)
    • Значение, если это текст: строка «Это текст»
    • Значение, если это не текст: пустая строка (обозначается «»)

    Функция Excel ЕЧИСЛО

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

    То есть, Excel считает числом цифру, дату, формулу, вернувшую число и ссылку на число.

    В Экселе нет функции, проверяющей, что это не число. Чтобы сделать такую операцию, применяем логическую функцию НЕ . Вот так: =НЕ(ЕЧИСЛО(А1)) .

    Проверка чётности и нечётности значения

    Чтобы определить, чётно число или нет, используем функции:

    • ЕЧЁТН – проверить чётность числа
    • ЕНЕЧЁТ – определить нечётность

    Результаты работы функций:

    Проверяемое значение Функция Результат
    1 ЕЧЁТН(ссылка) ЛОЖЬ
    2 ЕЧЁТН(ссылка) ИСТИНА
    1 ЕНЕЧЁТ(ссылка) ИСТИНА
    2 ЕНЕЧЁТ(ссылка) ЛОЖЬ

    Если вы попытаетесь проверить нечисловые значения, функции вернут ошибку #ЗНАЧ!

    Для условия, если ячейка четная, функция ЕСЛИ может быть построена так: =ЕСЛИ(ЕЧЁТН(A2);»Это чётное значение»;»») . Получим такую таблицу результатов:

    Обратите внимание на особенности:

    1. Функция восприняла дату, как обычное число и выдала правильный результат
    2. Проверка текста и непечатаемого символа дали ошибку #ЗНАЧ!
    3. Число в текстовом формате (ячейка А6) было приведено к числовому и проверено, получен правильный результат
    4. Результат, когда в ячейке формула или ссылка – зависит от результата вычисления или значения в ячейке по ссылке

    Проверка в Excel, если ячейка пустая

    Для проверки пустоты, есть функция =ЕПУСТО() . Она дает такие результаты:

    Видим, что получаем ИСТИНУ лишь тогда, когда в ячейке пустота.

    Теперь напишем формулу в Эксель, если пустая ячейка, то выведем какое-то значение: =ЕСЛИ(ЕПУСТО(A2);»Пустая ячейка»;»») .

    Похожая проверка в Excel, если не пусто, то формула будет такая: =ЕСЛИ(НЕ(ЕПУСТО(A2));»Ячейка заполнена»;»») .

    Проверка на логическое значение

    Функция ЕЛОГИЧ проверит, находится ли в ячейке ИСТИНА или ЛОЖЬ. Если это так, вернет ИСТИНУ. В противном случае – ЛОЖЬ

    Это все вспомогательные функции, о которых я хотел рассказать. Применяйте их в комбинации с функцией ЕСЛИ и логическими функциями, чтобы получить максимальный эффект возможностей программы. Сейчас готов ответить на ваши вопросы, пишите комментарии!

    Источник: officelegko.com

    Функция ЕСЛИ() в EXCEL

    Функция ЕСЛИ() относится к наиболее часто используемым функциям.

    Синтаксис функции

    ЕСЛИ(лог_выражение;значение_если_истина;[значение_если_ложь])

    Лог_выражение — любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ. =ЕСЛИ(A1>=100;”Бюджет превышен”;”ОК!”) Т.е. если в ячейке A1 содержится значение большее или равное 100, то формула вернет ОК!, а если нет, то Бюджет превышен .

    В качестве аргументов функции, могут участвовать формулы, например: =ЕСЛИ(A1>100;СУММ(B1:B10);СУММ(C1:C10)) Т.е. если в ячейке A1 содержится значение >100, то суммирование ведется по столбцу B , а если меньше, то по столбцу С .

    Вложенные ЕСЛИ

    В EXCEL 2007 в качестве значений аргументов значение_если_истина и значение_если_ложь можно для построения более сложных проверок использовать до 64 вложенных друг в друга функций ЕСЛИ() . =ЕСЛИ(A1>=100;”Бюджет превышен”;ЕСЛИ(A1>=90;”Крупный проект”;ЕСЛИ(A1>=50;”Средний проект”;”Малый проект “)))

    Вышеуказанную формулу можно усложнять и дальше, но есть другие подходы:

    Для функции ВПР() необходимо создать в диапазоне A3:B6 таблицу значений:

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

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

    =ПРОСМОТР(A24;<-1E+307;0;1E-307>;A27:A29) (см. файл примера )

    Опущен третий аргумент [значение_если_ложь]

    Третий аргумент функции не обязателен, если его опустить, то функция вернет значение ЛОЖЬ (если условие не выполнено). =ЕСЛИ(A1>100;”Бюджет превышен”) Если в ячейке A1 содержится значение 1, то вышеуказанная формула вернет значение ЛОЖЬ.

    Вместо ИСТИНА или ЛОЖЬ в первом аргументе введено число

    Т.к. значение ЛОЖЬ эквивалентно 0, то формулы =ЕСЛИ(0;”Бюджет превышен”;”ОК!”) или (если в ячейке A1 содержится значение 0) =ЕСЛИ(A1;”Бюджет превышен”;”ОК!”) вернут ОК!

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

    Связь функции ЕСЛИ() с другими функциями использующие условия

    EXCEL содержит также другие функции, которые можно применять для анализа данных с использованием условий. Например, для подсчета количества вхождений чисел в диапазоне ячеек используется функция СЧЁТЕСЛИ() , а для сложения значений, удовлетворяющих определенным условиям, используется функция СУММЕСЛИ() .

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

    Пусть данные находятся в диапазоне A6:A11 (см. файл примера)

    Подсчитаем сумму значений, которые больше 10 с помощью функции СУММЕСЛИ() , записав =СУММЕСЛИ(A6:A11;”>10″) . Аналогичный результат (23) можно получить с помощью формулы массива =СУММ(ЕСЛИ(A6:A11>10;A6:A11)) (для ввода формулы в ячейку вместо ENTER нужно нажать CTRL+SHIFT+ENTER )

    Теперь подсчитаем количество вхождений чисел больше 10 в диапазоне ячеек A6:A11 =СЧЁТЕСЛИ(A6:A11;”>10″) . Аналогичный результат (2) можно получить с помощью формулы массива =СЧЁТ(ЕСЛИ(A6:A11>10;A6:A11))

    Теперь, когда принцип понятен, с помощью функции ЕСЛИ() можно конструировать и другие формулы с условиями. Например, нахождение минимального значения среди чисел больше 10: =МИН(ЕСЛИ(A6:A11>10;A6:A11))

    Источник: excel2.ru

    Текстовые функции Excel

    ФИО, номера банковских карт, адреса клиентов или сотрудников, комментарии и многое другое –все это является строками, с которыми многие сталкиваются, работая с приложением Excel. Поэтому полезно уметь обрабатывать информацию подобного типа. В данной статье будут рассмотрены текстовые функции в Excel, но не все, а те, которые, по мнению office-menu.ru, самые полезные и интересные:

    Список всех текстовых функций Вы можете найти на вкладке «Формулы» => выпадающий список «Текстовые»:

    Функция ЛЕВСИМВ

    Возвращает подстроку из текста в порядке слева направо в заданном количестве символов.

    Синтаксис: =ЛЕВСИМВ(текст; [количество_знаков])

    • текст – строка либо ссылка на ячейку, содержащую текст, из которого необходимо вернуть подстроку;
    • количество_знаков – необязательный аргумент. Целое число, указывающее, какое количество символов необходимо вернуть из текста. По умолчанию принимает значение 1.

    Пример использования:

    Формула: =ЛЕВСИМВ(“Произвольный текст”;8) – возвращенное значение «Произвол».

    Функция ПРАВСИМВ

    Данная функция аналогична функции «ЛЕВСИМВ», за исключением того, что знаки возвращаются с конца строки.

    Пример использования:

    Формула: =ПРАВСИМВ(“произвольный текст”;5) – возвращенное значение «текст».

    Функция ДЛСТР

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

    Синтаксис: =ДЛСТР(текст)

    Пример использования:

    Функция НАЙТИ

    Возвращает число, являющееся вхождением первого символа подстроки, искомого текста. Если текст не найден, то возвращается ошибка «#ЗНАЧ!».

    Синтаксис: =НАЙТИ(искомый_текст; текст_для_поиска; [нач_позиция])

    • искомый_текст – строка, которую необходимо найти;
    • текст_для_поиска – текст, в котором осуществляется поиск первого аргумента;
    • нач_позиция – необязательный элемент. Принимает целое число, которое указывает, с какого символа текст_для_поиска необходимо начинать просмотр. По умолчанию принимает значение 1.

    Пример использования:

    Из отрывка стихотворения великого поэта С.А.Есенина находим вхождение первого символа строки «птица». Поиск осуществляется с начала строки. Если в приведенном примере поиск осуществлялся бы с 40 символа, то функция в результате вернула ошибку, т.к. позиции вхождения не было найдено.

    Функция ЗАМЕНИТЬ

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

    Синтаксис: ЗАМЕНИТЬ(старый_текст; начальная_позиция; количество_знаков; новый_текст)

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

    Пример использования:

    Здесь в строке, содержащейся в ячейке A1, подменяется слово «старый», которое начинается с 19-го символа и имеет длину 6 символов, на слово «новый».

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

    • Аргумент «начальная_позиция» подменим функцией «НАЙТИ»;
    • В место аргумент «количество_знаков» вложим функцию «ДЛСТР».

    В результате получим формулу: =ЗАМЕНИТЬ(A1;НАЙТИ(“старый”;A1);ДЛСТР(“старый”);”новый”)

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

    Функция ПОДСТАВИТЬ

    Данная функция заменяет в тексте вхождения указанной подстроки на новый текст, чем схожа с функцией «ЗАМЕНИТЬ», но между ними имеется принципиальное отличие. Если функция «ЗАМЕНИТЬ» меняет текст, указанный посимвольно вручную, то функция «ПОДСТАВИТЬ» автоматически находит вхождения указанной строки и меняет их.

    Синтаксис: ПОДСТАВИТЬ(текст; старый_текст; новый_текст; [номер_вхождения])

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

    Пример использования:

    Строка в ячейке A1 содержит текст, в котором имеются 2 подстроки «старый». Нам необходимо подставить на место первого вхождения строку «новый». В результате часть текста «…старый-старый…», заменяется на «…новый-старый…».

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

    Функция ПСТР

    ПСТР возвращает из указанной строки часть текста в заданном количестве символов, начиная с указанного символа.

    Синтаксис: ПСТР(текст; начальная_позиция; количество_знаков)

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

    Пример использования:

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

    Аргумент количество_знаков может превышать допустимо возможную длину возвращаемых символов. Т.е. если в рассмотренном примере вместо количество_знаков = 12, было бы указано значение 15, то результат не изменился, и функция так же вернула строку «функции ПСТР».

    Для удобства использования данной функции ее аргументы можно подменить функциями «НАЙТИ» и «ДЛСТР», как это было сделано в примере с функцией «ЗАМЕНИТЬ».

    Функция СЖПРОБЕЛЫ

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

    Синтаксис: =СЖПРОБЕЛЫ(текст)

    Пример использования:

    =СЖПРОБЕЛЫ( ” Текст с лишними пробелами между словами и по краям “ )

    Результатом выполнения функции будет строка: “Текст с лишними пробелами между словами и по краям” .

    Функция СЦЕПИТЬ

    С помощью функции «СЦЕПИТЬ» можно объединить несколько строк между собой. Максимальное количество строк для объединения – 255.

    Синтаксис: =СЦЕПИТЬ(текст1; [текст2]; …)

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

    Пример использования:

    Функция возвратит строку: «Слово1 Слово2».

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

    Вместо использования данной функции можно применять знак амперсанда «&». Он так же объединяет строки. Например: «=”Слово1″&” “&“Слово2″».

    Источник: office-menu.ru

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

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

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

    Соединить текст из разных ячеек

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

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

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

    Подобрать значения для нужного результата

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

    Для этого на вкладке «Данные» надо выбрать «Анализ „Что если“», с помощью функции «Подбор параметра» задать целевое значение и выбрать ячейку, которую нужно изменить для получения желаемой цифры.

    Как следить за бюджетом

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

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

    Обновить курс валют

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

    Чтобы использовать эту функцию, на вкладке «Данные» выберите кнопку «Из интернета» и вставьте адрес надежного источника, например cbr.ru. Эксель предложит выбрать, какую именно таблицу нужно загрузить с сайта — отметьте нужную галочкой.

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

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

    Планировать действия

    Теперь, когда курсы валют и стоимость акций подгружаются автоматически, эксель становится динамичным рабочим инструментом. Но можно пойти дальше и требовать от него реакций — и даже советов! — при определенных изменениях. Для этого понадобится функция «Если». Она заполняет ячейки заданными значениями в зависимости от того, что происходит в остальной таблице.

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

    Вот так: =ЕСЛИ (ячейка с ценой акции >= цена выгодной продажи; “продавать”; ЕСЛИ (ячейка с ценой акции Это самый простой пример. Формула «Если» может совмещать данные из разных ячеек и таблиц и быть частью сложных формул, когда при нужном условии происходит умножение или другое действие

    Выделить цветом нужные данные

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

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

    Также можно выделить значения, которые находятся в определенном интервале (в условиях форматирования — «между»), содержат нужный текст («текст содержит»), или задать сразу несколько условий

    Суммировать только нужное

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

    Мы попробуем узнать, сколько Аня тратит на еду в офисе. Для этого в таблице создаем формулу =СУММ((А2:А16=F2)*(B2:B16=F3)*C2:С16) и получаем 915 рублей. Теперь постепенно.

    В первой скобке программа ищет значение из ячейки F2 («Аня») в столбце с именами. Во второй скобке — значение из ячейки F3 («Еда на работе») из столбца с категориями расходов. А после считает сумму ячеек из третьего столбца, которые выполнили эти условия.

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

    Расставить по порядку

    В экселе можно быстро узнать максимальное, минимальное и среднее значение для любого массива ячеек. Для этого в скобках формул =МАКС(), =МИН() и =СРЗНАЧ() нужно указать диапазон ячеек, в которых будет искать программа. Это пригодится для таблицы, в которую вы записываете все расходы: вы увидите, на что потратили больше денег, а на что — меньше. Еще этим тратам можно присвоить «места» — и отдать почетное первое место максимальной или минимальной сумме.

    Например, вы считаете зарплаты сотрудников и хотите узнать, кто заработал больше за определенный срок. Для этого в скобках формулы =РАНГ() через точку с запятой укажите ячейку, порядок которой хотите узнать; все ячейки с числами; 1, если нужен номер по возрастанию, или 0, если нужен номер по убыванию.

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

    Источник: journal.tinkoff.ru