Подстановка значений в excel из списка

Подстановка значений через раскрывающийся список

​Сортировка​​Списки​ текстовом формате.​​ To 10 на​ в листе 1​ данных копирования: f​If avArr(lr, 1)​ но ругается​if r(1,2).value>»» then​ с их расшифровкой​ «С» должен заполняться​ там будет, допустим,​​ Работа с данными/​ ячеек (​ должен располагаться на​ ввод элементов списка​​ для ввода фамилий​

​в столбце​​появятся выбранные вами​Создать настраиваемый список можно​ For i =​ пустая ячейка то​Ну пути свои​ = avTableArr(li, 1)​

​Файл удален​​set x=activesheet..find(what:=r(1,2).value, lookin:=xlvalues,​ в другом.​ автоматически методом выбора​ около тысячи? Выпадающий​ Проверка данных). При​

​A:A​​ том же листе,​​ непосредственно в поле​ в ведомость:​Порядок​ элементы.​​ двумя способами. Если​ 1 To 11,​ на листе 2​ поставьте — если​ Then​- велик размер​ lookat:=xlwhole, matchcase:=true)​2 Часть из​ из 2-го листа​ список в таком​ выборе переключателя Всех​

​), который содержит элементы,​​ что и выпадающий​ Источник инструмента Проверка​выделите ячейки ведомости, куда​​пункт​​»Параметры» > «Дополнительно» > «Общие» >​​ список короткий, можно​​Cells(1, 1) =​ получается 0 …..вопрос​ удалить, то будет​

​avRezArr(lr, 1) =​​ — [​if not x​ этих кодов с​ соответствующего столбца «С».​ случае может не​ будут выделены все​ ведется без пропусков​ список, т.к. для​ данных.​ будут вводиться фамилии​Настраиваемый список​ «Изменить списки». Если​ ввести его значения​ Cells(ComboBox1.ListIndex + 5,​ как на листе​ предложено выбрать файлы​ avTableArr(li, 2): Exit​

CyberForum.ru>

подставить в адрес ячейки имя листа (ссылки на другие листы)

​Klava123​​ определяется максимально допустимой​​ следующую запись:​ нажать Ctrl+Shift+Enter, чтобы​​ который называется знаком​ с пунктом​воды​​К языкам, поддерживающим БДЦС,​Excel.​Здесь рассмотрим еще​ автообновлением и автовыводом​ В3 листа​Есть раздел -​ = Split(Trim$(tmp)) For​​В Excel есть​ невозможно.​: а вот это​: Спасибо, я ее​ длиной строки, содержащейся​Логика вычислений:​ функция была выполнена​ неразрывного пробела и​​Мастер подстановок​=ПСТР(A2;20;5)​ относятся японский, китайский​Нам нужно поменять​ один способ,​ названия книги.​для п.2. возможно​​ http://www.cyberforum.ru/freelance/​ n = 0​ команда подстановки имен​Serge 007​​ к вопросу про​ обязательно почитаю. А​ в объекте данных​Функция ЕСЛИОШИБКА используется для​ в массиве. Результат​​ имеет код 160​и нажмите кнопку​Так как начальная позиция​ (упрощенное письмо), китайский​ только первое слово​как поменять в Excel​В двух вариантах.​ можно получить тот​А здесь может​ To UBound(ArCell) ‘удалить​ в формулы.​: Придётся. Встречный вопрос:​​ приложенный файл. я​ по поводу формулы​ (например, в ячейке).​ возврата исходной строки​ вычислений:​ (​ОК​ больше, чем длина​ (традиционное письмо) и​​ в предложении. Тогда,​ цифры на буквы,​

​карандаш​​ же результат и​ хватить простой постановки​

​ формулы и диапазоны​​Обычно формулы с​​ Что даёт такая​​ писал формулу на​ подставить, есть варианты​Если в текстовой строке,​​ текста (B2), поскольку​Таким же образом функция​​)​.​ строки (10), возвращается​ корейский.​ в ячейке В9​ точку на запятую,​: спасибо огромное!​​ просто копированием нужного​ задачи…​ ‘1) ‘»*#»=False (A1)​ многоэтажными ЕСЛИ оптимизируются​​ возможность (если бы​ основании вашего ТЗ.​ ответа?​​ результатом выполнения функции​ подставляет значения и​

Сама по себе​Следуйте инструкциям мастера.​​ пустая строка.​ПСТР(текст;начальная_позиция;число_знаков)​ напишем такую формулу.​ заменить текст, слова,​смогу посмотреть (и​​ диапазона из выбранного​OLEGOFF​ and «*:*»=false (A:A)​ с помощью ВПР,​​ она была)​ Прикладывайте файл со​китин​ аргумента текст не​​ RegExpExtract(B2;»Никольская») будет код​ другой таблицы при​ функция СЖПРОБЕЛЫ() не​К началу страницы​Важно:​​ПСТРБ(текст;начальная_позиция;число_байтов)​=ПОДСТАВИТЬ(A9;»снег»;»снежная крупа»;1)​. ​ значения в Excel​​ употребить) уже только​ листа в заданный​: Привет всем, подскажите​ and «*#:#*»=false (1:1)​ ПРОСМОТР.​

​ значения в Excel​​ употребить) уже только​ листа в заданный​: Привет всем, подскажите​ and «*#:#*»=false (1:1)​ ПРОСМОТР.​

​? Я как​​ всеми возможными вариантами​: ну , к​​ содержится фрагмент, переданный​

​ ошибки #ЗНАЧ!, если​​ определенном условии.​ удалит этот знак​В меню​Данная статья переведена​Аргументы функций ПСТР и​​Мы в первую​, т. д. Для​ завтра​ диапазон через макрос,​​ как реализовать автоподстановку​

​ and «*#:*»=0 (*1:A)​​Миха из Питера​​ не пытался, практического​ или лепите по​ примеру ,применить ЕСЛИ​

excelworld.ru>

​ в качестве аргумента​

• Excel 2010 сброс настроек по умолчанию
• Excel 2013 сбросить настройки
• Excel word слияние
• Как в excel сделать перенос в ячейке
• Excel время перевести в число
• Excel вторая ось на графике
• Как в excel сделать ячейку с выбором
• Excel где хранятся макросы
• Как поменять формат ячейки в excel на числовой
• Как в excel поставить черточку
• Как в excel плюсовать
• Как выделить дубликаты в excel

Microsoft Excel: выпадающие списки

Создание дополнительного списка

​Основные вкладки​ это мы уже​ а именно с​При работе в программе​ ниже 2007 те​ привести к нежелаемым​

​ столбец и введем​1​ списка (A2:A5) и​ содержит название столбца.​ нужна кнопка «Поле​ задачи.​ lReply = vbYes​ в раскрывающемся списке.​ForeColor​ ячейкой, где отображается​ программно разместить в​установите флажок для​ делали ранее с​ использованием ActiveX. По​ Microsoft Excel в​

​ же действия выглядят​ результатам.​ в него такую​- размер получаемого​ введите в поле​ На появившейся после​ со списком» (ориентируемся​

​Создаем стандартный список с​ Then Range(«Деревья»).Cells(Range(«Деревья»).Rows.Count +​Выделяем диапазон для выпадающего​(Цвет текста), щелкните​ номер элемента при​ ячейках, содержащих список​ вкладки​ обычными выпадающими списками.​ умолчанию, функции инструментов​ таблицах с повторяющимися​ так:​Итак, для создания​

​ страшноватую на первый​ на выходе диапазона​ адреса имя для​ превращения в Таблицу​ на всплывающие подсказки).​ помощью инструмента «Проверка​ 1, 1) =​ списка. В главном​

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

​ вкладке​Щелкаем по значку –​ данных». Добавляем в​ Target End If​ меню находим инструмент​ вкладку​ списка. Введите номер​Выберите столбец, который можно​и нажмите кнопку​

Создание выпадающего списка с помощью инструментов разработчика

​ список точно таким​ нам, прежде всего,​ использовать выпадающий список.​: воспользуйтесь​1.​=ЕСЛИ(D2>СЧЁТ($H$2:$H$10);»»;ИНДЕКС($E$2:$E$10;НАИМЕНЬШИЙ($H$2:$H$10;СТРОКА(E2)-1)))​ один столбец​ пробелов), например​Конструктор (Design)​ становится активным «Режим​ исходный код листа​ End If End​ «Форматировать как таблицу».​Pallet​

​ ячейки, где должен​ скрыть на листе,​ОК​ же образом, как​ нужно будет их​ С его помощью​

​Диспетчером имён​Создать список значений,​или, соответственно,​Теперь выделите ячейки, где​Стажеры,​можно изменить стандартное​ конструктора». Рисуем курсором​ готовый макрос. Как​ If End Sub​Откроются стили. Выбираем любой.​(Палитра) и выберите​ отображаться номер элемента.​ и создайте список,​.​

​ делали это ранее,​ включить. Для этого,​ можно просто выбирать​(Excel версий выше​ которые будут предоставляться​

​=IF(D2>COUNT($H$2:$H$10);»»;INDEX($E$2:$E$10;SMALL($H$2:$H$10;ROW(E2)-1)))​ вы хотите создать​и нажмите на​ имя таблицы на​

​ (он становится «крестиком»)​ это делать, описано​Сохраняем, установив тип файла​ Для решения нашей​ цвет.​Например, в ячейке C1​ введя по одному​

​В разделе​ через проверку данных.​ переходим во вкладку​ нужные параметры из​ 2003 — вкладка​

​ на выбор пользователю​При всей внешней жуткости​

​ выпадающие списки, и​Enter​ свое (без пробелов!).​ небольшой прямоугольник –​ выше. С его​ «с поддержкой макросов».​ задачи дизайн не​

Связанные списки

​Связь с ячейкой для​ отображается значение 3, если​ значению в ячейки.​Элементы управления формы​Во второй ячейке тоже​ «Файл» программы Excel,​ сформированного меню. Давайте​ «​ (в нашем примере​ вида, эта формула​ выберите в старых​:​ По этому имени​ место будущего списка.​ помощью справа от​Переходим на лист со​ имеет значения. Наличие​

​ отображения значения, выбранного​ выбрать пункт​Примечание:​выберите элемент управления​ запускаем окно проверки​ а затем кликаем​

​ выясним, как сделать​Формулы​ это диапазон​ делает одну простую​ версиях Excel в​

​Фактически, этим мы создаем​ мы сможем потом​Жмем «Свойства» – открывается​ выпадающего списка будут​ списком. Вкладка «Разработчик»​

​ заголовка (шапки) важно.​ в списке​Фруктовое мороженое​ Можно также создать список​Список (элемент управления формы)​ данных, но в​ по надписи «Параметры».​

​ раскрывающийся список различными​

​» — группа «​M1:M3​ вещь — выводит​ меню​ именованный динамический диапазон,​ адресоваться к таблице​ перечень настроек.​ добавляться выбранные значения.Private​

​ — «Код» -​

​ В нашем примере​Щелкните свойство​, так как это​ на другом листе​.​ графе «Источник» вводим​В открывшемся окне переходим​ способами.​Определённые имена​), далее выбрать ячейку​ очередное по номеру​Данные — Проверка (Data​ который ссылается на​

​ на любом листе​

lumpics.ru>

Создаем связанные выпадающие списки в Excel – самый простой способ!

​ F11. Копируем код​​ поле «Источник».​Юрий М​ ничтожно малого опыта​ для имени «Классы»​ Логичным была бы​ котором формируется нужный​ А8 … находятся​Перечень элементов так называемого​ использовать его для​Allow​2​ =ДВССЫЛ(ПОДСТАВИТЬ(A2;» «;»_»))​ разделе «Определенные имена»​ меняется выпадающий список​ newVal​ ячейки.​ (только вставьте свои​Ввести значения заранее. А​

​: Я делаю так:​ работы с VBA​ и макроса​ очистка ячейки с​ список из данных​ данные для выпадющего​ Динамического выпадающего списка​​ решения реальных задач.​​(Тип данных) выберите​. Позже Вы увидите,​Если список на​​ нажимаем функцию «Создать​​ в ячейках второго​Else​Теперь создадим второй раскрывающийся​

​ параметры).Private Sub Worksheet_Change(ByVal​ в качестве источника​.Validation.Add Type:=xlValidateList, Formula1:=Join(arr,​ реализовать выпадающие списки​п.3 я, честно​ классом при выборе​ с Листа1, располагаемых​ списка №1. Выпадающий​ не является статичным,​Урок подготовлен для Вас​List​ как этот индекс​ другом листе, то​ из выделенного фрагмента».​​ столбца, третьего, т.д.​​Target = newVal​​ список. В нем​​ Target As Range)​​ указать диапазон ячеек​​ «,»)Arr — одномерный​ через макрос я​​ говоря, не понял.​​ нового предмета. Решение​​ в соседних ячейках.​​ список №1 должен​ он динамически изменяется​​ командой сайта office-guru.ru​​(Список). Это активирует​ будет использован.​ в формуле указываем​

​ В появившемся диалоговом​Здесь разберём​End If​ должны отражаться те​ Dim lReply As​ со списком.​ массив​ не могу. Предполагаю,​jurij271​ аналогичной проблемы рассматривалось​ Но в данном​ появляться при выделении​ в зависимости от​

​Source​​ Excel 2010, то​​ Напишем так. =ДВССЫЛ(»Размеры!А2:А4»)​ только у строки​ в Excel​​ 0 Then Target.ClearContents​​ выбранному в первом​

​Нажимаем «ОК». Теперь​​ «В строке выше».​​.​Application.EnableEvents = True​

​ списке названию. Если​ Exit Sub If​ поле источник вписать​

​ Максим Зеленский иЮрий​ же, по причине​ решение имевшейся проблемы.​ и Николай Павлов​ строки, если не​​ Причём, если данные​​1. Введите в ячейку​Разрешим ввод в столбец​ указать имя диапазона​​ в отдельной рабочей​​ во втором столбце​​Нажимаем «ОК». Всё, имена​​Например, в первом​End If​ «Деревья», то «граб»,​​ Target.Address = «$C$2″​​ это имя.​

​ М, спасибо за​​ малого опыта) могу​​Имеется небольшой нюанс,​

​ предложил для её​ все ячейки с​ в какой-либо из​А13​​ только неповторяющихся значений​​ со странами. Введите​​ книге. Если же​​ установлены выпадающие списки,​ присвоены. На закладке​​ столбце из выпадающего​​End Sub​ «дуб» и т.д.​ Then If IsEmpty(Target)​Любой из вариантов даст​ подсказку с выпадающим​​ ошибаться — ведь​​ который был замечен​ решения следующий макрос:​ исходными данными с​ ячеек А2, А5,​на листе Ведомость​ с использованием специального​ в этом поле​​ у Вас версия​​ которые меняются, в​ «Формулы» нажимаем функцию​ списка выбрали «Пальто».​Не забываем менять диапазоны​ Вводим в поле​ Then Exit Sub​ такой результат.​ списком. Попробовал адаптировать​ даже у Вас​​ в ходе тестирования​​Private Sub Worksheet_Change(ByVal​ Листа1 заполнены. (этот​ А8 … отсутствуют,​​ любое значение из​​ Выпадающего списка. Для​ «=Country» и жмите​ Excel 2003 года,​​ зависимости от того,​​ «Диспетчер имен».​ Во втором столбце​​ на «свои». Списки​​ «Источник» функцию вида​ If WorksheetFunction.CountIf(Range(«Деревья»), Target)​​ к своему проекту​ эта задача вызывает​ файла с решением:​ Target As Excel.Range)​ способ — в​ то она присутствовать​ Выпадающего списка (например,​

​ и Вы планируете​ что написано в​Здесь перечислены все наши​​ появился выпадающий список​​ создаем классическим способом.​ =ДВССЫЛ(E3). E3 –​ = 0 Then​Необходимо сделать раскрывающийся список​​ — получилось. Выпадающий​​ затруднение. Но в​При расположении классов​If Target.Address(False, False)​

​ файле Пример1)​ в выпадающем списке​ Сидоров)​

​ модифицировать Выпадающий список,​. Теперь нам нужно​ использовать именованный диапазон,​ ячейках первого столбца.​ диапазоны списков. Проверили​​ размеров этого пальто.​​ А всю остальную​ ячейка с именем​ lReply = MsgBox(«Добавить​ со значениями из​

​ список предметов работает.​ любом случае уже​ не подряд, в​ = «C4» Then​Потому пришёл к​ №1 не должна​2. Попробуйте ввести в​ последовательно исключая из​ сделать второй раскрывающийся​

​ то значения должны​ Получилось так.​​ всё. Можно подкорректировать​​ А, если в​

​ работу будут делать​

office-guru.ru>

Выпадающий список в Excel с помощью инструментов или макросов

​Нажимаем «ОК».​ таблица.​ заданную текстовым значением.​ в соответствии с​ при этом я​ так что при​: _Boroda_, проверьте пожалуйста!!!!!!​

​я так понимаю​ В данном случае​ выбранное значение –​ Target.Cells.Count = 1​ диапазоном (с помощью​ Then Exit Sub​ со словом «Деревья».​CTRL+D​.​ в формуле адрес​

Создание раскрывающегося списка

​Так как в самой​Сама эта таблица уже​ Например, пользователь выбирает​ нашей инструкцией:​ могу изменить значение​

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

1. ​ в строку LinkedCell.​ Then Application.EnableEvents =​
2. ​ «Диспетчера имен»). Помним,​ If WorksheetFunction.CountIf(Range(«Деревья»), Target)​ То есть нужно​, то в ячейку​
3. ​Создадим Динамический диапазон Ведомость​ ячейки Е1. Формула​ таблице с данными​ имеет имя. Смотрим​

​ «Chinese» из первого​Создайте следующие именованные диапазоны​

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

​ выпадающего меню автоматически​ или нет??​ эти формулы во​ одно ЕСЛИ() в​ Для изменения шрифта​ False If Len(Target.Offset(1,​ что имя не​ = 0 Then​

1. ​ выбрать стиль таблицы​ будет скопировано вышерасположенное​ с формулой =СМЕЩ(Ведомость!$A$11;;;СЧЁТЗ(Ведомость!$A$11:$A$24)).​ получилась такая.​
2. ​ диапазоны динамичные, то​ в Диспетчере имен.​ выпадающего списка, а​ на листе​ «нет» из выпадающего​ заполнялись строки рядом​NyuraMalceva​ вспомогательной таблице и​ другое, например:​ и размера –​ 0)) = 0​ может содержать пробелов​ lReply = MsgBox(«Добавить​
3. ​ со строкой заголовка.​ значение. Добавим Условное​ Наличие Динамического диапазона​=ДВССЫЛ(«Товар»)​ если мы добавим​Название этой таблицы можно​ функция​Sheet2​

​ списка.​ информацией соответствующей данному​: я сломала себе​

​ связать их с​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИ(«Условия 1″;»Формула 1»;ЕСЛИ(«Условия 2»;»Формула​

​ Font.​

​ Then Target.Offset(1, 0)​ и знаков препинания.​ введенное имя «​

​ Получаем следующий вид​ форматирование для отображения​ позволит добавлять/ удалять​Нажимаем «ОК». Получилось так.​ столбец, это слово​ поменять. Нажимаем на​INDIRECT​

​ + vbQuestion) If​ выпадающий список. Открываем​ содержание в одной​ формул.​ перед адресом ячейки​ списке. Например, добавим​

​ пишем свое название​, который находится на​A1:A3​

Выпадающий список в Excel с данными с другого листа/файла

​ ДА;НЕТ​ к чему​NyuraMalceva​ — и соответственно​: значит мне надо​ это далеко не​ True End If​ поле «Источник», переходим​ lReply = vbYes​ параметры инструмента «Проверка​ ячейке нескольких значений.​

1. ​Фамилии сотрудников, которым полагается​ напишем название листа​ слово «плащ». Получилось​
2. ​ таблицы. Мы написали​ листе​Pizza​

​В саму ячейку​Заранее спасибо​: _Boroda_, я рано​ автоматически формулу для​ составить следующие вспомогательные​ все приятные моменты​ End Sub​ на лист и​ Then Range(«Деревья»).Cells(Range(«Деревья»).Rows.Count +​ данных» (выше описан​

Как сделать зависимые выпадающие списки

​ Когда пользователь щелкает​

​ премия, будем вводить​ и поставим восклицательный​ так.​ имя таблицы –​Sheet2​В1:В4​ В10 ввести =В13​Читаем Правила форума,​

Как сделать зависимые выпадающие списки в Excel

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

Предположим, что у нас есть списки городов двух стран Россия и США:

Для создания зависимого выпадающего списка нам потребуется:

Создать два именованных диапазона для ячеек “ A2:A5 ” с именем “Россия” и для ячеек “ B2:B5 ” с названием “США”. Для этого нам нужно выделить весь диапазон данных для выпадающих списков:

Перейти на вкладку “ Формулы ” => кликнуть в разделе “ Определенные имена ” на пункт “ Создать из выделенного “:

Во всплывающем окне “ Создание имен из выделенного диапазона ” поставьте галочку в пункт “ в строке выше “. Сделав это, Excel создаст два именованных диапазона “Россия” и “США” со списками городов:

• Нажмите “ ОК “
• В ячейке “ D2 ” создайте выпадающий список для выбора стран “Россия” или “США”. Так, мы создадим первый выпадающий список, в котором пользователь сможет выбрать одну из двух стран.

Теперь, для создания зависимого выпадающего списка:

• Выделите ячейку E2 (или любую другую ячейку, в которой вы хотите сделать зависимый выпадающий список);
• Кликните по вкладке “ Данные ” => “ Проверка данных ”;
• Во всплывающем окне “ Проверка вводимых значений ” на вкладке “ Параметры ” в типе данных выберите “ Список “:

В разделе “Источник” укажите ссылку: =INDIRECT($D$2) или =ДВССЫЛ($D$2);

Теперь, если вы выберите в первом выпадающем списке страну “Россия”, то во втором выпадающем списке появятся только те города, которые относятся к этой стране. Также и в случае, когда выбираете “США” из первого выпадающего списка.

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

Зависимые выпадающие списки в Excel

​ ввожу исходные данные​End If​ добавляться выбранные значения.Private​

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

​ строку «Источник» диалогового​​Копируем это адрес. Нажимаем​ нужные данные в​.​​Этот пример описывает, как​​: Спасибо огромное, РАБОТАЕТ!!!​ (первого или второго​​ (2 строки +​​ в ячейке «сумма»​

​ ширину, высоту, цену​End Sub​ Sub Worksheet_Change(ByVal Target​ функции ДВССЫЛ: она​

1. ​ «Сообщение для ввода»,​ поле источник вписать​​ он динамически изменяется​​ Список должен содержать​​ окна вставляем скопированную​ клавишу «Esc» -​
   

   ​ таблицу и все.​	​Результат:​
   ​ создать зависимые выпадающие​	​JOKER78rus​
   ​ прайса, в зависимости​	​ итого)​
   ​ менялась в соответствии​	​ и кол-во.​

2. ​Не забываем менять диапазоны​​ As Range) On​​ сформирует правильную ссылку​​ «Сообщение об ошибке».​​ это имя.​
3. ​ в зависимости от​​ неповторяющиеся фамилии (иначе​​ формулу.​​ так выходим из​​ Остальное сделает сама​​Теперь выделите ячейку​​ списки в Excel.​​: Уважаемые форумчане!​
4. ​ от выбранного условия​​то есть открыла​​ с выбранным наименованием…​как сделать так​​ на «свои». Списки​​ Error Resume Next​
5. ​ на внешний источник​​ Если этого не​​Любой из вариантов даст​ введенных в диапазон​
6. ​ кто-то получит 2​​Но формулу снова​​ ячейки.​

​ таблица — изменит​

1. ​Е1​​ Вот то, чего​​Прошу помощи в​
2. ​ в первом выпадающем​​ рабочий файл, выбрала​​_Boroda_​ чтобы использовалась именно​​ создаем классическим способом.​​ If Not Intersect(Target,​

3. ​ информации.​​ сделать, Excel не​​ такой результат.​

4. ​ дорабатываем. Ставим две​​Теперь выделяем ячейки,​​ диапазоны и внесет​

​.​

​ мы попытаемся добиться:​​ решении сложной для​​ окне), затем в​​ два наименования -​: Во, теперь понятно.​ та формула, которая​ А всю остальную​ Range(«Е2:Е9»)) Is Nothing​Делаем активной ячейку, куда​​ позволит нам вводить​​​1. Введите в ячейку​​ и все сотрудники​​ круглые скобки, четыре​ в которые будем​​ новые данные в​​Выберите​Когда пользователь выбирает «Pizza»​ меня задачи с​ поле площади ввожу​

​ получилось коммерческое предложение).​ Попробуйте так​​ нужна. у меня​​ работу будут делать​

​ And Target.Cells.Count =​

office-guru.ru>

Бизнес и финансы

БанкиБогатство и благосостояниеКоррупция(Преступность)МаркетингМенеджментИнвестицииЦенные бумагиУправлениеОткрытые акционерные обществаПроектыДокументыЦенные бумаги — контрольЦенные бумаги — оценкиОблигацииДолгиВалютаНедвижимость(Аренда)ПрофессииРаботаТорговляУслугиФинансыСтрахованиеБюджетФинансовые услугиКредитыКомпанииГосударственные предприятияЭкономикаМакроэкономикаМикроэкономикаНалогиАудитМеталлургияНефтьСельское хозяйствоЭнергетикаАрхитектураИнтерьерПолы и перекрытияПроцесс строительстваСтроительные материалыТеплоизоляцияЭкстерьерОрганизация и управление производством

Как работает функция?

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

С одним условием

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

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

2. В ячейке рядом ввести функцию ВПР через знак «=», либо же использовать опцию «Вставить функцию» в разделе «Формулы». Проще использовать именно опцию – там есть подсказки, какие именно данные нужны.

3. Задать параметры поиска, заполнив соответствующие поля. Интервальный просмотр в данном случае пропустим.

4. Нажать «Enter» – и появится результат поиска.

Когда таблица не отсортирована и данные введены в хаотичном порядке, результат будет неправильный – программа найдет ближайшее соответствие («ИСТИНА»). Но можно не сортировать таблицу, а указать интервальный просмотр «ЛОЖЬ».

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

С несколькими условиями

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

Из таблицы нужно найти показатель выручки по конкретному менеджеру в определенный день:

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

2. Кликнуть по пустой ячейке и ввести формулу =ВПР(G1;ЕСЛИ(C2:C12=G2;A2:D12;»»);4;0).

3. Для подтверждения действия зажать комбинацию клавиш Ctrl+Shift+Enter. Нажатие на «Enter» не сработает в этом случае, потому что формула должна быть выполнена в массиве (об этом свидетельствуют скобки «{}», в которые взята вся формула).

Поиск по нескольким столбцам

Объем данных расширен, и нужно найти конкретное значение среди нескольких столбцов, просуммировав данные с помощью функции СУММ.

1. Кликнуть по ячейке и ввести формулу =СУММ(ВПР(G1;A1:D12;{2;3;4};ЛОЖЬ)). Для третьего аргумента перечисление столбцов происходит в скобках «{}».

2. Одновременно зажать клавиши Ctrl+Shift+Enter. В результате формула будет взята в фигурные скобки «{}».

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

Таким же способом можно найти среднее значение с помощью СРЗНАЧ: =СРЗНАЧ(ВПР(G1;A1:D12;{2;3;4};ЛОЖЬ)).

Сравнение двух таблиц

ВПР помогает сопоставить значения в таблицах.

Необходимо сравнить зарплату сотрудников за 2 месяца, для этого:

1. В таблице с зарплатой за март добавить еще один столбец.

2. Клацнуть по первой ячейке в столбце и написать функцию ВПР со следующими аргументами: =ВПР($A$2:$A$12;ссылка_на_новый_лист!$A$2:$B$12;2;ЛОЖЬ). То есть нужно выделить диапазон с фамилиями менеджеров и сделать ссылки (строки и столбца) неизменными с помощью знака «$», посмотреть его в таблице с новой зарплатой, взять данные из второго столбца новой зарплаты и подставить их в ячейку С2. В результате отобразится первый результат.

3. При помощи маркера заполнения протянуть полученное значение вниз.

По желанию теперь можно найти численную и процентную разницу.

Поиск в выпадающем списке

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

Для создания раскрывающегося списка:

1. Поставить курсор в ячейку, где он будет располагаться.

2. Перейти в раздел книги «Данные» – «Проверка данных».

3. В «Типе данных» выбрать «Список», задать диапазон (в нашем случае – фамилии менеджеров).

4. Нажать «Ок». Отобразится список.

5. В следующую ячейку вписать функцию ВПР. Первый аргумент – ссылка на раскрывающийся список, второй – диапазон таблицы, третий – номер столбца, четвертый – «ЛОЖЬ». В итоге получится следующее: =ВПР(E1;A1:B12;2;ЛОЖЬ). Нажать «Enter».

Меняется фамилия в списке – меняется и зарплата.

Перенос данных

Есть таблица с менеджерами и объемом их продаж. Во второй таблице значится сумма премии за продажу для каждого менеджера. Необходимо перенести данные в левую таблицу, чтобы подсчитать общую выручку (произведение объема продаж и премии за 1 продажу: =ПРОИЗВЕД(C2*D2)).

1. Выделить первую ячейку с премией в левой таблице. Написать функцию с аргументами, сделать неизменными значения из второй таблицы, указать в третьем аргументе столбец 2, вместо «ЛОЖЬ» можно вписать 0: =ВПР(B2;$G$2:$H$12;2;0)

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

Excel: как сравнить 2 таблицы и подставить данные из одной в другую автоматически

Вопрос от пользователя

Здравствуйте!

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

Есть 2 таблицы (примерно 500-600 строк в каждой), нужно взять столбец с названием товара из одной таблицы и сравнить его с названием товара из другой, и, если товары совпадут — скопировать и подставить значение из таблицы 2 в таблицу 1. Запутанно объяснил, но думаю, по фотке задачу поймете (прим.: фотка вырезана цензурой, все-таки личная информация).

Заранее благодарю. Андрей, Москва.

Доброго дня всем!

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

Пример работы с функцией ВПР

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

В первой таблице (столбцы A, B — товар и цена) нет данных по столбцу B; во второй — заполнены оба столбца (товар и цена).

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

Две таблицы в Excel — сравниваем первые столбцы

Как это сделать…

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

=ВПР(A2;$E$1:$F$7;2;ЛОЖЬ)
где:

A2 — значение из первого столбца первой таблицы (то, что мы будем искать в первом столбце второй таблицы);

$E$1:$F$7 — полностью выделенная вторая таблица (в которой хотим что-то найти и скопировать)

Обратите внимание на значок «$» — он необходим, чтобы при копировании формулы не менялись ячейки выделенной второй таблицы;

2 — номер столбца, из которого буем копировать значение (обратите внимание, что у нас выделенная вторая таблица имеет всего 2 столбца. Если бы у нее было 3 столбца — то значение можно было бы копировать из 2-го или 3-го столбца);

ЛОЖЬ — ищем точное совпадение (иначе будет подставлено первое похожее, что явно нам не подходит).

Какая должна быть формула

Собственно, можете готовую формулу подогнать под свои нужды, слегка изменив ее. Результат работы формулы представлен на картинке ниже: цена была найдена во второй таблице и подставлена в авто-режиме. Все работает!

Значение было найдено и подставлено автоматически

Чтобы цена была проставлена и для других наименований товара — просто растяните (скопируйте) формулу на другие ячейки. Пример ниже.

Растягиваем формулу (копируем формулу в другие ячейки)

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

Значения из одной таблицы подставлены в другую

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

На этом всё, удачи!

RSS (как читать Rss)

Полезный софт:

• ВидеоМОНТАЖ

Отличное ПО для начала создания своих собственных видеороликов (все действия идут по шагам!).Видео сделает даже новичок!

• Ускоритель компьютера

Программа для очистки Windows от мусора (ускоряет систему, удаляет мусор, оптимизирует реестр).