Содержание

Как присвоить значение ячейке в зависимости от значения другой ячейки excel

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

ИП Павлов Николай Владимирович
ИНН 633015842586
ОГРН 310633031600071

Связанный список в EXCEL

Отдел – Сотрудники отдела . При выборе отдела из списка всех отделов компании, динамически формируется список, содержащий перечень фамилий всех сотрудников этого отдела (двухуровневая иерархия);
Город – Улица – Номер дома . При заполнении адреса проживания можно из списка выбрать город , затем из списка всех улиц этого города – улицу , затем, из списка всех домов на этой улице – номер дома (трехуровневая иерархия).

Создание Связанного списка на основе Проверки данных рассмотрим на конкретном примере.

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

Таблицу, в которую будут заноситься данные с помощью Связанного списка , разместим на листе Таблица . См. файл примера Связанный_список.xlsx

Список регионов и перечни стран разместим на листе Списки .

Обратите внимание, что названия регионов (диапазон А2:А5 на листе Списки ) в точности должны совпадать с заголовками столбцов, содержащих названия соответствующих стран ( В1:Е1 ).

Присвоим имена диапазонам, содержащим Регионы и Страны (т.е. создадим Именованные диапазоны ). Быстрее всего это сделать так:

выделитьячейки А1:Е6 на листе Списки (т.е. диапазон, охватывающий все ячейки с названиями Регионов и Стран );
нажать кнопку «Создать из выделенного фрагмента» (пункт меню Формулы/ Определенные имена/ Создать из выделенного фрагмента );
Убедиться, что стоит только галочка «В строке выше»;
Нажать ОК.

Проверить правильность имени можно через Диспетчер Имен ( Формулы/ Определенные имена/ Диспетчер имен ). Должно быть создано 5 имен.

Можно подкорректировать диапазон у имени Регионы (вместо =списки!$A$2:$A$6 установить =списки!$A$2:$A$5 , чтобы не отображалась последняя пустая строка)

На листе Таблица , для ячеек A 5: A 22 сформируем выпадающий список для выбора Региона .

Теперь сформируем выпадающий список для столбца Страна (это как раз и будет желанный Связанный список ).

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

Тестируем. Выбираем с помощью выпадающего списка в ячейке A 5 Регион – Америка , вызываем связанный список в ячейке B 5 и балдеем – появился список стран для Региона Америка : США, Мексика …

Теперь заполняем следующую строку. Выбираем в ячейке A 6 Регион – Азия , вызываем связанный список в ячейке B 6 и опять балдеем: Китай, Индия …

Теперь о недостатках . При создании имен с помощью кнопки меню Создать из выделенного фрагмента, все именованные диапазоны для перечней Стран были созданы одинаковой длины (равной максимальной длине списка для региона Европа (5 значений)). Это привело к тому, что связанные списки для других регионов содержали пустые строки.

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

Чтобы не создавать десятки имен, нужно изменить сам подход при построении Связанного списка . Рассмотрим этот подход в другой статье: Расширяемый Связанный список .

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

Случаются ситуации, когда необходимо, чтобы ячейке присваивалось значение, зависящее от какого-либо результата выраженного в цифровом эквиваленте. Для таких ситуаций подойдет функция «ВЫБОР» или в английской версии «CHOOSE». Данная функция присваивает ячейке результат по заданному индексу.

Рассмотрим пример использования функции «ВЫБОР».

Например, необходимо автоматизировать присвоения школьникам статуса в зависимости от оценки, которую они получили: 2 – двоечник, 3- троечник и так далее.

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

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

Как прописать функцию «Выбор».

В поля Значение 1,2… и так далее указываем статусы учеников по возрастанию.

=ВЫБОР(B7;C6;D6;E6;F6;G6)

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

[expert_bq id=»1570″]Работы С Нами Kutools for Excel Автора Выделить имена утилита, все именованные диапазоны в активном листе будут выделены цветом фона. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Случаются ситуации, когда необходимо, чтобы ячейке присваивалось значение, зависящее от какого-либо результата выраженного в цифровом эквиваленте. Для таких ситуаций подойдет функция «ВЫБОР» или в английской версии «CHOOSE». Данная функция присваивает ячейке результат по заданному индексу.

Как увидеть все именованные диапазоны в Excel?

Чтобы переименовать рабочий лист, например Лист1,можно воспользоваться контекстным меню или сделать двойной щелчок по ярлычку листа, в появившемся окне удалить старое имя (Лист1)и ввести новое имя, например Таблица.Для завершения ввода следует щелкнуть по кнопке ОК или нажать клавишу Enter.

Зафиксированная ячейка	Что происходит при копировании или перемещении	Клавиши на клавиатуре
$A$1	Столбец и строка не меняются.	Нажмите F4.
A$1	Строка не меняется.	Дважды нажмите F4.
$A1	Столбец не изменяется.	Трижды нажмите F4.

[expert_bq id=»1570″]Чтобы ввести ссылку на всю строку или столбец, нужно набрать номер строки или букву столбца дважды и разделить их двоеточием, например А А , 2 2 или А В, 2 4. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] При копировании формул в Excel действует правило относительной ориентации ячеек, суть которого состоит в том, что при копировании формулы табличный процессор автоматически смещает адрес в соответствии с относительным расположением исходной ячейки и создаваемой копии.

Для Чего Присваиваются Имена Ячейкам в Excel • Как зафиксировать ячейку дав ей имя

Как присвоить значение ячейке в зависимости от значения другой ячейки excel

Ячейки в свою очередь располагаются на конкретном листе книги Excel. Каждый лист книги имеет свое уникальное название. По умолчанию они называются «Лист1», «Лист2» и т.д. Книга Excel должна состоять как минимум из одного листа.
[expert_bq id=»1570″]Диапазон это А все ячейки одной строки; Б совокупность клеток, образующих в таблице область прямоугольной формы; В все ячейки одного столбца; Г множество допустимых значений. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Чтобы переименовать рабочий лист, например Лист1,можно воспользоваться контекстным меню или сделать двойной щелчок по ярлычку листа, в появившемся окне удалить старое имя (Лист1)и ввести новое имя, например Таблица.Для завершения ввода следует щелкнуть по кнопке ОК или нажать клавишу Enter.

Excel фамилия и инициалы из полного фио

Предположим, нужно рассчитать цены продажи при разных уровнях наценки. Для этого нужно умножить колонку с ценами (столбец В) на 3 возможных значения наценки (записаны в C2, D2 и E2). Вводим выражение для расчёта в C3, а затем копируем его сначала вправо по строке, а затем вниз:

Дополнительные данные → Похожие темы → Все про Exel → Как вставить значения → Как объединить ячейки → Как вставить форматы → Дополнительные данные → Вставить формулы→ Аргументы функции