000818.ru

Финансовые и Бух. услуги
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как ограничить ввод / ввод значения в ячейку на основе другого значения ячейки в Excel?

Как выделить ячейки в Excel по условию? Выделение группы ячеек

Столбцы и строки образуют ячейки в электронной таблице Excel. Столбец образуется пересечением строки и столбца. Адреса ячеек задаются именем столбца и номером строки на пересечении ячеек и определяют местоположение ячейки на листе.

Ячейка таблицы может содержать число, дату или текст. Сразу после создания ячейки числа и даты автоматически выравниваются вправо, а текст — влево. Запятая используется в качестве разделителя чисел, а точка — в качестве разделителя дат. В конце даты точка не ставится. Числа и даты, нарушающие эти правила, будут рассматриваться в приложении как текст. В одну ячейку можно ввести 32 767 символов. Информацию в ячейках можно отображать в различных форматах, включая числовой и текстовый. Чаще всего ячейки форматируют (изменяя формат), перемещают (изменяя координаты) и удаляют (сдвигая влево или вверх). Я хотел бы рассказать о том, как это делается, причем делается быстро и автоматически.

Выпадающий список в Excel на основе исходных данных.

У нас есть таблица №1, в которой есть два столбца, один с названием конфеты и один с ценой конфеты. Давайте создадим выпадающий список на основе столбца с названием конфеты. Таблица №1 и выпадающий список должны находиться на одном листе.

Выберите ячейку, в которой должен располагаться наш выпадающий список. В данном примере это ячейка F4. Затем найдите кнопку Проверить данные на вкладке Данные.

Выпадающий список в MS Exce

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

Тип данных. Выберите вариант: Список;

Читайте так же:
Как обновить формулу при вставке строк в Excel?

Источник. Выберите диапазон ячеек с данными, которые должны появиться в выпадающем списке. В нашем примере это ячейки таблицы №1 (C4:C13) с названиями конфет. Диапазон можно выбрать, просто поместив курсор в поле Источник. После этого диапазон можно выделить. При выборе диапазона ссылки на него автоматически становятся абсолютными (=$C$4:$C$13).

Проверьте входные значения и другие элементы не должны быть тронуты.

Выпадающий список в MS Excel

Нажмите OK. В указанной ячейке появится выпадающий список. В нашем примере выпадающий список будет содержать названия конфет из таблицы 1.

Выпадающий список в MS Excel

В поле Источник вместо диапазона можно использовать имя субъекта. В поле Источник вместо диапазона =$C$4:$C$13 напишите имя субъекта со знаком равенства. Например: =Конфеты.

Выпадающий список в MS Excel

Выпадающий список можно разместить на отдельном листе. Например, наша таблица 1 будет находиться на листе 1, а выпадающий список, основанный на этой таблице, будет находиться на листе 2. Алгоритм точно такой же, только в поле Source мы выбираем нужный диапазон из таблицы 1. Это не только запишет диапазон ячеек в поле Source, но и имя листа, на котором он находится: =Sheet1!$E$6:$E$15. Если вместо диапазона используется определенное имя, например, Candy, то разницы нет. Определенное имя, перед которым стоит знак равенства, вводится в поле Source: =Candy.

Ввод данных в ячейки

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

При вводе текста в электронную таблицу все данные вводятся в одну строку. Для ввода нескольких строк текста в ячейку нажмите Alt + Enter или выберите Формат ячеек. — Вкладка Выравнивание — команда Перенос по словам в контекстном меню ячейки.

Читайте так же:
Как объединить повторяющиеся строки и суммировать значения в Excel?

Вы можете одновременно вводить одни и те же данные в разные ячейки одного листа. После ввода данных выделите ячейки, в которых они должны появиться, и нажмите Ctrl + Enter для завершения вычисления.

Также можно вводить одни и те же данные на нескольких листах одновременно. Затем выберите ярлыки листов, на которых необходимо ввести данные, и после ввода данных нажмите Enter.

Пять символов

Для подсчета количества ячеек, содержащих определенное количество символов текста, вы можете использовать функцию СЧЁТЕСЛИ. В общей форме формулы (выше), RNG является диапазон ячеек, а «?» соответствует любому одному символу.

Количество ячеек, содержащих 5 символов

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

СЧЁТЕСЛИ подсчитывает количество ячеек в диапазоне, которые содержат пять символов путем сопоставления содержимого каждой ячейки с шаблоном «. », который поставляется в качестве критерия для СЧЁТЕСЛИ. «?» символ является подстановочным в Excel, что означает «любой одиночный символ», так что эта модель будет считать ячейки, которые содержат любые пять символов. Подсчет ячеек, которые соответствуют этому шаблону возвращает число, в данном примере, это число 3.

Функция ЕПУСТО() проверяет содержимое только одной ячейки. Чтобы подсчитать количество пустых ячеек в диапазоне, то используйте функцию СЧИТАТЬПУСТОТЫ() , но если ячейки содержат значение Пустой текст («»), то функция СЧИТАТЬПУСТОТЫ() будет подсчитывать также и эти ячейки наряду с действительно пустыми. Об этом читайте в статье Подсчет пустых ячеек.

Чтобы ответить на вопрос «Есть ли хотя бы 1 пустая ячейка в B6:B11?» используйте формулу массива

Чтобы ответить на вопрос «Есть ли хотя бы 1 заполненная ячейка в B6:B11?» используйте формулу массива

Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).

Читайте так же:
Как отключить сохранение книги, но разрешить сохранение только как в Excel?

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

excel если пусто то другая ячейка

В этом случае мы используем IF вместе с функцией EPUSTA:

Эта формула означает: ЕСЛИ(ячейка D2 пуста, вернуть текст «Пустая», в противном случае вернуть текст «Не пустая»). Вы также можете легко использовать собственную формулу для состояния «Не пустая». В следующем примере вместо функции ЕПУСТО используются знаки «». «» — фактически означает «ничего».

excel если пусто то другая ячейка

Эта формула означает: ЕСЛИ(в ячейке D3 ничего нет, вернуть текст «Пустая», в противном случае вернуть текст «Не пустая»). Вот пример распространенного способа использования знаков «», при котором формула не вычисляется, если зависимая ячейка пуста:

Если (D3 — Nothing, возвращается Nothing, в противном случае вычисляется формула).

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

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

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

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

excel если пусто то другая ячейка

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

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

Настоящий метод не является эффективным способом определения наличия пустых ячеек. В результате формулу необходимо изменить. Вместо 7 следует подставить другое число.

Читайте так же:
Как найти первое / последнее значение больше X в Excel?

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

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

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

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

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

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

И наконец, вот формула Excel, которая позволяет выполнять вычисления только при отсутствии пустых ячеек

Функция NUMBER (или ISNUMBER) возвращает TRUE, если ссылается на число. Естественно, он возвращает FALSE, когда ссылается на пустую ячейку.

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

excel если пусто то другая ячейка

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

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

excel если пусто то другая ячейка

Иными словами, перед нами знакомая формула.

Функции SCHETZ безразлично, что находится в ячейке Excel — число или текст.

То же самое относится и к функции PLAY.

Читайте так же:
Как отключить запрос на сохранение при закрытии книги?

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

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

excel если пусто то другая ячейка

Мы надеемся, что этот материал оказался для вас полезным. Вот еще несколько примеров использования функции IF в Excel для условий.

excel если пусто то другая ячейка

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector