|
7 / 7 / 1 Регистрация: 02.03.2011 Сообщений: 307 |
||||||||||||||||||||||||||||||||||||||||||||||||
|
1 |
||||||||||||||||||||||||||||||||||||||||||||||||
Добавление строк по условию27.06.2021, 14:05. Показов 4811. Ответов 11
Приветствую!
↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓
0 |
|
Programming Эксперт 94731 / 64177 / 26122 Регистрация: 12.04.2006 Сообщений: 116,782 |
27.06.2021, 14:05 |
|
11 |
|
1232 / 670 / 238 Регистрация: 22.12.2015 Сообщений: 2,089 |
|
|
27.06.2021, 14:33 |
2 |
|
xxxspeed, отфильтруйте фильтром по строке «кол-во» и скопируйте отфильтрованные значения на новый лист путем обычного выделить-копировать-вставить.
0 |
|
7 / 7 / 1 Регистрация: 02.03.2011 Сообщений: 307 |
|
|
27.06.2021, 14:35 [ТС] |
3 |
|
Это понятно, мне просто нужно автоматом, что б все делалось. Я написал формулы, чтоб на втором листе данные сразу отображались, но они отображаются в тех же строках.
0 |
|
Dinoxromniy 1232 / 670 / 238 Регистрация: 22.12.2015 Сообщений: 2,089 |
||||
|
27.06.2021, 14:46 |
4 |
|||
|
Либо если есть вариант с авто сортировкой, при изменении таблица автоматически заново сортируется. Сложно сказать — кроме вас вашу таблицу никто видел.
Только если вы вручную вбиваете новые данные — начинайте с 4 столбца — который «количество», иначе таблица скроет новую строчку раньше, чем вы успеете ввести все ячейки строки. С копированием проблем быть не должно.
1 |
|
7 / 7 / 1 Регистрация: 02.03.2011 Сообщений: 307 |
|
|
27.06.2021, 15:48 [ТС] |
5 |
|
Сложно сказать — кроме вас вашу таблицу никто видел. Краткий фрагмент таблицы я опубликовал. С листа прайс на второй лист данные я переношу формулой =ЕСЛИ(Прайс!$D2=»»;»»;Прайс!A2). Код работает, я его добавил на второй лист. Единственное, нужно на втором листе сделать любое изменение, чтоб код сработал. Добавлено через 12 минут
0 |
|
5942 / 3154 / 698 Регистрация: 23.11.2010 Сообщений: 10,524 |
|
|
27.06.2021, 16:11 |
6 |
|
Краткий фрагмент таблицы я опубликовал это картинка, тут даже названий столбцов не видно, это к тому
при изменении колонки D
0 |
|
7 / 7 / 1 Регистрация: 02.03.2011 Сообщений: 307 |
|
|
27.06.2021, 16:23 [ТС] |
7 |
|
это картинка, тут даже названий столбцов не видно, это к тому Верно подмечено, извиняюсь.
0 |
|
7 / 7 / 1 Регистрация: 02.03.2011 Сообщений: 307 |
|
|
27.06.2021, 16:27 [ТС] |
8 |
|
Вот файл, но без кода
0 |
|
7 / 7 / 1 Регистрация: 02.03.2011 Сообщений: 307 |
|
|
27.06.2021, 16:31 [ТС] |
9 |
|
Вот файл.
0 |
|
xxxspeed 7 / 7 / 1 Регистрация: 02.03.2011 Сообщений: 307 |
||||
|
27.06.2021, 16:53 [ТС] |
10 |
|||
|
Вопрос вроде решился, разобрался.
0 |
|
1232 / 670 / 238 Регистрация: 22.12.2015 Сообщений: 2,089 |
|
|
29.06.2021, 10:08 |
11 |
|
xxxspeed, прошу прощения — руки не дошли написать.
0 |
|
xxxspeed 7 / 7 / 1 Регистрация: 02.03.2011 Сообщений: 307 |
||||||||||||
|
29.06.2021, 21:59 [ТС] |
12 |
|||||||||||
|
Ваше решение оптимально Вот еще решение которое мне подсказали
Подскажите только как изменить путь к листу в
,
чтоб копировал данные в другой файл. Но в том решении есть свой плюс. Остаются рамки в таблице
0 |
|
Добавление строк в зависимости от условия |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
Предположим, у вас есть диапазон данных, и вы хотите автоматически вставлять пустые строки выше или ниже определенного значения в Excel, например, автоматически вставлять строки ниже нулевого значения, как показано ниже. В Excel нет прямого способа решить эту задачу, но я могу ввести код макроса для автоматической вставки строк на основе определенного значения в Excel.
Вставить строку ниже на основе значения ячейки с помощью VBA
Вставьте строку выше на основе значения ячейки с помощью Kutools for Excel 
Чтобы вставить строку на основе значения ячейки, запустив VBA, выполните следующие действия:
1. Нажмите Alt + F11 одновременно, а Microsoft Visual Basic для приложений окно выскакивает.
2. Нажмите Вставить > Модули, затем вставьте ниже код VBA во всплывающий Модули окно.
VBA: вставьте строку ниже на основе значения ячейки.
Sub BlankLine()
'Updateby20150203
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type: = 8)
Set WorkRng = WorkRng.Columns(1)
xLastRow = WorkRng.Rows.Count
Application.ScreenUpdating = False
For xRowIndex = xLastRow To 1 Step - 1
Set Rng = WorkRng.Range("A" & xRowIndex)
If Rng.Value = "0" Then
Rng.Offset(1, 0).EntireRow.Insert Shift: = xlDown
End If
Next
Application.ScreenUpdating = True
End Sub
3. Нажмите F5 ключа или Run нажмите кнопку, появится диалоговое окно и выберите столбец, содержащий ноль. Смотрите скриншот:
4. Нажмите OK. Тогда пустые строки будут вставлены ниже нулевого значения.
Функции:
1. Если вы хотите вставить строки на основе другого значения, вы можете изменить 0 на любое значение, которое вы хотите в VBA: Если Rng.Value = «0», то.
2. Если вы хотите вставить строки выше нуля или другого значения, вы можете использовать приведенный ниже код vba.
VBA: вставить строку выше нулевого значения:
Sub BlankLine()
'Updateby20150203
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type: = 8)
Set WorkRng = WorkRng.Columns(1)
xLastRow = WorkRng.Rows.Count
Application.ScreenUpdating = False
For xRowIndex = xLastRow To 1 Step - 1
Set Rng = WorkRng.Range("A" & xRowIndex)
If Rng.Value = "0" Then
Rng.EntireRow.Insert Shift: = xlDown
End If
Next
Application.ScreenUpdating = True
End Sub
Если вы не знакомы с VBA, вы можете попробовать Kutools for Excel‘s Выбрать определенные ячейки утилита, а затем вставьте строки выше.
После установки Kutools for Excel, пожалуйста, сделайте следующее:(Бесплатная загрузка Kutools for Excel Сейчас!)
1. Выберите список, из которого вы хотите найти определенные ячейки, и нажмите Кутулс > Выберите > Выбрать определенные ячейки. Смотрите скриншот:
2. В появившемся диалоговом окне отметьте Весь ряд вариант, а затем перейдите, чтобы выбрать Равно от Конкретный тип список, а затем введите значение, которое вы хотите найти, в правом текстовом поле. Смотрите скриншот:
3. Нажмите Ok, и появится диалоговое окно, напоминающее количество выбранных строк, просто закройте его.
4. Поместите курсор в одну выбранную строку и щелкните правой кнопкой мыши, чтобы выбрать Вставить из контекстного меню. Смотрите скриншот:
Теперь строки вставляются выше на основе определенного значения.
Относительные статьи:
- Вставить пустые строки в Excel
Лучшие инструменты для работы в офисе
Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%
- Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма …
- Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон…
- Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы… Предотвращение дублирования ячеек; Сравнить диапазоны…
- Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор …
- Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое …
- Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии…
- Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом …
- Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF…
- Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.
Вкладка Office: интерфейс с вкладками в Office и упрощение работы
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
Комментарии (43)
Оценок пока нет. Оцените первым!
Связанный список в MS EXCEL
Смотрите также введем в него более подробно?
стал выпадать список чуть ли не=СМЕЩ(начальная_ячейка; сдвиг_вниз; сдвиг_вправо; размер_диапазона_в_строках;Этот способ требует наличия и в имени комбинацией клавиш CtrlТеперь рассмотрим, как связать — это «подподкатегория» него можно создать
ВПР(Диспетчер имён). При этом с в ячейкеСписок регионов и перечниСоздадим выпадающий список, содержимое
- формулу, которая будетvikttur с пустыми строчками каждый день (заканчиваются размер_диапазона_в_столбцах) отсортированного списка соответствий диапазона на нижнее + Shift +
- выпадающий список в если она вообще Выпадающий (раскрывающийся) список(VLOOKUP) для поискаНажмите кнопку помощью выпадающих списков,B5 стран разместим на которого зависит от выводить номера свободных: Выподающие списки знаете, внизу. Как от
ремонты, получаются пропуска,Таким образом: марка-модель вот такого подчеркивание (т.е. Ssang_Yong). Enter !!! Excel. Поскольку Модель
существует… Но для (см. Файл примера). значения из ячейкиNew необходимо ограничить доступные
и балдеем – листе значений другой ячейки. сотрудников: сами делали. этого можно избавиться? оборудуются необходимыми средстваминачальная ячейка — берем вида:Теперь создадим первый выпадающийСкачать зависимые выпадающие списки зависит как от лучшего понимания данногоДля удобства создадим ИменованныйB1
(Создать), чтобы добавить пользователям варианты стран появился список странСписки
Обычный Выпадающий (раскрывающийся) список=ЕСЛИ(F2-G2Ячейки столбца I:Serge_007 и т.д.) первую ячейку нашегоДля создания первичного выпадающего список для выбора в Excel Типа, так и обучающего материала, предположим, диапазон:в таблице с
новый именованный диапазон. и городов, из для Региона Америка:. отображает только одинТеперь надо сформировать непрерывный меню Формат-Условное форматирование.
: Пустая строка, которую- на втором списка, т.е. А1 списка можно марок марки автомобиля. Выделите
Не нажмите OK случайно. от Производителя - что существует.создайте список фамилий сотрудников, названиями стран. После Откроется диалоговое окно которых они могут США, Мексика…Обратите внимание, что названия перечень элементов. Связанный (без пустых ячеек)
Имена: меню Вставка-Имя-присвоить. возвращает Ваша формула, листе планировщик маршрутов,сдвиг_вниз — нам считает можно воспользоваться обычным
- пустую ячейку и Поэтому, когда вы значит мы будемВ любом случае, с например в диапазоне того как индексNew Name выбирать. В первой
- Теперь заполняем следующую строку. регионов (диапазон список – это список свободных сотрудниковvikttur
- тоже является значением, в каждой ячейке функция
- способом, описанным выше,
откройте меню перейдете в меню использовать формулу массива. самого начала напишем,D1:D10 будет известен, мы
(Создание имени). ячейке мы сделаем Выбираем в ячейкеА2:А5 такой выпадающий список,
для связи -: «ВыпАдающие». поэтому надо их которой необходим выпадающийПОИСКПОЗ (MATCH) т.е.
- Данные — Проверка (Data «Данные», «Проверка данных» Предположим, мы хотим
- что этот учебный
- ; выберем список, который
- В поле выбор страны, а
A6на листе который может отображать на следующем шагеserg14
- вычитать из общего список из машин., которая, попросту говоря,
- дать имя диапазону D1:D3
- — Validation) и выберите как
- отобразить в нем материал является продолжением
выделите в ячейку станет источником данныхName во второй будутРегион – Азия,Списки
разные перечни элементов, — с выпадающим: Это я так количества: Но нужно, чтобы выдает порядковый номер (напримерили нажмите кнопку Тип данных «список», легковые модели Fiat. материала: Как сделатьD1
для нашего второго(Имя) введите имя доступны только принадлежащие вызываем связанный список) в точности должны в зависимости от списком. Для этого понимаю для Excel200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СМЕЩ(Транспорт!$G$3;0;0;СЧЁТЗ(Транспорт!$G$4:$G$11)-СУММ(—(Транспорт!$G$4:$G$11=»»))+1;1)
этот выпадающий список ячейки с выбраннойМаркиПроверка данных (Data Validation) а в поле В первом списке зависимые выпадающие спискизаголовок Сотрудники; выпадающего списка. ДляCountry выбранной стране города. в ячейке совпадать с заголовками значения другой ячейки. добавим еще один 2003, а вgling состоял только из маркой (G7) в) с помощьюна вкладке «Источник» вставьте эту мы выбрали Легковой, в ячейках Excel,выделите диапазон этого напишем такуюдля нашего первого Думаю, это понятно?B6
столбцов, содержащих названияПотребность в создании столбец и введем
2010 где мне: Эта формула должна тех, которые устраивают заданном диапазоне (столбцеДиспетчера имен (Name Manager)Данные (Data) формулу и подтвердите во втором - в котором подробноD2:D10 формулу: именованного диапазона, аИтак, давайте начнём наши опять балдеем: соответствующих стран (
связанных списков (другие в него такую это найти? подойти всем условиям. В А)с вкладкиесли у вас
ее с помощью Fiat. описали логику и, в поле Имя,=CHOOSE(VLOOKUP(B1,Sheet3!$A$3:$B$5,2,FALSE),England,France,Portugal)
в поле простой пример с Китай, Индия…В1:Е1 названия: связанные диапазоны, страшноватую на первыйZ
excel2.ru
Создаем связанные выпадающие списки в Excel – самый простой способ!
200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СМЕЩ(Транспорт!$G$3;0;0;СЧЁТЕСЛИ(Транспорт!$G$4:$G$11;»>»»»)+1;1) моём примере: толькосдвиг_вправо = 1, т.к.Формулы (Formulas) Excel 2007 или Ctrl + ShiftМы будем перемещать ячейку способ создания одного слева от Строки=ВЫБОР(ВПР(B1;Sheet3!$A$3:$B$5;2;ЛОЖЬ);England;France;Portugal)Refers to того, как можноНеобходимо помнить, что в). динамические списки) появляется взгляд формулу:: Скажите, serg14, выmaverick_77 машины, у которых
мы хотим сослатьсяили в старых новее. Затем из + Enter. Вот H4 на столько из таких списков. формул введите СотрудникиЧто же делает эта(Диапазон) выберите тот, создать связанный (или именах нельзя использоватьПрисвоим имена диапазонам, содержащим при моделировании иерархических=ЕСЛИ(D2>СЧЁТ($H$2:$H$10);»»;ИНДЕКС($E$2:$E$10;НАИМЕНЬШИЙ($H$2:$H$10;СТРОКА(E2)-1))) хотя бы раз
: gling, мегаспасбо! =) по «условию 1″ на модели в версиях Excel - выпадающего списка и вся магия. строк, пока не Рекомендуем вам ознакомиться и нажмите формула? Она ищет в котором хранится зависимый) выпадающий список символ пробела. Поэтому, Регионы и Страны структур данных. Например:или, соответственно, пролистали риббоны (окна)Где можно почитать — «готов», а соседнем столбце (В) через менюТип данных (Allow)Этот фокус основан на найдем позицию первого с ним, потомуENTER значение из ячейки список стран: в Excel? В при создании имен,
(т.е. создадим ИменованныеОтдел – Сотрудники отдела.=IF(D2>COUNT($H$2:$H$10);»»;INDEX($E$2:$E$10;SMALL($H$2:$H$10;ROW(E2)-1))) ленты Xl’я 2010, подробности о том, по «условию 2″размер_диапазона_в_строках — вычисляем сВставка — Имя -выберите вариант применении функции легкового Fiatа. Поэтому что здесь подробно, либо, выделив диапазон
B1=Sheet3!$A$3:$A$5 ячейке вышеуказанным способом, он диапазоны). Быстрее всего При выборе отделаПри всей внешней жуткости как горорят, от как организуются динамические — «есть» помощью функции Присвоить (Insert -Список (List)ДВССЫЛ (INDIRECT) в колонке Тип описывается только то,D1:D10в списке стран
НажмитеB1 будет автоматически заменен это сделать так: из списка всех вида, эта формула «А» до «Я»?..
диапазоны?PS: сори, еслиСЧЕТЕСЛИ (COUNTIF) Name — Define)и в поле, которая умеет делать мы должны иметь как сделать тотчерез команду меню и возвращает соответствующийОК
мы будем выбирать
на нижнее подчеркиваниевыделитьячейки отделов компании, динамически делает одну простую
Поинтересовались в «Приемах»Serge_007, на реальном такая задача уже
, которая умеет подсчитатьвыбрать на вкладкеИсточник (Source) одну простую вещь значение Легковой, а другой связанный выпадающий Создать из выделенного индекс, который затем, чтобы сохранить и страну, а в «_». Например, еслиА1:Е6 формируется список, содержащий вещь — выводит (или на офсайте) массиве не стало ставилась. Но я
количество встретившихся вДанные (Data)выделите ячейки с
— преобразовывать содержимое в колонке Производитель список 
А фрагмента (Формулы/ Определенные использует функция закрыть диалоговое окно. ячейке вместо Америка (ячейкана листе перечень фамилий всех очередное по номеру куда подевались в работать почему-то. Но не увидел её списке (столбце А)команду названиями марок (желтые любой указанной ячейки должен быть Fiat. это то, что имена).CHOOSEИмена диапазонам, содержащим города,B2В1Списки сотрудников этого отдела имя сотрудника (используя новом облике офиса всё равно большое среди рассмотренных на нужных нам значенийПроверка данных (Data validation) ячейки в нашем в адрес диапазона, Если бы мы мы хотим получить:СОВЕТ(ВЫБОР), чтобы выбрать можно присвоить точно– принадлежащий ей) ввести «Северная Америка»(т.е. диапазон, охватывающий (двухуровневая иерархия); функцию НАИМЕНЬШИЙ) из команды и меню спасибо за вариант. форуме. Если я — марок автовыбрать из выпадающего списка примере). После нажатия который понимает Excel. использовали промежуточный столбецИтак, мы имеем::
1-й, 2-й или
таким же образом.
город, как на (соответственно подкорректировав ячейку все ячейки сГород – Улица – списка или пустую из XL’я 2003?..steysi просто не туда (G7) вариант проверки на То есть, если
(это было бытип автомобиля: Легковой, ФургонЕсли в будущем
3-й именованный диапазон.Теперь мы можем создать примере:А2 названиями Регионов и Номер дома. При ячейку, если именаСписки — проверка: Подскажите пожалуйста как смотрел, дайте ссыль,
размер_диапазона_в_столбцах = 1, т.к.Список (List)ОК в ячейке лежит отличным решением, но и Внедорожник (Категория) потребуется пополнять списокВот так будет выглядеть выпадающие списки в
Для начала нужно создать), то после нажатия
Стран);
заполнении адреса проживания
свободных сотрудников уже
office-guru.ru
Ввод данных из списка значений. Часть 1: Выпадающий список в MS EXCEL
данных, условное - сделать и возможно пжл, и можно нам нужен одини указать впервый выпадающий список текст «А1», то хотели бы показатьпроизводитель: Fiat, Volkswagen i сотрудников, то придется
наш второй раскрывающийся тех ячейках, где базу данных. На кнопки Создать изнажать кнопку «Создать из можно из списка кончились. на главной, Имена ли это в будет убить тему. столбец с моделями качестве готов: функция выдаст в вам что-то более Suzuki (Подкатегория) и
вручную модифицировать границы список: планировали выбирать данные. втором листе я выделенного фрагмента будет выделенного фрагмента» (пункт выбрать город, затемв Excel 2003 и — через формулы екселе, выпадающий список
koyaanisqatsiВ итоге должно получиться
- Источника (Source)Теперь создадим второй выпадающий результате ссылку на крутое
),
),
- модель: … немножечко их именованного диапазона. КакВ результате мы получим
- Выделите ячейку занес список стран, создано имя «Северная_Америка». меню Формулы/ Определенные из списка всех старше идем в и пр.пр. со следующим условием:: maverick_77, так ? что-то вроде этого:=Марки список, в котором ячейку А1. Если
то мы бы есть (Подподкатегория)
обойти это неудобство два связанных (илиB1 которые хочу дать В этом случае имена/ Создать из улиц этого города меню
VLad777 0-нет льгот, 1-скидкаgling
- Осталось добавить выпадающий списокили просто выделить будут отображаться модели в ячейке лежит искали комбинацию этих
- В то же время читайте в статье зависимых) выпадающих списка.
- (в ней мы
- пользователям на выбор формула =ДВССЫЛ(A5) работать
- выделенного фрагмента);
– улицу, затем,Вставка — Имя -: 2007 и 2010 в оплате 25%,: Может так? Изменения на основе созданной ячейки D1:D3 (если выбранной в первом
слово «Маша», то данных: Легковой Fiat. мы имеем следующие Динамический диапазон. Если мы выбираем будем выбирать страну), в первом раскрывающемся не будет, т.к.Убедиться, что стоит только
из списка всех Присвоить (Insert -Главная — Условное 2- 50%???? Нужно в закрашенных ячейках. формулы к ячейке они на том списке марки. Также функция выдаст ссылку Однако у нас данные:Теперь создадим Выпадающий список страну откройте вкладку
excel2.ru
Связанные выпадающие списки и формула массива в Excel
списке, а в при выборе региона галочка «В строке домов на этой Name — Define) форматирование-Создать правило(или управление вычислить льготу иkoyaanisqatsi G8. Для этого: же листе, где как в предыдущем на именованный диапазон нет такого столбца,
Два связанных выпадающих списка с формулой массива
Этот список должен быть для ввода фамилийFranceData соседнем столбце указал «Северная Америка» функция выше»; улице – номерв Excel 2007 и правилами) оплату: gling, У васвыделяем ячейку G8 список). случае, откройте окно с именем но мы можем отсортирован в следующей в ведомость:, в связанном списке
(Данные), нажмите
- числовой индекс, который ДВССЫЛ() не найдет
- Нажать ОК. дома (трехуровневая иерархия).
- новее — жмемФормулы — Диспетчер
Вячеслав Я поприличнее ) Списоквыбираем на вкладке
А вот для зависимогоПроверки данныхМаша
- создать его «на
- очередности:
- выделите ячейки ведомости, куда
у нас будутData Validation соответствует одному из соответствующего имени. ПоэтомуПроверить правильность имени можноВ этой статье рассмотрен кнопку имен.: только из нужныхДанные (Data) списка моделей придется, но в поле
и т.д. Такой, лету», другими словами,Тип. будут вводиться фамилии города только из(Проверка данных), а списков городов. Списки формулу можно подкорректировать, через Диспетчер Имен только двухуровневый связанныйДиспетчер Имен (Name Manager)serg14steysi данных. У менякоманду
создать именованный диапазон
Первый и второй связанный выпадающий список: Тип и Производитель
Источник своего рода, «перевод используя формулу массива.Производитель. сотрудников, например Франции.
затем в выпадающем городов располагаются правее чтобы она работала
(Формулы/ Определенные имена/ список. Многоуровневый связанныйна вкладке: Ясно. Спасибо за
, можно написать формулу с пустыми (Проверка данных (Data validation) с функцией
Третий связывающий выпадающий список: Модель
нужно будет ввести стрелок» ;) Набирая эту формулу,Модель.А2:А5Из этой статьи Вы меню выберите в столбцах при наличии пробелов Диспетчер имен). Должно список рассмотрен вФормулы (Formulas) помощь. Прошу прощения в ячейке иgling
или в менюСМЕЩ вот такую формулу:Возьмем, например, вот такой вы можете себеОн может быть любой; узнали, как можноData ValidationD в названиях Регионов: быть создано 5 одноименной статье Многоуровневыйи создаем новый именованный за глупые вопросы. растащить далее по: Именованный динамический диапазон.Данные — Проверка (Data(OFFSET)=ДВССЫЛ(F3) список моделей автомобилей представить, что такой длины. Что ещевызовите инструмент Проверка данных сделать простейшие связанные(Проверка данных)., =ДВССЫЛ(ПОДСТАВИТЬ(A5;» «;»_»)). имен. связанный список. диапазонЯ знаю, что делать, ячейкам:
maverick_77 — Validation), который будет динамическиили =INDIRECT(F3)
Toyota, Ford и
промежуточный столбец существует, важно: стоит добавить (Данные/ Работа с выпадающие списки вОткроется диалоговое окноFТеперь оМожно подкорректировать диапазон уСоздание иерархических структурИмена
но не знаюКод =ЕСЛИ(A2=0;B2*1;ЕСЛИ(A2=1;B2*1,25;ЕСЛИ(A2=2;B2*1,5;»Больше скидок: koyaanisqatsi, gling, Принципиально,из выпадающего списка выбираем ссылаться только нагде F3 — адрес Nissan: и вы увидите, к нему еще данными/ Проверка данных); Microsoft Excel. ВыData Validationинедостатках
имени Регионы (вместо
данных позволяет избежатьпо следующей формуле:
куда потом девать нет»))) Пример во образ результата такой! вариант проверки ячейки моделей определенной ячейки с первымВыделим весь список моделей что будет проще два меньших списка,установите условие проверки Список; можете взять этот
(Проверка вводимых значений).H
. =списки!$A$2:$A$6 установить =списки!$A$2:$A$5, неудобств выпадающих списков=СМЕЩ(Лист1!$I$2;0;0;СЧЁТЗ(Лист1!$I$2:$I$10)-СЧИТАТЬПУСТОТЫ(Лист1!I$2:I$10)) тела… вложении. Результат по =) Уже радостно!Список (List) марки. Для этого: выпадающим списком (замените Тойоты (с ячейки ;-) необходимых для Типа
exceltable.com
Связанные (зависимые) выпадающие списки
Способ 1. Функция ДВССЫЛ (INDIRECT)
в поле Источник введите простой пример иМы хотим дать пользователю. Так, например, рядомПри создании имен чтобы не отображалась связанных со слишкомв англоязычной версии =OFFSET(Лист1!$I$2;0;0;COUNTA(Лист1!$I$2:$I$10)-COUNTBLANK(Лист1!I$2:I$10))Имеем в качестве примера столбцу Спасибо большое! =)))и вводим вНажмите на свой). А2 и внизДля определения положения Легковой и Производителя, то =Сотрудники; использовать его для на выбор список с с помощью кнопки последняя пустая строка) большим количеством элементов.
Фактически, мы просто даем недельный график дежурств,Сgling, у Вас,
качествеCtrl+F3Все. После нажатия на до конца списка) Fiat, мы, конечно, есть к категориинажмите ОК. решения реальных задач. вариантов, поэтому вFrance меню Создать изНа листеСвязанный список можно диапазону занятых ячеек который надо заполнитьsteysi конечно, изящней =)Источника (Source)или воспользуйтесь кнопкойОК и дадим этому будем использовать функцию (первый список) иТеперь при выделении любойУрок подготовлен для Вас полестоит индекс выделенного фрагмента, всеТаблица
реализовать в EXCEL, в синем столбце именами сотрудников, причем: ОО спасибо большое!!! Особенно хорошо, чтознак равно иДиспетчер имен (Name manager)содержимое второго списка диапазону имя ПОИСКПОЗ. Смотрите: подкатегории (второй список). ячейки из диапазона командой сайта office-guru.ruAllow2 именованные диапазоны для, для ячеек
с помощью инструмента собственное название для каждого сотрудника ))) Сейчас попробую))) в выпадающем списке имя нашего диапазона,на вкладке будет выбираться поToyotaПОИСКПОЗ(B4&C4;F5:F39&G5:G39;0) Эти дополнительные спискиА2:А5Источник: http://www.excel-user.com/2011/02/cascading-validation-lists.html(Тип данных) выберите, который соответствует списку перечней Стран былиA5:A22 Проверка данных (Данные/Имена максимальное количество рабочихserg14 не меняется последовательность т.е.Формулы (Formulas) имени диапазона, выбранного. В Excel 2003Вышеописанное означает, что мы списки выглядят следующим
, справа от ячейкиПеревел: Антон АндроновList городов созданы одинаковой длинысформируем выпадающий список Работа с данными/. дней (смен) ограничено.: Здравствуйте! машин относительно друг=Модели
. В версиях до
в первом списке.
и старше - хотим знать позицию образом: будет появляться кнопка
Автор: Антон Андронов(Список). Это активирует2 (равной максимальной длине для выбора Региона. Проверка данных) с
Осталось выделить ячейки B2:B8 Идеальным вариантом было
- Прошу вашей помощи друга. Это важноВуаля! 2003 это былаМинусы это можно сделать Легкового Fiatа (отсюдаДело в том, что со стрелкой, нажавПри заполнении ячеек данными поле. Позже Вы увидите, списка для регионавыделяем ячейки условием проверки Список
- нашего графика и бы организовать в в решении такого для нас.4 способа создать выпадающий команда менютакого способа: в меню и связь B4&C4). эти списки не на которую можно иногда необходимо ограничитьSource
- как этот индекс Европа (5 значений)).A5:A22 (пример создания приведен
Способ 2. Список соответствий и функции СМЕЩ (OFFSET) и ПОИСКПОЗ (MATCH)
добавить в них ячейках B2:B8 выпадающий вопроса.Можно ли формулы,
список в ячейкахВставка — Имя -В качестве вторичных (зависимых)Вставка — Имя - Где? В нашем
- должны иметь дубликатов выбрать необходимую фамилию. возможность ввода определенным(Источник), где необходимо будет использован. Это привело к; в данной статье) выпадающий список с список, но приВозможно ли каким-то которые Вы написали листа
- Присвоить (Insert - диапазонов не могут Присвоить (Insert - воображаемом вспомогательном столбце,
- записей по ТипуПроверку данных можно настроить списком значений. Например, указать имя диапазонаЕсли Вы работаете в тому, что связанные вызываем инструмент Проверка данных; или с помощью элементами диапазона этом сделать так, образом в функции на листе «транспорт»,
Автоматическое создание выпадающих списков Name — Define) выступать динамические диапазоны Name — Define). то есть: F5:F39&G5:G39. и Производителю, находящихся так, чтобы при при заполнении ведомости со странами. Введите Excel 2010, то
- списки для другихустанавливаем тип данных – элемента управления формыИмена чтобы уже занятые ЕСЛИ() создать список разместить на третьем при помощи инструментовСоздайте новый именованный диапазон задаваемые формулами типаВ Excel 2007 И здесь самая
- в списке Моделей. вводе фамилий не ввод фамилий сотрудников в этом поле можете создать лист-источник регионов содержали пустые Список; Список (см. статью. Для этого
сотрудники автоматически убирались
если условие не
листе? А то надстройки PLEX с любым именемСМЕЩ (OFFSET) и новее - большая сложность всей Вы можете создать
из списка появлялось с клавиатуры можно «=Country» и жмите в отдельной рабочей строки.в поле Источник вводим: Связанный список нав Excel 2003 и из выпадающего списка, выполняется. этот лист у
Выбор фото из выпадающего (например
. Для первичного (независимого)
- на вкладке формулы. их с помощью
- окно с описанием заменить выбором изОК книге. Если жеКонечно, можно вручную откорректировать =Регионы основе элемента управления старше — откроем оставляя только свободных:
- serg14 нас отчётный… спискаМодели
- списка их использоватьФормулы (Formulas)Остальное уже проще, а инструмента «Удалить дубликаты» ошибки (для этого определенного заранее списка. Теперь нам нужно у Вас версия диапазоны или даже
- Теперь сформируем выпадающий список формы). меню
Чтобы реализовать подобный вариант: файл в 2003
glingВыпадающий список с автоматическим) и в поле можно, а вот
- с помощью
- наибольшего внимания требует (например, это показано во вкладке Сообщение (табеля). сделать второй раскрывающийся Excel 2003 года, вместо Именованных диапазонов
- для столбца СтранаСоздание Связанного списка наДанные — Проверка (Data выпадающего списка выполним excel: На другом листе удалением уже использованныхСсылка (Reference) вторичный список долженДиспетчера имен (Name Manager)
функция СЧЁТЕСЛИМН, которая
Ссылки по теме
- в этом видео для ввода введитеОдним из вариантов заполнения
- список, чтобы пользователи и Вы планируете создать Динамические диапазоны.
- (это как раз основе Проверки данных
- — Validation) несколько простых шагов.VLad777
- эти формулы будут элементовв нижней части
planetaexcel.ru
Выпадающий список, формируемый по условиям (Формулы/Formulas)
быть определен жестко,. Затем повторим то
проверяет, сколько есть продолжительностью около 2
необходимый текст). ячеек является выбор могли выбрать город. использовать именованный диапазон, Но, при большом и будет желанный
рассмотрим на конкретном
,Сначала давайте подсчитаем кто: для ячейки всего выглядеть такДинамическая выборка данных для окна введите руками без формул. Однако, же самое со Легковых Fiatов. В минут). Когда мыНедостатком значений из заранее Мы поместим этот
то значения должны количестве имен делать Связанный список). примере.в Excel 2007 и из наших сотрудников используйте ф-цию СЧЕТЕСЛИ.200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИОШИБКА(ВПР(СТРОКА(A1);Транспорт!$A$3:$B$8;2;0);»») выпадающего списка функциями следующую формулу: это ограничение можно списками Форд и частности, она проверяет, это сделали, тогдаэтого решения является определенного списка в
раскрывающийся список в находиться в той это будет достаточновыделяем ячейкиЗадача новее — жмем уже назначен надля выпадающего диап.но и в ИНДЕКС и ПОИСКПОЗ
=СМЕЩ($A$1;ПОИСКПОЗ($G$7;$A:$A;0)-1;1;СЧЁТЕСЛИ($A:$A;$G$7);1) обойти, создав отсортированный
Ниссан, задав соответственно сколько раз в …
то, что у MS EXCEL. Предположим, ячейку же книге, можно трудоемко.B5:B22
: Имеется перечень Регионов, кнопку
дежурство и на макрос нужен. именованном диапазоне «Врейс»maverick_77=OFFSET($A$1;MATCH($G$7;$A:$A;0)-1;1;COUNTIF($A:$A;$G$7);1)
список соответствий марка-модель имена диапазонам списке встречаются такие пользователя есть потенциальная что в таблицуB2 на другом листе.
Кроме того, при; состоящий из названийПроверка данных (Data Validation) сколько смен. Дляvikttur тоже нужно сделать
: Здравствуйте.Ссылки должны быть абсолютными (см. Способ 2).Ford записи, которые вДля ячеек, которые должны возможность ввести в ведомости необходимо вводить. А теперь вниманиеМы будем использовать именованные добавлении новых Регионоввызываем инструмент Проверка данных; четырех регионов. Дляна вкладке
этого добавим к: Вариант без VBA,
ссылку на новыйПомогите, пжл, решить (со знаками $).Имена вторичных диапазонов должныи столбце F5:F39 имеют стать раскрывающимися списками ведомость повторяющиеся фамилии. фамилии сотрудников. Чтобы – фокус! Нам диапазоны и сделаем придется вручную создаватьустанавливаем тип данных –
каждого Региона имеетсяДанные (Data) зеленой таблице еще но «не требуется» лист где будут насущную проблему. После нажатия Enter
совпадать с элементами
Nissan значение Легковой, а в меню «Данные» Для того, чтобы
не ошибиться с нужно проверить содержимое
так, чтобы эти именованные диапазоны для Список; свой перечень Стран.
В открывшемся окне выберем один столбец, введем тоже нужно выбирать» эти формулы. СтолбецСоздаём планировщик маршрутов
excelworld.ru
Выпадающий список с определенным условием
к формуле будут первичного выпадающего списка.. в столбце G5:G39 выбираем «Проверка данных» контролировать появление повторяющихся написанием фамилий можно ячейки с названием связанные выпадающие списки их Стран.в поле Источник вводим:
Пользователь должен иметь в списке допустимых в него следующую (столбец G). А на листе транспорта. На маршрут автоматически добавлены имена
Т.е. если вПри задании имен помните — Fiat. Функция и как тип фамилий, можно использовать
предварительно создать список страны (ячейка B1), работали во всех
CyberForum.ru
Функция «ЕСЛИ()» + выпадающий список
Чтобы не создавать десятки =ДВССЫЛ(A5)
возможность, выбрав определенный значений вариант формулу:
Вариант с условным Транспорт можно скрыть можно отправить машину листов — не нем есть текст
о том, что выглядит так: данных выбираем «Список».
идеи из статьи всех сотрудников организации, чтобы получить индекс
версиях Excel. Следующий имен, нужно изменить
Важно, чтобы при создании Регион, в соседнейСписок (List)=СЧЁТЕСЛИ($B$2:$B$8;E2) или в англоязычной форматированием — столбцы
от лишних вопросов. удовлетворяющую двум условиям: пугайтесь :)
с пробелами, то имена диапазонов вСЧЁТЕСЛИМН(F5:F39;B4;G5:G39;C4)Для Типа как источник
Ввод данных из а заполнение ведомости
соответствующий базе данных шаг – создать сам подход при правила Проверки данных ячейке выбрать из
и укажем версии =COUNTIF($B$2:$B$8;E2) I:J.
maverick_77 «условие 1» и
Функция
придется их заменять Excel не должны
А вся формула для данных мы просто списка значений. Часть свести к выбору с городами. Если именованные диапазоны для
построении Связанного списка. активной ячейкой была Выпадающего списка нужнуюИсточник (Source)Фактически, формула просто вычисляетserg14: gling, Спасибо! =) «условие 2″СМЕЩ (OFFSET) на подчеркивания с содержать пробелов, знаков раскрывающегося списка это: указываем диапазон B7:B9.
2. Выпадающий список фамилии из этого пользователь выберет наших списков. На Рассмотрим этот подход
B5 ему Страну из
данных: сколько раз имя: Благодарю за ответ.
Но у меняИтого имеем:
умеет выдавать ссылку помощью функции препинания и начинатьсяЕсли вы планируете использовать
planetaexcel.ru
Выпадающий список с удалением использованных элементов
Для Производителя мы уже с контролем дублирования.
списка.Portugal
Постановка задачи
вкладке в другой статье:, т.к. мы используем этого Региона.Вот и все! Теперь сотрудника встречалось в А возможно привести возникли проблемы, когда- на одном на диапазон нужногоПОДСТАВИТЬ (SUBSTITUTE) обязательно с буквы. эту формулу в используем формулу, котораяИтак, как сделать дваИнструмент Проверка данных (Данные/
, то мы должныFormulas Расширяемый Связанный список.
Шаг 1. Кто сколько работает?
относительную адресацию.Таблицу, в которую будут при назначении сотрудников диапазоне с именами. пример такого макроса? в столбце с листе список номеров размера, сдвинутый относительно, т.е. формула будет Поэтому если бы
нескольких ячейках - подробно описана здесь.
связанных списка в Работа с данными/ обратиться к базе(Формулы) есть команда
Шаг 2. Кто еще свободен?
ПРЕДСТАВЬТЕ СИТУАЦИЮ:Тестируем. Выбираем с помощью заноситься данные с на дежурство ихТеперь выясним, кто изserg14 номерами машин появились машин (бортов) с исходной ячейки на выглядеть как =ДВССЫЛ(ПОДСТАВИТЬ(F3;»
в одной из
Шаг 3. Формируем список
не забудьте обозначить Она выглядит так: Excel: категория, подкатегория Проверка данных) с с индексомName ManagerМы хотим создать выпадающего списка в помощью Связанного списка, имена будут автоматически наших сотрудников еще: Спасибо огромное!!!
буквы. Я в
признаками по условиям
заданное количество строк
«;»_»)) марок автомобилей присутствовал ячейки как абсолютныеМодель — описание для и категория более условием проверки Список,3(Диспетчер имён). Нажав в Excel небольшую ячейке разместим на листе
Шаг 4. Создаем именованный диапазон свободных сотрудников
- удаляться из выпадающего свободен, т.е. неserg14 формуле в диспетчере (в приложенном примере и столбцов. В
- Надо руками создавать много бы пробел (например ссылки! И еще этой записи сделаем нижнего уровня. Своими как раз предназначен
, в которой хранятся на нее, откроется табличку, где можноA5
Таблица
списка, оставляя только
исчерпал запас допустимых: Только я не имён вместо СЧЁТ — лист «Транспорт»). более понятном варианте именованных диапазонов (если
Шаг 5. Создаем выпадающий список в ячейках
Ssang Yong), то СУПЕРВАЖНО, чтобы вся таким же самым словами в данном для решения нашей названия городов Португалии. диалоговое окно
- выбрать страну иРегион – Америка,. См. файл примера тех, кто еще смен. Добавим еще очень понял как
- поставил СЧЁТЗ и У каждой машины синтаксис этой функции у нас много его пришлось бы запись была подтверждена
образом. случае нижний уровень задачи: с помощью Мы воспользуемся функциейName Manager соответствующий ей город. вызываем связанный список
Связанный_список.xlsx свободен. один столбец и это сделать…не подскажите в выпадающем списке значения условий меняется таков: марок автомобилей).
planetaexcel.ru
заменить в ячейке
Логический оператор ЕСЛИ в Excel применяется для записи определенных условий. Сопоставляются числа и/или текст, функции, формулы и т.д. Когда значения отвечают заданным параметрам, то появляется одна запись. Не отвечают – другая.
Логические функции – это очень простой и эффективный инструмент, который часто применяется в практике. Рассмотрим подробно на примерах.
Синтаксис функции ЕСЛИ с одним условием
Синтаксис оператора в Excel – строение функции, необходимые для ее работы данные.
=ЕСЛИ (логическое_выражение;значение_если_истина;значение_если_ложь)
Разберем синтаксис функции:
Логическое_выражение – ЧТО оператор проверяет (текстовые либо числовые данные ячейки).
Значение_если_истина – ЧТО появится в ячейке, когда текст или число отвечают заданному условию (правдивы).
Значение,если_ложь – ЧТО появится в графе, когда текст или число НЕ отвечают заданному условию (лживы).
Пример:
Оператор проверяет ячейку А1 и сравнивает ее с 20. Это «логическое_выражение». Когда содержимое графы больше 20, появляется истинная надпись «больше 20». Нет – «меньше или равно 20».
Внимание! Слова в формуле необходимо брать в кавычки. Чтобы Excel понял, что нужно выводить текстовые значения.
Еще один пример. Чтобы получить допуск к экзамену, студенты группы должны успешно сдать зачет. Результаты занесем в таблицу с графами: список студентов, зачет, экзамен.
Обратите внимание: оператор ЕСЛИ должен проверить не цифровой тип данных, а текстовый. Поэтому мы прописали в формуле В2= «зач.». В кавычки берем, чтобы программа правильно распознала текст.
Функция ЕСЛИ в Excel с несколькими условиями
Часто на практике одного условия для логической функции мало. Когда нужно учесть несколько вариантов принятия решений, выкладываем операторы ЕСЛИ друг в друга. Таким образом, у нас получиться несколько функций ЕСЛИ в Excel.
Синтаксис будет выглядеть следующим образом:
=ЕСЛИ(логическое_выражение;значение_если_истина;ЕСЛИ(логическое_выражение;значение_если_истина;значение_если_ложь))
Здесь оператор проверяет два параметра. Если первое условие истинно, то формула возвращает первый аргумент – истину. Ложно – оператор проверяет второе условие.
Примеры несколько условий функции ЕСЛИ в Excel:
Таблица для анализа успеваемости. Ученик получил 5 баллов – «отлично». 4 – «хорошо». 3 – «удовлетворительно». Оператор ЕСЛИ проверяет 2 условия: равенство значения в ячейке 5 и 4.
В этом примере мы добавили третье условие, подразумевающее наличие в табеле успеваемости еще и «двоек». Принцип «срабатывания» оператора ЕСЛИ тот же.
Расширение функционала с помощью операторов «И» и «ИЛИ»
Когда нужно проверить несколько истинных условий, используется функция И. Суть такова: ЕСЛИ а = 1 И а = 2 ТОГДА значение в ИНАЧЕ значение с.
Функция ИЛИ проверяет условие 1 или условие 2. Как только хотя бы одно условие истинно, то результат будет истинным. Суть такова: ЕСЛИ а = 1 ИЛИ а = 2 ТОГДА значение в ИНАЧЕ значение с.
Функции И и ИЛИ могут проверить до 30 условий.
Пример использования оператора И:
Пример использования функции ИЛИ:
Как сравнить данные в двух таблицах
Пользователям часто приходится сравнить две таблицы в Excel на совпадения. Примеры из «жизни»: сопоставить цены на товар в разные привозы, сравнить балансы (бухгалтерские отчеты) за несколько месяцев, успеваемость учеников (студентов) разных классов, в разные четверти и т.д.
Чтобы сравнить 2 таблицы в Excel, можно воспользоваться оператором СЧЕТЕСЛИ. Рассмотрим порядок применения функции.
Для примера возьмем две таблицы с техническими характеристиками разных кухонных комбайнов. Мы задумали выделение отличий цветом. Эту задачу в Excel решает условное форматирование.
Исходные данные (таблицы, с которыми будем работать):
Выделяем первую таблицу. Условное форматирование – создать правило – использовать формулу для определения форматируемых ячеек:
В строку формул записываем: =СЧЕТЕСЛИ (сравниваемый диапазон; первая ячейка первой таблицы)=0. Сравниваемый диапазон – это вторая таблица.
Чтобы вбить в формулу диапазон, просто выделяем его первую ячейку и последнюю. «= 0» означает команду поиска точных (а не приблизительных) значений.
Выбираем формат и устанавливаем, как изменятся ячейки при соблюдении формулы. Лучше сделать заливку цветом.
Выделяем вторую таблицу. Условное форматирование – создать правило – использовать формулу. Применяем тот же оператор (СЧЕТЕСЛИ).
Скачать все примеры функции ЕСЛИ в Excel
Здесь вместо первой и последней ячейки диапазона мы вставили имя столбца, которое присвоили ему заранее. Можно заполнять формулу любым из способов. Но с именем проще.