Vba excel. функции преобразования типов

Формула ЕСЛИ в Excel – примеры нескольких условий

Довольно часто количество возможных условий не 2 (проверяемое и альтернативное), а 3, 4 и более. В этом случае также можно использовать функцию ЕСЛИ, но теперь ее придется вкладывать друг в друга, указывая все условия по очереди. Рассмотрим следующий пример.

Нескольким менеджерам по продажам нужно начислить премию в зависимости от выполнения плана продаж. Система мотивации следующая. Если план выполнен менее, чем на 90%, то премия не полагается, если от 90% до 95% — премия 10%, от 95% до 100% — премия 20% и если план перевыполнен, то 30%. Как видно здесь 4 варианта. Чтобы их указать в одной формуле потребуется следующая логическая структура. Если выполняется первое условие, то наступает первый вариант, в противном случае, если выполняется второе условие, то наступает второй вариант, в противном случае если… и т.д. Количество условий может быть довольно большим. В конце формулы указывается последний альтернативный вариант, для которого не выполняется ни одно из перечисленных ранее условий (как третье поле в обычной формуле ЕСЛИ). В итоге формула имеет следующий вид.

Комбинация функций ЕСЛИ работает так, что при выполнении какого-либо указанно условия следующие уже не проверяются

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

они входят в интервал B2

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

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

Пример 3: Определяем стартовую позицию для SEARCH (ПОИСК)

Если мы запишем два знака минус (двойное отрицание) перед функцией ISNUMBER (ЕЧИСЛО), то она возвратит значения 1/ вместо TRUE/FALSE (ИСТИНА/ЛОЖЬ). Далее, функция SUM (СУММ) в ячейке E2 подсчитает суммарное количество записей, где искомый текст был найден.

В следующем примере в столбце B показаны:

Название города | Профессия

Наша задача найти профессии, содержащие текстовую строку, введённую в ячейке E1. Формула в ячейке C2 будет следующая:

Данная формула нашла строки, которые содержат слово “bank”, но в одной из них это слово встречается не в названии профессии, а в названии города. Это нас не устраивает!

После каждого названия города стоит символ | (вертикальная черта), поэтому мы, используя функцию SEARCH (ПОИСК), можем найти позицию этого символа. Его позиция может быть указана, как значение аргумента start_num (нач_позиция) в “главной” функции SEARCH (ПОИСК). В результате названия городов будут проигнорированы поиском.

Теперь проверенная и исправленная формула будет считать только те строки, которые содержат слово “bank” в названии профессии:

Дата и время

За любой датой в Excel скрывается целое число. Датой его делает формат.

Аналогично со временем: одна единица — это день, а часть единицы (число от 0 до 1) — время, то есть часть дня.

Это не значит, что так имеет смысл вводить даты и время в ячейки, вводите их в любом из стандартных форматов — Excel сразу отформатирует их как даты:

ДД.ММ.ГГГГ

ДД/ММ/ГГГГ

ГГГГ-ММ-ДД

С датами можно производить операции вычитания и сложения.

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

Прибавить к дате число — и получить дату, которая наступит через соответствующее количество дней.

Вычислить рейтинг с неполным совпадением ВПР

ВПР без ошибки #н/д

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

Эта Таблица должна быть отсортирована по возрастанию, и ВПР должен быть настроен сделать «приблизительное соответствие».

В показанном примере, формула ВПР выглядит так:

Как эта формула работает

В данном случае $F$6-результат преобразовать в (34 в примере) $C$6:$D$17-это таблица с оценкой и рейтингом, состоящий из 2-столбец таблицы 2 говорит ВПР, чтобы получить данные из колонки 2 (рейтинг), и верно говорит ВПР сделать «приблизительное соответствие».

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

Иными словами, ВПР поиск последнего значения, которое меньше или равно значению поиска.

Если первое значение в таблице меньше значения, ВПР вернет #н/д ошибка.

Примечание: по умолчанию функция ВПР будет выполнять приближенное сравнение, так что нет никакой необходимости, чтобы поставить 4-м аргументом, поскольку значение по умолчанию-True. Тем не менее, мы рекомендуем Вам войти в привычку поставляя последний аргумент, так что вы иметь визуальное напоминание о текущем режиме поиска.

Excel At Excel вып.3: Собираем данные с разных листов

Дано: 22 таблицы унифицированного формата с перечнем помещений от 22 дочерних предприятий.

Задача: сделать сводную таблицу с данными всех 22 таблиц

Итак, решение. Есть три варианта решения данной задачи. Первый — использовать встроенную (начиная с excel 2013) надстройку PowerPivot (об этом методе мы поговорим позднее). Второй — посредством сводной таблицы через несколько диапазонов консолидации. Не люблю этот метод, т.к. такая сводная таблица имеет существенно более ограниченный функционал по сравнению с простой сводной таблицей. Наконец, третий вариант — это создать общую таблицу с использованием функции INDIRECT (ДВССЫЛ). Этому методу и посвятим сегодня наш пост.

Что мы имеем? 22 одинаковых по формату таблицы на отдельных листах и с разным количеством строк в каждой. Соответственно, для построения общей таблицы нам надо решить следующие «проблемы»:

1) как заставить общую таблицу «переключаться» с одного листа данных на другой?;

2) как заставить ее делать это в нужный момент (когда закончились строки на одном листе)?

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

Обратите внимание на формулу в столбце Кол-во записей. Мы могли бы использовать просто COUNTA (СЧЁТА) со ссылкой на каждый лист

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

Именно для этого в функцию COUNTA (СЧЁТА) и внедряется эта чуднАя функция INDIRECT (ДВССЫЛ). Остановимся на ней подробнее.

Функция имеет, по сути, единственный параметр — ref_text (ссылка_на_текст). Что делает эта функция? Всего-навсего преобразует текст в ссылку на ячейку. То есть она преобразует текст, например, «А1» в ссылку и возвращает значение ячейки А1.

Соответственно, зная несложные правила работы с текстом в Excel, мы можем легко сделать текст внутри изменяемым, а значит можем сделать и изменяемыми ссылки, которые будет возвращать функция INDIRECT (ДВССЫЛ).

Разберем пример выше. Функция INDIRECT (ДВССЫЛ) имеет следующий вид:

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

Соответственно, в нашем случае у нас неизменен столбец B:B, а вот название листа меняется. Для «автоматизации» формулы заменяем Название листа на ссылку на название листа с данными по соответствующему обществу (столбец Название листа). Получаем следующее:

где все, что подчеркнуто — текст, а выделено жирным — ссылки.

ВАЖНО! Обратите внимание на кавычки и конкатенацию («склеивание») при помощи амперсента. Не забудьте, что текст вносится в кавычках и соединяется со ссылками при помощи символа &

Получается, что INDIRECT (ДВССЫЛ) получает название листа из ячеек в столбце А:А (Название листа), а диапазон у нас прописан фиксированно текстом (В:В). COUNTA (СЧЁТА) же просто считает количество строк в столбце В:В на соответствующем листа за вычетом заголовка.

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

=IF(A2=»»;»»;IF(COUNTIF($A$2:A2;A2)=OFFSET(‘Список обществ’!$E$2;A2-1;0);IF(A2+1>COUNTA(‘Список обществ’!A:A)-1;»»;A2+1);A2))

Затем пишем формулу второго цикла:

Далее при помощи уже знакомой нам функции OFFSET (СМЕЩ) заполняем столбцы из таблицы на листе Список обществ.

Осталось подтянуть данные с 22 листов по обществам. Тут нам опять поможет OFFSET (СМЕЩ), но уже в паре с INDIRECT (ДВССЫЛ).

OFFSET (СМЕЩ) передает в формулу название листа, а конструкция B2+1 — номер строки в столбце Е на том листе.

ВАЖНО! Обратите внимание, что OFFSET (СМЕЩ) вкладывается внутрь INDIRECT (ДВССЫЛ), а не наоборот

Как рассчитать текущий возраст по дате рождения в Excel

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

Как рассчитать текущий возраст в полных годах

Если вы хотите рассчитать чей-то текущий возраст по дате рождения, лучше всего использовать функцию ДОЛЯ ГОДА. Это возвращает общее количество полных лет плюс часть текущего года.

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

Чтобы рассчитать возраст в годах, используя YEARFRAC:

1. Откройте Эксель.
2. Щелкните ячейку, в которой вы хотите указать возраст.
3. Тип =TRUNC(YEARFRAC(
4. Выберите ячейку, содержащую дату рождения.
5. Введите СЕГОДНЯ ())) и нажмите Enter.
6. Рассчитывается возраст человека в годах.
7. Если у вас есть другие возрасты, которые необходимо рассчитать, щелкните ячейку, содержащую вашу формулу, затем щелкните и удерживайте маленький квадрат в правом нижнем углу ячейки.
8. Потяните вниз, чтобы применить формулу к другим ячейкам.
9. Отпустите кнопку мыши — другие возрасты будут рассчитаны.

Как рассчитать текущий возраст в годах и месяцах

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

Чтобы рассчитать возраст в годах и месяцах в Excel:

1. Щелкните ячейку, в которой вы хотите указать возраст.
2. Введите =РАЗНДАТ(
3. Щелкните ячейку с датой рождения.
4. Введите ,СЕГОДНЯ(),»Г») и «Годы и» и РАЗНДАТ(
5. Щелкните ячейку с датой рождения еще раз.
6. Введите «СЕГОДНЯ()», «ГМ») и «Месяцы» и нажмите Enter.
7. Будет рассчитан возраст в годах и месяцах.
8. Чтобы применить формулу к другим ячейкам, нажмите и удерживайте квадрат в правом нижнем углу ячейки и перетащите его вниз.
9. Теперь подсчитываются другие возрасты.

Как рассчитать текущий возраст в годах, месяцах и днях

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

Чтобы рассчитать возраст в годах, месяцах и днях в Excel:

1. Щелкните ячейку, в которой вы хотите указать возраст.
2. Введите =РАЗНДАТ(
3. Щелкните ячейку с датой рождения.
4. Введите ,СЕГОДНЯ(),»Г») и «Годы и» и РАЗНДАТ(
5. Щелкните ячейку с датой рождения еще раз.
6. Введите «СЕГОДНЯ()», «ГМ») и «Месяцы» и «РАЗНДАТ(
7. Щелкните ячейку с датой рождения еще раз.
8. Введите «СЕГОДНЯ()», «MD») и «Дни» и нажмите Enter.
9. Возраст будет исчисляться в годах, месяцах и днях.
10. Чтобы применить формулу к другим ячейкам, нажмите и удерживайте маленький квадрат в правом нижнем углу ячейки и перетащите его вниз.
11. Другие века подсчитают.

Дополнительные ресурсы для изучения использования Excel

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

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

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

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

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

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

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

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

Создание пользовательских форматов

Excel позволяет создать свой (пользовательский) формат ячейки. Многие знают об этом, но очень редко пользуются из-за кажущейся сложности. Однако это достаточно просто, главное понять основной принцип задания формата.

Для того, чтобы создать пользовательский формат необходимо открыть диалоговое окно Формат ячеек и перейти на вкладку Число. Можно также воспользоваться сочетанием клавиш Ctrl + 1.

В поле Тип вводится пользовательские форматы, варианты написания которых мы рассмотрим далее.

В поле Тип вы можете задать формат значения ячейки следующей строкой:

”любой текст”КодФормата”любой текст”

Посмотрите простые примеры использования форматирования. В столбце А – значение без форматирования, в столбце B – с использованием пользовательского формата (применяемый формат в столбце С)

Какие цвета можно применять

В квадратных скобках можно указывать один из 8 цветов на выбор:

Синий, зеленый, красный, фиолетовый, желтый, белый, черный и голубой.

Числовые форматы

Символ	Описание применения	Пример формата	До форматирования	После форматирования
#	Символ числа. Незначащие нули в начале или конце число не отображаются	######	001234	1234
Символ числа. Обязательное отображение незначащих нулей	000000	1234	001234
,	Используется в качестве разделителя целой и дробной части	####,#	1234,12	1234,1
пробел	Используется в качестве разделителя разрядов	# ###,#0	1234,1	1 234,10

Форматы даты

Формат	Описание применения	Пример отображения
М	Отображает числовое значение месяца	от 1 до 12
ММ	Отображает числовое значение месяца в формате 00	от 01 до 12
МММ	Отображает сокращенное до 3-х букв значение месяца	от Янв до Дек
ММММ	Полное наименование месяца	Январь – Декабрь
МММММ	Отображает первую букву месяца	от Я до Д
Д	Выводит число даты	от 1 до 31
ДД	Выводит число в формате 00	от 01 до 31
ДДД	Выводит день недели	от Пн до Вс
ДДДД	Выводит название недели целиком	Понедельник – Пятница
ГГ	Выводит последние 2 цифры года	от 00 до 99
ГГГГ	Выводит год даты полностью	1900 – 9999

Стоит обратить внимание, что форматы даты можно комбинировать между собой. Например, формат “ДД.ММ.ГГГГ” отформатирует дату в привычный нам вид 31.12.2017, а формат “ДД МММ” преобразует дату в вид 31 Дек

Как вводить даты и время в Excel

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

“Классическая” форма	3.10.2006
Сокращенная форма	3.10.06
С использованием дефисов	3-10-6
С использованием дроби	3/10/6

Внешний вид (отображение) даты в ячейке может быть очень разным (с годом или без, месяц числом или словом и т.д.) и задается через контекстное меню – правой кнопкой мыши по ячейке и далее Формат ячеек (Format Cells):

Время вводится в ячейки с использованием двоеточия. Например

16:45

По желанию можно дополнительно уточнить количество секунд – вводя их также через двоеточие:

16:45:30

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

27.10.2012 16:45

Быстрый ввод дат и времени

Для ввода сегодняшней даты в текущую ячейку можно воспользоваться сочетанием клавиш Ctrl + Ж (или CTRL+SHIFT+4 если у вас другой системный язык по умолчанию).

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

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

Суммирование ячеек с числами и текстом в Excel

Пример 2. В таблице содержатся данные о суммарной выручке двух магазинов по дням. В некоторых ячейках содержатся текстовые данные “N”, свидетельствующие о том, что магазин не имел выручки. Рассчитать суммарную выручку для магазинов по дням.

Вид таблицы данных:

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

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

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

Ввод дат и времени

Несмотря на то, что даты и время в Excel записываются в виде десятичных дробей, весьма затруднительно вводить их именно в таком виде. Можно вводить даты и время  в одном из заданных форматов: Д.М.ГГ, Д МММ ГГ, Д МММ или МММ ГГ (для  записи года можно использовать и 4 цифры).

Если введенное значение не соответствует ни одному из указанных встроенных форматов, то Excel применяет к нему наиболее подходящий. Например, при вводе 1 дек вы увидите в ячейке отформатированное значение 01.дек, в строке формул при этом будет отображаться 01.12.2003 (если, разумеется, текущий год 2003).

При вводе времени также можно использовать встроенные форматы: ч:мм АМ/РМ, ч:мм:сс АМ/РМ, ч:мм, ч:мм:сс или комбинированные форматы даты и времени – Д.ММ.ГГ ч:мм

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

Если при вводе времени не используются символы AM, РМ, am или pm, то Excel применит 24-часовой формат, иначе 12-часовой. Другими словами, значение 3:00 всегда воспринимается как 3:00 AM, если только специально не указать  3:00 РМ.

Если пользователь ввел в состав даты значение года, состоящее из двух цифр, то она интерпретируется так: двузначные значения года от 00 до 29 интерпретируется как годы с 2000 по 2029; двузначные значения года от 30 до 99 интерпретируется как годы с 1930 по 1999. Например, дата 02.03.29 будет воспринята как 02.03.2029, а дата 02.03.31 – как 02.03.1931.

Функция 18: SEARCH (ПОИСК)

Функция SEARCH (ПОИСК) отыскивает текстовую строчку снутри иной текстовой строчки, и, если находит, то докладывает её позицию.

Как можно применять функцию SEARCH (ПОИСК)?

Функция SEARCH (ПОИСК) отыскивает текстовую строчку снутри иной текстовой строчки. Она может:

• Отыскать строчку текста снутри иной текстовой строчки (без учёта регистра).
• Употреблять в поиске знаки подстановки.
• Найти стартовую позицию в просматриваемом тексте.

Синтаксис SEARCH (ПОИСК)

Функция SEARCH (ПОИСК) имеет вот таковой синтаксис:

• find_text (искомый_текст) – текст, который Вы ищете.
• within_text (текст_для_поиска) – текстовая строчка, снутри которой происходит поиск.
• start_num (нач_позиция) – если не указан, то поиск начнётся с первого знака.

Ловушки SEARCH (ПОИСК)

Функция SEARCH (ПОИСК) вернет позицию первой совпадающей строчки, не зависимо от регистра. Если Для вас нужен поиск с учётом регистра, Вы сможете применять функцию FIND (НАЙТИ), с которой мы познакомимся дальше в рамках марафона 30 функций Excel за 30 дней.