Поиск значения в диапазоне таблицы excel по столбцам и строкам

Как Excel обрабатывает время

Программа Excel «воспринимает» дату и время как обычное число. Электронная таблица преобразует подобные данные, приравнивая сутки к единице. В результате значение времени представляет собой долю от единицы. К примеру, 12.00 – это 0,5.

Значение даты электронная таблица преобразует в число, равное количеству дней от 1 января 1900 года (так решили разработчики) до заданной даты. Например, при преобразовании даты 13.04.1987 получается число 31880. То есть от 1.01.1900 прошло 31 880 дней.

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

Пример функции ДАТА

Построение значение даты, составляя его из отдельных элементов-чисел.

Синтаксис: год; месяц, день.

Все аргументы обязательные. Их можно задать числами или ссылками на ячейки с соответствующими числовыми данными: для года – от 1900 до 9999; для месяца – от 1 до 12; для дня – от 1 до 31.

Если для аргумента «День» задать большее число (чем количество дней в указанном месяце), то лишние дни перейдут на следующий месяц. Например, указав для декабря 32 дня, получим в результате 1 января.

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

Зададим большее количество дней для июня:

Примеры использования в качестве аргументов ссылок на ячейки:

Функция РАЗНДАТ в Excel

Возвращает разницу между двумя датами.

• начальная дата;
• конечная дата;
• код, обозначающий единицы подсчета (дни, месяцы, годы и др.).

Способы измерения интервалов между заданными датами:

• для отображения результата в днях – «d»;
• в месяцах – «m»;
• в годах – «y»;
• в месяцах без учета лет – «ym»;
• в днях без учета месяцев и лет – «md»;
• в днях без учета лет – «yd».

В некоторых версиях Excel при использовании последних двух аргументов («md», «yd») функция может выдать ошибочное значение. Лучше применять альтернативные формулы.

Примеры действия функции РАЗНДАТ:

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

Функция ГОД в Excel

Возвращает год как целое число (от 1900 до 9999), который соответствует заданной дате. В структуре функции только один аргумент – дата в числовом формате. Аргумент должен быть введен посредством функции ДАТА или представлять результат вычисления других формул.

Пример использования функции ГОД:

Функция МЕСЯЦ в Excel: пример

Возвращает месяц как целое число (от 1 до 12) для заданной в числовом формате даты. Аргумент – дата месяца, который необходимо отобразить, в числовом формате. Даты в текстовом формате функция обрабатывает неправильно.

Примеры использования функции МЕСЯЦ:

Примеры функций ДЕНЬ, ДЕНЬНЕД и НОМНЕДЕЛИ в Excel

Возвращает день как целое число (от 1 до 31) для заданной в числовом формате даты. Аргумент – дата дня, который нужно найти, в числовом формате.

Чтобы вернуть порядковый номер дня недели для указанной даты, можно применить функцию ДЕНЬНЕД:

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

Для отображения порядкового номера недели для указанной даты применяется функция НОМНЕДЕЛИ:

Дата 24.05.2015 приходится на 22 неделю в году. Неделя начинается с воскресенья (по умолчанию).

В качестве второго аргумента указана цифра 2. Поэтому формула считает, что неделя начинается с понедельника (второй день недели).

Для указания текущей даты используется функция СЕГОДНЯ (не имеет аргументов). Чтобы отобразить текущее время и дату, применяется функция ТДАТА ().

Excel REPT функция

В Excel функция ПОВТОР используется для повторения символов указанное количество раз. Это полезная функция, которая может помочь вам справиться со многими случаями в Excel, такими как заполнение ячеек до определенной длины, создание гистограммы в ячейках и т. Д.

Синтаксис функции ПОВТОР в Excel:

=REPT (text, number_times)

 Аргументы:

• text: Необходимые. Текст, который нужно повторить.
• number_times: Необходимые. Количество раз повторения текста, оно должно быть положительным числом.

Заметки:

• 1. Если результат функции ПОВТОР превышает 32,767 XNUMX символов, #VALUE! отображается значение ошибки.
• 2. Если число_раз равно 0, функция ПОВТОР возвращает пустой текст.
• 3. Если число_раз не целое число, оно усечено. Например, если число_раз 6.8, текст будет повторяться только 6 раз.

 Примеры:

Пример 1: Использование функции REPT для многократного повторения символов

Используйте эту функцию REPT, чтобы повторить символы x раз:

=REPT(A2,B2)

И вы получите нужный результат, см. Снимок экрана:

Предположим, у вас есть список чисел разной длины, и теперь вы хотите сделать все числа одинаковой длины (6-значными). В этом случае вы можете добавить начальные нули перед числами с помощью функции ПОВТОР.

Введите или скопируйте приведенную ниже формулу в пустую ячейку:

=REPT(«0»,6-LEN(A2))&A2

Внимание: В этой формуле A2 это ячейка, которую вы хотите использовать, — это символ, которым вы хотите дополнить число, число 6 длина текста, которую вы хотите исправить, вы можете изменить ее по своему усмотрению. Затем перетащите дескриптор заполнения вниз к ячейкам, к которым вы хотите применить эту формулу, и все числа будут преобразованы в фиксированные шестизначные строки символов, см

Снимок экрана:

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

Пример 3: Использование функции REPT для создания гистограммы в ячейке

С помощью этой функции REPT вы также можете создать простую гистограмму в ячейке на основе значения ячейки, как показано ниже.

1. В пустую ячейку, куда вы хотите вставить гистограмму, введите следующую формулу:

=REPT(CHAR(110),B2/20)

Внимание: В приведенной выше формуле символ (110) — это символ, который вы хотите повторить, чтобы отобразить символ с кодом ASCII 110; B2 / 20 вычисляет количество раз, которое вы хотите повторить на основе, вы можете изменить расчет на основе ваших данных. 2

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

2. Затем перетащите маркер заполнения вниз к ячейкам, к которым вы хотите применить эту формулу, и вы получите следующий результат:

3. Затем выберите ячейки формулы и измените шрифт текста на форматирование Wingdings, будут отображаться некоторые сплошные квадраты, если вы измените число в столбце B, гистограмма также изменится, см. Снимок экрана:

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

1. Выделите ячейки с гистограммой и нажмите Главная > Условное форматирование > Новое правило.

2. В Новое правило форматирования диалоговое окно:

• (1.) Выберите Используйте формулу, чтобы определить, какие ячейки следует форматировать. в Выберите тип правила список;
• (2.) В Формат значений, где эта формула истинна текстовое поле введите эту формулу: = B2> = 200.
• (3.) Щелкните Формат кнопку, чтобы выбрать нужный цвет шрифта.

3. Затем нажмите OK чтобы закрыть это диалоговое окно, повторите описанные выше шаги, чтобы создать новое правило для данных меньше 200 зеленого цвета.

 Дополнительные функции:

• Функция Excel ВПРАВО
• Функция RIGHT используется для возврата текста справа от текстовой строки.

• Функция ЧИСЛО в Excel
• Функция NUMBERVALUE помогает вернуть действительное число из числа, хранящегося в виде текста.

• Функция ПОИСК в Excel
• Функция ПОИСК может помочь вам найти позицию определенного символа или подстроки в заданной текстовой строке.

Как перевернуть текст в excel

= Мир MS Excel/Статьи об Excel

• 1
• 2
• 3

Приёмы работы с книгами, листами, диапазонами, ячейками

Приёмы работы с формулами

Настройки Excel

Инструменты Excel

Интеграция Excel с другими приложениями

Форматирование

Выпадающие списки

Примечания

Сводные таблицы

Гиперссылки

Excel и интернет

Excel для Windows и Excel для Mac OS

Инструмент камера возвращает динамический снимок указанного диапазона. Динамический — это значит он изменяется при изменении диапазона, который мы сфотографировали. По умолчанию камера недоступна во всех версиях Excel. Для того что бы ей воспользоваться необходимо сначала вывести её на панель меню (в Excel версий до 2007) или на панель быстрого доступа (в Excel версий после 2003). Как это делается?

Для Excel версий до 2007:

Меню Сервис — пункт Настройка — вкладка Команды — категории Сервис — команды Камера — левой кнопкой мыши перетаскиваем иконку камеры на удобное место в меню.

Для Excel версий после 2003:

Кликаем правой кнопкой мыши по панели быстрого доступа — Настройка панели быстрого доступа — Выбрать команды из Все команды — Камера — Добавить — ОК.

Теперь, когда инструмент камера добавлен в меню/на панель быстрого доступа можно приступить к решению вопроса, озвученного в названии этой статьи: Как перевернуть текст в ячейке?Как я уже писал выше — это невозможно. Формулы и функции не работают с форматированием, ориентацию текста в диалоговом окне Формат ячеек можно изменять только в пределах от 90 до -90 градусов, в VBA нет функций, позволяющих это сделать. Остаётся только перевернуть монитор

Выделяем ячейку, которую надо скопировать. Левой кнопкой мыши кликаем на иконку инструмента камера. Окантовка выделенной ячейки станет активной (как при обычном копировании). Левой кнопкой мыши указываем место на листе, куда необходимо вставить снимок и он автоматически вставляется. Всё, мы получили динамический снимок ячейки. Остаётся только перевернуть его на 180 градусов и готово. На рисунке, в ячейке В2, Вы видите снимок ячейки А1:

Все изменения производимые в ячейке А1 будут отображаться в В2.

ПРИМЕЧАНИЕ: Можно так же «фотографировать» и один диапазон. Одновременно несколько ячеек/диапазонов или несмежные диапазоны инструмент камера не обрабатывает, о чём появится соответствующее сообщение, если Вы попытаетесь применить камеру к ним. С получившимся снимком можно работать как с обычным рисунком, внедрённым на лист, то есть, изменять его размеры, свойства, форму, устанавливать прозрачность, рамки и прочее.

Развитие личности

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

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

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

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

Важно помнить, что всё, через что мы проходим, — это жизненные уроки. Это часть опыта

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

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

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

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

1 Простое протягивание формулы

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

• В первую ячейку с одной из сторон (например, сверху) надо записать нужную формулу и нажать Enter.
• После появления рассчитанного по формуле значения навести курсор в нижний правый угол ячейки. Подождать, пока толстый белый крестик не превратиться в тонкий черный.
• Нажать на крестик и, удерживая его, протянуть формулу в нужном направлении. В указанном примере — вниз.

Аргументы в формуле будут изменяться соответственно новому расположению. И если в самой первой ячейке это были F7 и G7, в последней позиции столбца это будет уже F12 и G12. Соответственно, если начинать распространять формулы по строкам, изменяться будут не цифры, а буквы в обозначениях ячеек.

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

Применение функции СЕГОДНЯ в Excel

Разберемся с тем, как работает функция СЕГОДНЯ, с чем не должно возникнуть трудностей даже у начинающих пользователей Excel. Введите в пустой клетке =СЕГОДНЯ() и нажмите Enter для применения. Вы увидите, что на экране появилась текущая дата в стандартном формате ячейки.

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

Далее рассмотрю несколько простых примеров того, как можно комбинировать СЕГОДНЯ с другими функциями в Excel. Например, вы можете вычислить возраст человека, зная его год рождения, для чего введите =ГОД(СЕГОДНЯ())-2000, где 2000 – тот самый год. Вместо этого числа можно подставить номер ячейки, где стоит год рождения. Как результат вы увидите текущий возраст человека.

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

Можно узнать, какое число будет через несколько дней, для чего к СЕГОДНЯ нужно добавить указанное количество дней через +, но в этом случае формат ячейки должен остаться «Дата», ведь вас интересует точный результат, включая день, месяц и год. Точно так же работает и вычитание указанного количества дней от текущей даты.

Если нужно вывести только сегодняшний день, используйте строку =ДЕНЬ(СЕГОДНЯ()), а для месяца =МЕСЯЦ(СЕГОДНЯ()), формат ячейки сохраняйте общим, ведь необходимо отобразить только одно число.

Теперь разберу более сложный вариант комбинирования функций, например, сколько дней пройдет от сегодняшнего дня до указанной даты. В этом случае дату нужно указывать как текст, поэтому комбинировать ее с другими значениями в ячейках не получится. Введите =ДАТАЗНАЧ(«01.01.2000»)-СЕГОДНЯ(), где указанную дату замените на свою.

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

Комьюнити теперь в Телеграм

Подпишитесь и будьте в курсе последних IT-новостей

Подписаться

Последняя заполненная ячейка в EXCEL

history 17 апреля 2013 г.

Поиск позиции

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

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

Диапазон без пропусков и начиная с первой строки

В случае, если в столбце значения вводятся, начиная с первой строки и без пропусков, то определить номер строки последней заполненной ячейки можно формулой: =СЧЁТЗ(A:A))

Формула работает для числовых и текстовых диапазонов (см. Файл примера )

Значение из последней заполненной ячейки в столбце выведем с помощью функции ИНДЕКС() : =ИНДЕКС(A:A;СЧЁТЗ(A:A))

Ссылки на целые столбцы и строки достаточно ресурсоемки и могут замедлить пересчет листа. Если есть уверенность, что при вводе значений пользователь не выйдет за границы определенного диапазона, то лучше указать ссылку на диапазон, а не на столбец. В этом случае формула будет выглядеть так: =ИНДЕКС(A1:A20;СЧЁТЗ(A1:A20))

Диапазон без пропусков в любом месте листа

Если список, в который вводятся значения расположен в диапазоне E8:E30 (т.е. не начинается с первой строки), то формулу для определения номера строки последней заполненной ячейки можно записать следующим образом: =СЧЁТЗ(E9:E30)+СТРОКА(E8)

Формула СТРОКА(E8) возвращает номер строки заголовка списка. Значение из последней заполненной ячейки списка выведем с помощью функции ИНДЕКС() : =ИНДЕКС(E9:E30;СЧЁТЗ(E9:E30))

Диапазон с пропусками (числа)

В случае наличия пропусков (пустых строк) в столбце, функция СЧЕТЗ() будет возвращать неправильный (уменьшенный) номер строки: оно и понятно, ведь эта функция подсчитывает только значения и не учитывает пустые ячейки.

Если диапазон заполняется числовыми значениями, то для определения номера строки последней заполненной ячейки можно использовать формулу =ПОИСКПОЗ(1E+306;A:A;1) . Пустые ячейки и текстовые значения игнорируются.

Так как в качестве просматриваемого массива указан целый столбец ( A:A ), то функция ПОИСКПОЗ() вернет номер последней заполненной строки. Функция ПОИСКПОЗ() (с третьим параметром =1) находит позицию наибольшего значения, которое меньше или равно значению первого аргумента (1E+306). Правда, для этого требуется, чтобы массив был отсортирован по возрастанию. Если он не отсортирован, то эта функция возвращает позицию последней заполненной строки столбца, т.е. то, что нам нужно.

Чтобы вернуть значение в последней заполненной ячейке списка, расположенного в диапазоне A2:A20 , можно использовать формулу: =ИНДЕКС(A2:A20;ПОИСКПОЗ(1E+306;A2:A20;1))

Диапазон с пропусками (текст)

В случае необходимости определения номера строки последнего текстового значения (также при наличии пропусков), формулу нужно переделать: =ПОИСКПОЗ(«*»;$A:$A;-1)

Пустые ячейки, числа и текстовое значение Пустой текст («») игнорируются.

Диапазон с пропусками (текст и числа)

Если столбец содержит и текстовые и числовые значения , то для определения номера строки последней заполненной ячейки можно предложить универсальное решение: =МАКС(ЕСЛИОШИБКА(ПОИСКПОЗ(«*»;$A:$A;-1);0); ЕСЛИОШИБКА(ПОИСКПОЗ(1E+306;$A:$A;1);0))

Функция ЕСЛИОШИБКА() нужна для подавления ошибки возникающей, если столбец A содержит только текстовые или только числовые значения.

Другим универсальным решением является формула массива : =МАКС(СТРОКА(A1:A20)*(A1:A20<>»»))

После ввода формулы массива нужно нажать CTRL + SHIFT + ENTER . Предполагается, что значения вводятся в диапазон A1:A20 . Лучше задать фиксированный диапазон для поиска, т.к. использование в формулах массива ссылок на целые строки или столбцы является достаточно ресурсоемкой задачей.

Значение из последней заполненной ячейки, в этом случае, выведем с помощью функции ДВССЫЛ() : =ДВССЫЛ(«A»&МАКС(СТРОКА(A1:A20)*(A1:A20<>»»)))

Как обычно, после ввода формулы массива нужно нажать CTRL + SHIFT + ENTER вместо ENTER .

СОВЕТ: Как видно, наличие пропусков в диапазоне существенно усложняет подсчет. Поэтому имеет смысл при заполнении и проектировании таблиц придерживаться правил приведенных в статье Советы по построению таблиц .

Самые полезные функции Excel

Среди многих функций Excel есть некоторые скрытые функции, которыми легко пользоваться, и многие из них вам не известны. Без дальнейших задержек мы рассмотрим 5 таких функций Excel.

Sparklines

Спарклайны были впервые представлены в Excel 2010 и используются для представления визуализаций для тренда по данным в ряд. Он помещается в одну ячейку Excel и экономит место на листе. Это классная функция и она очень проста в использовании. Вычисление тренда для данных строк и размещение визуализации в одном Excel – отличная возможность для использования.

Чтобы создать свои собственные Sparklines, выберите диапазон данных. Нажмите вставить на ленте и выберите тип Спарклайны (линия, столбец или выигрыш/проигрыш). Затем введите диапазон цели, где вы хотите показать Sparklines. Для получения дополнительной информации о том, как создать Sparklines, посетите Офисные блоги.

Условное форматирование

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

Чтобы создать тепловую карту, выберите данные и перейдите на ленту. В разделе Главная нажмите Условное форматирование , а затем нажмите Цветовые шкалы. Теперь выберите цветовую гамму. Вы даже можете установить цветовую шкалу, отредактировав правило форматирования. Для получения дополнительной информации об условном форматировании посетите веб-сайт поддержки Office.

МАЛЫЕ и БОЛЬШИЕ функции

Мы все знаем о функциях MAX и MIN. Они дают вам максимальные и минимальные значения выбранных данных соответственно. Но, чтобы найти 1-е, 2-е, 3-е или n-е наибольшее или наименьшее значение выбранного диапазона данных, мы можем использовать функции LARGE и SMALL соответственно.

В этом примере, чтобы найти два лучших продукта для каждого месяца, мы использовали функции MATCH и INDEX вместе с функциями LARGE и SMALL. Для получения дополнительной информации посетите МАЛЕНЬКИЕ и БОЛЬШИЕ функции.

Удалить дубликаты

Не вините меня за упоминание этой функции в этом списке

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

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

Чтобы удалить дубликаты, выберите данные и перейдите на ленту. В разделе Данные нажмите кнопку Удалить дубликаты , чтобы просмотреть данные без дубликатов. Для получения дополнительной информации о том, как найти и удалить дубликаты, посетите сайт Office.com.

Срезы

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

В Excel 2013, если вы хотите добавить Slicer в свои диаграммы, выберите диапазон данных и нажмите insert> Slicer. Теперь выберите часть данных, которую вы хотите использовать в качестве фильтра. На изображении выше столбец Product используется в качестве фильтра. Как здесь для получения дополнительной информации о том, как использовать слайсеры.

Для получения более подробной информации об этих функциях и для загрузки учебного пособия в качестве примера посетите раздел «Блоги Office»

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

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

1	= СМЕЩЕНИЕ (ссылка, строки, столбцы)

1	= СМЕЩЕНИЕ (B2,0; ПОИСКПОЗ (МАКС (B2: XFD2) + 1; B2: XFD2,1) -1)

Посмотрим, как работает эта формула.

Функция ПОИСКПОЗ

Мы используем функцию MATCH, чтобы «подсчитать», сколько значений ячеек меньше 1 + максимум всех значений в строке 2, начиная с B2.

1	= ПОИСКПОЗ (искомое_значение, искомое_массив, )

1	= ПОИСКПОЗ (МАКС (B2: XFD2) + 1; B2: XFD2,1)

Lookup_value функции MATCH является максимальным из всех значений в строке2 + 1. Поскольку это значение, очевидно, не существует в строке2, а match_type установлено в 1 (меньше или равно lookup_value), функция MATCH вернет Позиция последней «проверенной» ячейки в массиве, то есть количество ячеек, заполненных данными в диапазоне B2: XFD2 (XFD — самый последний столбец в новых версиях Excel).

СМЕЩЕНИЕ Функция

Затем мы используем функцию OFFSET, чтобы получить значение этой ячейки, положение которой было возвращено функцией MATCH.

1	= СМЕЩЕНИЕ (B2,0; C4-1)

Функция НЕ

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

Функция NOT меняет значение своего аргумента. Так что, если логическое значение ИСТИНА, тогда оно возвращает ЛОЖЬ. И если логическое значение ЛОЖЬ, оно вернет ИСТИНА.

Это будет легче объяснить на некоторых примерах.

Структура функции НЕ имеет вид;

  = НЕ (логическое) 

НЕ Функциональный Пример 1

В этом примере представьте, что у нас есть головной офис в Лондоне, а затем много других региональных сайтов. Мы хотим отобразить слово «Да», если на сайте есть что-то, кроме Лондона, и «Нет», если это Лондон.

Функция NOT была вложена в логический тест функции IF ниже, чтобы сторнировать ИСТИННЫЙ результат.

  = ЕСЛИ (НЕ (B2 = "London"), "Да", "Нет") 

Это также может быть достигнуто с помощью логического оператора NOT <>. Ниже приведен пример.

  = ЕСЛИ (В2 <> "Лондон", "Да", "Нет") 

НЕ Функциональный Пример 2

Функция NOT полезна при работе с информационными функциями в Excel. Это группа функций в Excel, которые что-то проверяют и возвращают TRUE, если проверка прошла успешно, и FALSE, если это не так.

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

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

Функция ISTEXT используется для проверки наличия текста. Это возвращает TRUE, если текст есть, поэтому функция NOT переворачивает это на FALSE. И если ИФ выполняет свой расчет.

  = ЕСЛИ (НЕ (ISTEXT (В2)), В2 * 5%, 0) 

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

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

Поиск последнего значения в строке или столбце функцией ПРОСМОТР

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

Задача: найти значение продаж в последнем месяце по каждому филиалу, т.е. для Москвы это будет 78, для Питера — 41 и т.д.

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

Универсальным решением будет использование функции ПРОСМОТР (LOOKUP) :

У этой функции хитрая логика:

• Она по очереди (слева-направо) перебирает непустые ячейки в диапазоне (B2:M2) и сравнивает каждую из них с искомым значением (9999999).
• Если значение очередной проверяемой ячейки совпало с искомым, то функция останавливает просмотр и выводит содержимое ячейки.
• Если точного совпадения нет и очередное значение меньше искомого, то функция переходит к следующей ячейке в строке.

Легко сообразить, что если в качестве искомого значения задать достаточно большое число, то функция пройдет по всей строке и, в итоге, выдаст содержимое последней проверенной ячейки. Для компактности, можно указать искомое число в экспоненциальном формате, например 1E+11 (1*10 11 или сто миллиардов).

Если в таблице не числа, а текст, то идея остается той же, но «очень большое число» нужно заменить на «очень большой текст»:

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

1. латиница прописные (A-Z)
2. латиница строчные (a-z)
3. кириллица прописные (А-Я)
4. кириллица строчные (а-я)

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

Вот так. Не совсем очевидное, но красивое и компактное решение. Для поиска последней непустой ячейки в столбцах работает тоже «на ура».

Производим вычисления по условию.

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

В примере ниже столбец F содержит даты завершения закупок шоколада.

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

Формула в ячейке F3:

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

Функция СЧЕТЗ (английский вариант — COUNTA) подсчитывает количество значений (текстовых, числовых и логических) в диапазоне ячеек Excel. Если мы знаем количество значений в диапазоне, то легко можно составить условие. Если число значений равно числу ячеек Excel, то значит, пустых среди них нет и можно производить вычисление. Если такого равенства нет, значит есть хотя бы одна пустая ячейка, и вычислять нельзя.

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

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

Функция ЕПУСТО (английский вариант — ISBLANK) проверяет, не ссылается ли она на пустую ячейку. Если это так, то возвращает ИСТИНА.

Функция ИЛИ (английский вариант — OR) позволяет объединить условия и указать, что нам достаточно того, чтобы хотя бы одна функция ЕПУСТО обнаружила пустую ячейку. В этом случае никаких вычислений не производим и функция ЕСЛИ возвращает пустую строку. А вот если не пусто – то производим вычисления.

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

Рассмотрим теперь более универсальные решения.

В качестве аргумента условия в функции ЕСЛИ мы используем СЧИТАТЬПУСТОТЫ (английский вариант — COUNTBLANK). Она возвращает количество пустых ячеек, но любое число больше 0 Excel интерпретирует как ИСТИНА.

И, наконец, еще одна формула ЕСЛИ (IF) в Excel, которая проверит «если не пусто» и позволит производить расчет только при наличии непустых ячеек.

Функция ЕЧИСЛО (или ISNUMBER) возвращает ИСТИНА, если ссылается на число. Естественно, при ссылке на пустую ячейку возвратит ЛОЖЬ.

А теперь посмотрим, как это работает. Заполним таблицу недостающим значением.

Как видите, все наши формулы рассчитаны и возвратили одинаковые значения.

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

Итак, перед нами уже знакомое выражение

Для функции СЧЕТЗ не имеет значения, число или текст используются в ячейке Excel.

То же можно сказать и о функции СЧИТАТЬПУСТОТЫ.

А вот третий вариант — к проверке условия при помощи функции ЕЧИСЛО добавляем проверку ЕТЕКСТ (ISTEXT в английском варианте). Объединяем их функцией ИЛИ.

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

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

Надеемся, этот материал был полезен. А вот еще несколько примеров работы с условиями «если – то» при помощи функции ЕСЛИ (IF) в Excel.

Примеры использования функции ЕСЛИ: