Профессиональные приемы работы в Microsoft Excel

Обмен опытом => Microsoft Excel => Тема начата: Findirector от 20.02.2008, 14:39

Название: Списки в Excel
Отправлено: Findirector от 20.02.2008, 14:39
Hello users? Вопрос, как из выпадающего списка при нажатии на стрелочку вниз и при вводе первой буквы слова появлялось слово из списка.
Название: Re: Списки в Excel
Отправлено: Алексей Шмуйлович от 20.02.2008, 21:56
Список по нажатию мышкой можно создать тремя способами:
1. Панель инструментов Форма. Выбрать выпадающий список, нарисовать где нужно. В свойствах настроить источник строк и присоединенную ячейку.
2. Аналогично сосздать список, но из панели инструментов Элементы управления. Этот способ посложнее в настройке, но списком можно управлять из VBA. Соответственно, имеет больше настроек и возможностей.
3. Меню Данные - Проверка. Тип проверки - Список. Настройки аналогичные.

А вот чтобы по введенным первым буквам подставлялось значение - это сложнее. Это будет автоматически происходить, если в том же столбце в текущей области уже введена та строка, которую нужно подставить.
Под текущей областью понимают область таблицы, со всех сторон изолированную от других областей пустыми строками и столбцами.
Название: Re: Списки в Excel
Отправлено: Волан$ от 16.04.2008, 16:40
Добрый день.

Подскажите как можно решить следующие задачи:
1. Есть три колонки - код, наименование и еще один код.
Требуется создать список (drop down list) из которого пользователи будут выбирать значения типа: "код"+"/"+"наименование "+"/"+"еще один код", а в текущую ячейку будет подставляться код и в две колонки справа будет расщепляться наименование и другой код без разделителей.

2. Есть две колонки - группа, подгруппа.
Требуется в первом списке выбрать нужную группу, а второй список справа как бы вешает фильтр и предлагает для выбора только подгруппы данной группы. Все через списки.

За любые идеи буду благодарен!
Название: Re: Списки в Excel
Отправлено: Волан$ от 21.04.2008, 15:11
Всё оказалось очень просто. Функция OFFSET с Validation очень хорошо помогли.
Название: Re: Списки в Excel
Отправлено: ЛуннаЯ от 06.05.2008, 00:10
Здравствуйте все!
Помогите с решением вопроса: есть номерные бланки (1-1000). Они выдаются по принципу: когда, кому и количество. возврат их длится 1-5 месяцев. за 4 месяца выдано 6000 бланков. Руководство требует предоставлять отчет ежемесячно о "не возвращенных" бланках. Как это систематизировать в Excel? Регистрация возврата идет в MS-DOS, я конвертирую информацию в Excel, но получается невыносимо долго.(http://s.rimg.info/2fe62a9f57e6cf04c1164a7db6607421.gif) (http://smiles.33b.ru/smile.4515.html)
Название: Re: Списки в Excel
Отправлено: Алексей Шмуйлович от 06.05.2008, 00:48
Здравствуйте. А Вы примерчик исходных данных и желаемого результата  к сообщению приложите - мы помозгуем
Название: Re: Списки в Excel
Отправлено: ЛуннаЯ от 06.05.2008, 19:00
Цитата: Алексей Шмуйлович от 06.05.2008, 00:48
Здравствуйте. А Вы примерчик исходных данных и желаемого результата  к сообщению приложите - мы помозгуем
Спасибо за надежду! Информация во вложении 1
Название: Re: Списки в Excel
Отправлено: ЛуннаЯ от 06.05.2008, 19:06
Цитата: Алексей Шмуйлович от 06.05.2008, 00:48
Здравствуйте. А Вы примерчик исходных данных и желаемого результата  к сообщению приложите - мы помозгуем
И во вложении 2. Буду с нетерпением ждать (http://s.rimg.info/c3bc8be8b76143e00ebaaeeb0b67cef2.gif) (http://smiles.33b.ru/smile.662.html).
Название: Re: Списки в Excel
Отправлено: Алексей Шмуйлович от 08.05.2008, 10:40
Из примеров понять задачу пока сложно.
Давайте разделим ее на составляющие.
1) Бланки выдаются сотрудникам. Это где-то регистрируется и можно список выгрузить через DOS в Excel. Только это должен быть именно список - один список с 4 полями: Серия, Номер, Имя, Дата выдачи. А не так как у Вас - серия в шапке и столбцы по сериям прилепляются справа. Поэтому лучше все-таки выложить исходный DOS-файл.
2) Бланки возвращаются. Резонно предположить, что это тоже где-то регистрируется. Иначе задача просто не имеет решения. В каком виде есть такая информация? Лучше всего, конечно, организвать регистрацию в той же программе, что и выдача. Но если нет, то опишите, что есть.
3) Задача - сопоставить два списка и выбрать выданные, но не возвращенные в срок бланки. Это легко сделать и в Excel и, особеноо легко, в Access.
Название: Re: Списки в Excel
Отправлено: ЛуннаЯ от 09.05.2008, 17:34
Господа! В том то и дело, что выдача бланков протоколов осуществляется по бумажному носителю по приложенному образцу «Книга выдачи бланков.xls». Мало содержательная информация! Бланки в количестве 10, 20, 50, 100, 200, ..., 1000 получают в соответствии с  ведомственным распоряжением – руководители отделов (по их потребности). В своих подразделениях они по аналогичному журналу передают протоколы исполнителям, ко мне ежедневно возвращают протоколы уже исполнители. Все возвращенные протоколы по мере поступления регистрируются в DOS-е, порядок этого поступления полностью отражен на листе «2008.xls»

Мне для учета был предложен принцип, отраженный в «Ревизии.xls». Здесь по мере поступления бланков для регистрации в DOS из вертикального списка удалялись «возвращенные», то есть рабочий инструмент – бумага и перо (или в электронном виде). Но человеческий фактор – масса небрежности в таком учете. Ошибок, пропусков – тьма.
Может быть, в Exls для каждого отдела завести свой лист учета выданных бланков и сопоставлять отраженный в нем список к листу «2008.xls»? Этот лист у меня уже в нескольких видах анализа – источник динамичной информации (в него ежедневно добавляю сведения из DOS).

Для руководства я  должна предоставлять к 10 числу каждого месяца сведения «о долгах по протоколам»

Отдел

Дата получения   Количество не возвращенных бланков   Серия          Из них №  
         
   Хотелось бы это привести в соответствие с ИХ требованиями и возможностями ПК.
Access? Хочу попробовать, но понять бы принцип формирования баз данных применительно к моей проблеме. Продемонстрировать сможете? Буду благодарна за науку!
Название: Re: Списки в Excel
Отправлено: Шпец Докапыч от 16.05.2008, 02:42
Цитата: Алексей Шмуйлович от 08.05.2008, 10:40
Из примеров понять задачу пока сложно...
3) Задача - сопоставить два списка и выбрать выданные, но не возвращенные в срок бланки. Это сделать в Excel легко...

Действительно :)
Во вложении макрoс, сравнивающий два массива номеров бланков и выводящий в отчёт невозвращённые.
Название: Re: Списки в Excel
Отправлено: Waalan от 20.05.2008, 12:36
Вопрос в продолжение данной темы:
для начала имеется список, например, наименование и еще несколько столбцов характеристик к этим данным. Так вот на других листах необходимо, чтобы по вводу наименования (я сделал раскрывающийся список) характеристики заносились автоматом. Так вот вопрос: как же это сделать? Искать пытался, но почемуто не нашел ..
Название: Re: Списки в Excel
Отправлено: Шпец Докапыч от 20.05.2008, 14:27
Цитата: Waalan от 20.05.2008, 12:36
Для начала имеется список...

Раз список, собственно, один – можно использовать функцию ИНДЕКС.
Название: Re: Списки в Excel
Отправлено: Waalan от 21.05.2008, 11:41
Прошу прощения, но сделать как у вас не получилось  :-[, хотя пробовал даже скопировать у Вас и поменять на свои ссылки, и еще вопрос: как был сделан раскрывающийся список (я сделал через проверку, но там нельзя ссылаться на другие листы)? Если Вам не сильно трудно можете записать комментарии по ходу выполнения.
Название: Re: Списки в Excel
Отправлено: Шпец Докапыч от 21.05.2008, 12:08
Цитата: Waalan от 21.05.2008, 11:41
И еще вопрос: как был сделан раскрывающийся список (я сделал через проверку, но там нельзя ссылаться на другие листы)?

На листе "Список" диапазону A2:A9 присвоено имя АВТО, которое и указано в проверке данных.
Название: Re: Списки в Excel
Отправлено: Waalan от 21.05.2008, 19:51
Я все понял! СПАСИБА!!!
Название: Re: Списки в Excel
Отправлено: ЛуннаЯ от 22.05.2008, 20:53
Цитата: Шпец Докапыч от 16.05.2008, 02:42


Действительно :)
Во вложении макрoс, сравнивающий два массива номеров бланков и выводящий в отчёт невозвращённые.

(http://s12.rimg.info/ab5828155a4818b57defa684591648d1.gif) (http://smiles.33b.ru/smile.110245.html) (http://s.rimg.info/bb8182c284c32d99c6c6db2a56d0d0e7.gif) (http://smiles.33b.ru/smile.3809.html) (http://s.rimg.info/bb8182c284c32d99c6c6db2a56d0d0e7.gif) (http://smiles.33b.ru/smile.3809.html) (http://s.rimg.info/bb8182c284c32d99c6c6db2a56d0d0e7.gif) (http://smiles.33b.ru/smile.3809.html) Шпец Докапыч! 
Завидую Вашим друзьям: у них есть ВЫ!(http://s2.rimg.info/8b77985fbaa9e447b525442d5a91bc5a.gif) (http://smiles.33b.ru/smile.29225.html)
Название: Re: Списки в Excel
Отправлено: GWolf от 26.06.2008, 07:49
Цитата: Алексей Шмуйлович от 20.02.2008, 21:56
2. Аналогично сосздать список, но из панели инструментов Элементы управления. Этот способ посложнее в настройке, но списком можно управлять из VBA. Соответственно, имеет больше настроек и возможностей.

А с примером кода? Если возможно! Спасибо!!!
Название: Re: Списки в Excel
Отправлено: Шпец Докапыч от 26.06.2008, 10:52
В контекстном меню списка есть пункт "Свойства", которые, соответственно, и можно назначать с помощью VBA (как и любому объекту Excel), например:
Sub SH_Test_Prop()
  With ActiveSheet.Shapes("ListBox1").DrawingObject
    .LinkedCell = "B2"
    .ListFillRange = "Лист1!A1:A10"
  End With
End Sub


Естественно, в нагрузку, объект позволяет работать со свойствами и методами общими для всех элементов управления (OLEControlObject).
Название: Re: Списки в Excel
Отправлено: lusui от 08.07.2008, 19:00
Всем доброе время суток. Подскажите, пожалуйста, если кто в курсе как осуществить такую вот вещь:

Есть 2-ва листа, основной и второстепенный. На второстепенном находиться информация о машинах: класс, марка, модель, номер и хозяин. Суть в том что бы на основном листе создать выпадающий список и при выборе номера все остальное выбиралось само

Может кто-то подскажет, смотрел эту тему http://www.planetaexcel.ru/tip.php?aid=64 в ней полностью разобрался, но это не то что нужно.
Подскажите, пожалуйста, кто знает

Файл с примером: http://aro-ua.com/test.xls
Название: Re: Списки в Excel
Отправлено: VictorK от 08.07.2008, 23:09
Цитата: lusui от 08.07.2008, 19:00
Есть 2-ва листа, основной и второстепенный. На второстепенном находиться информация о машинах: класс, марка, модель, номер и хозяин. Суть в том что бы на основном листе создать выпадающий список и при выборе номера все остальное выбиралось само
Динамическая выборка из списка функциями ИНДЕКС и ПОИСКПОЗ (http://www.planetaexcel.ru/tip.php?aid=102)
Вместо ссылок на диапазон данных лучше использовать имена диапазонов.
Если список на "второстепенном" листе будет со временем дополняться новыми данными, можно применить:
Использование динамических именованных диапазонов (http://www.planetaexcel.ru/tip.php?aid=93)
Название: Re: Списки в Excel
Отправлено: oleg от 30.07.2008, 11:47
Доброго времени суток. Задача: есть список из 50 строк, при выборе из списка, скажем, ТОВАР_004 ячейка напротив списка принимает значение, равное цене ТОВАР_004. Как это сделать? Я делел через ЕСЛИ, но у этой функции только 7 раз можно выполнять проверку.
Подскажите, как решить проблему, спасибо.
Название: Re: Списки в Excel
Отправлено: Василий Алибабаевич от 30.07.2008, 11:56
Используйте функцию ВПР(_) (https://msexcel.ru/content/view/20/2/).
Название: Re: Списки в Excel
Отправлено: oleg от 30.07.2008, 12:31
Не совсем понятно, как эта функция может решить мою задачу. Пример задачи во вложении.
Название: Re: Списки в Excel
Отправлено: oleg от 30.07.2008, 12:48
Всё, огромное спасибо, разобрался и все сделал :)
Название: Re: Списки в Excel
Отправлено: oleg от 30.07.2008, 13:19
Так, разобрался с одним, столкнулся с новой проблемой. Надо сформировать новый список, в зависимости того, что выбрано в списке №1 :-(,   "ЕСЛИ" тоже не подходит...
Название: Re: Списки в Excel
Отправлено: Шпец Докапыч от 30.07.2008, 14:52
Цитата: lusui от 08.07.2008, 19:00
Cмотрел эту (http://www.planetaexcel.ru/tip.php?aid=64) тему,  в ней полностью разобрался, но это не то что нужно.
Цитата: oleg от 30.07.2008, 13:19
Надо сформировать новый список, в зависимости того, что выбрано в списке №1 :-(

Может, то, что не подходит lusui, подойдёт Вам, oleg?
Название: Re: Списки в Excel
Отправлено: Soldier от 11.08.2008, 14:57
Доброго времени всем!

1. Как сделать раскрывающийся список с данными из нескольких листов?
2. Как увеличить количество строк в листе, а то на 65536 непомещается   (
Название: Re: Списки в Excel
Отправлено: MickMick от 16.09.2008, 19:28
Добрый вечер. Падскажите, пожалуйста, как можно решить следующую задачу:
1. Есть два листа. На первом создается таблица (1), на втором находится таблица (2) с данными, которая может дополняться (смотреть вложение). Нужно, чтобы при выборе ячейки "Предмет" в (1) открывалась таблица (2) (как при создании гиперссылки), и при выборе в ней Предмета - в первую таблицу переносились бы все данные, которые связаны с этим предметом.
2. Ширина ячейки "Предмет" в создаваемой таблице должна быть фиксированной, а если данные не помещатся - высота ячейки (и всей строки) автоматически увеличивается.
3. Нужно, чтобы при вводе данных в стоку таблицы (1) автоматически добавлялась следующая чистая строка, и при заполнении ячейки "Предмет" у этой строки появлялся номе по порядку.
4. Под таблицей в правой ячейке должны суммироваться учебные часы, выбранных предметов.
Извините за громоздкое объяснение, но очень нужна ваша помощь. Зараннее спасибо.
Название: Re: Списки в Excel
Отправлено: Шпец Докапыч от 16.09.2008, 20:34
Цитата: MickMick от 16.09.2008, 19:28
Добрый вечер. Подскажите, пожалуйста, как можно решить следующую задачу...

Добрый вечер. Инструкция:

Надеюсь, что объяснил доступно ;)
Название: Re: Списки в Excel
Отправлено: MickMick от 08.10.2008, 15:47
ЦитироватьДобрый вечер. Падскажите, пожалуйста, как можно решить следующую задачу:
Предлагаю свое решение поставленной задачи.
Название: Автоввод в выпадающем списке
Отправлено: vadkol от 09.10.2008, 19:32
Всем привет. Может кто поможет, в выпадающем списке очень большое колличество текстовых элементов, можно ли сделать  автоввод с клавиатуры в списке
Название: Re: Списки в Excel
Отправлено: K.E.N. от 24.12.2008, 09:54
как сделать так чтобы при выборе из списка Ф.И.О. выдергивались данные из Листа3 соттветствующие данной фамилии ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ???
Название: Re: Списки в Excel
Отправлено: IKor от 24.12.2008, 13:16
Проще всего это сделать при помощи АвтоФильтра прямо на листе 3.
Но если потребуется, то можно сделать так как показано в прилагаемом файле - описание формул в самос файле.
Важное ограничение: данные в таблице на листе 3 должны быть отсортированы по сотрудникам - иначе придется сильно усложнять формулы листов.
Кроме того, все то же самое можно сделать при помощи макросов, но я не возьмусь ;)
P.S. в качестве бонуса я убрал из списка сотрудников повторяющиеся фамилии.
Название: Re: Списки в Excel
Отправлено: K.E.N. от 25.12.2008, 11:37
спасибки юольшущие!  :D
с наступающим всех :)  :D

осталось только все разобрать по полочкам :). но с этим я справлюсь сам  ;)
Название: Re: Списки в Excel
Отправлено: glokkk от 08.01.2009, 07:25
Цитата: Шпец Докапыч от 21.05.2008, 12:08
Цитата: Waalan от 21.05.2008, 11:41
И еще вопрос: как был сделан раскрывающийся список (я сделал через проверку, но там нельзя ссылаться на другие листы)?

На листе "Список" диапазону A2:A9 присвоено имя АВТО, которое и указано в проверке данных.

Добрый день!
А если усложнить задачу (см. аттачмент)? К примеру, имеется тот же список машин, но теперь в зависимости от выбранной характеристики должен выпадать список машин, соответствующих данной характеристике. Пробовал решить аналогично - не получилось. Задача осложняется тем, что в одной ячейке находятся несколько характеристик.

Заранее спасибо
Название: Re: Списки в Excel
Отправлено: Шпец Докапыч от 08.01.2009, 10:51
Цитата: glokkk от 08.01.2009, 07:25
А если усложнить задачу (см. аттачмент)?

Во-первых, это можно сделать автофильтром по условию "содержит".
А, во-вторых, вам нужно одно решение или СРАЗУ два? Если два, то составляйте аналогичную формулу с формулой во вложении (там пример выборки).
Название: Re: Списки в Excel
Отправлено: IKor от 08.01.2009, 11:06
;) "Штандартенфюрер тоже, но Хунта успел раньше..." (c) А. и Б. Стругацкие
Еще один вариант решения. Комменатрии внутри файла.
Название: Re: Списки в Excel
Отправлено: glokkk от 08.01.2009, 12:50
Шпец Докапыч, IKor спасибо. Буду смотреть

Шпец, а как вы связали в листе "Сортировка" ячейку В1 с остальным списком?

Название: Re: Списки в Excel
Отправлено: Шпец Докапыч от 08.01.2009, 15:40
Цитата: glokkk от 08.01.2009, 12:50
Шпец, а как вы связали в листе "Сортировка" ячейку В1 с остальным списком?

Похоже, что история повторяется. Waalan (https://forum.msexcel.ru/profiles/waalan-u4448.html) тоже не учёл именованный диапазон. Ссылка на неё ("B1") в имени "VPR". Или Вы о другом?  :-\

Для 2003-го:
Вставка -> Имя -> Присвоить

Для 2007-го:
Формулы -> Диспетчер имён

Название: Re: Списки в Excel
Отправлено: glokkk от 09.01.2009, 12:35
Благодарю. Разобрался.  ???
Название: Re: Списки в Excel
Отправлено: IKor от 09.01.2009, 14:57
Цитата: glokkk от 09.01.2009, 12:35
Нет, я имею ввиду, когда кликаешь на ячейку В1 и выбираешь условие, внизу "выпадает" список соответствующий этим условиям. У меня это не получается сделать

Цитата: Шпец Докапыч от 08.01.2009, 15:40
Похоже, что история повторяется. Waalan (https://forum.msexcel.ru/profiles/waalan-u4448.html) тоже не учёл именованный диапазон. Ссылка на неё ("B1") в имени "VPR".
Цитата: Шпец Докапыч от 21.05.2008, 12:08
Цитата: Waalan от 21.05.2008, 11:41
И еще вопрос: как был сделан раскрывающийся список (я сделал через проверку, но там нельзя ссылаться на другие листы)?

На листе "Список" диапазону A2:A9 присвоено имя АВТО, которое и указано в проверке данных.

меню ДАННЫЕ / ПРОВЕРКА => вкладка ПАРАМЕТРЫ => тип_данных=Список
Название: Re: Списки в Excel
Отправлено: IKor от 15.01.2009, 22:31
Вот в этой теме (https://forum.msexcel.ru/microsoft_excel/problema_s_formuloy_massiva-t1183.0.html) обсуждалась возможность генерации
списка уникальных значений без вспомогательных столбцов.
К сожалению, полностью избежать их использования не удалось,
но даже в таком виде решение кажется мне интересным.
Динамически обновляемый раскрывающийся список для ввода данных
Суть вопроса:
Довольно часто бывает удобно выбирать значение для ввода из наперед заданных (ранее введенных) значений. Для этой цели в Excel есть несколько возможностей, например, проверка вводимых значений (меню ДАННЫЕ / ПРОВЕРКА / список). К сожалению, список необходимо сформировать заранее и для ввода нового уникального значения его предварительно требуется добавить в этот список. Предлагаемое решение позволяет вводить данные как из выпадающего списка ранее введенных значений, так и указывать новые (последние автоматически добавятся к списку уникальных).
Недостатком этого решения является то, что для его работы требуется-таки сформировать список уникальных значений в отдельном месте документа. Однако наполнение этого списка производится автоматически - от человека требуется только следить за тем, чтобы количество строк в нем превышало количество уникальных значений.

Более того, часто бывает необходимо на отдельном листе показать _сводные_ (https://msexcel.ru/content/view/34/2/) данные по введенным ранее значениям: например вводятся найденные на складе товары и их количество, а отдельно требуется посчитать суммарное количество для каждого из товаров. В таких случаях удобно совместить в одном месте список уникальных "товаров" для подсчета их суммы и вспомогательный список уникальных значений.
Во вложенном файле - пример и комментарии к формулам. Надеюсь, что решение будет полезно.

P.S.  На самом деле Excel сам формирует список ранее введенных значений и даже упорядочивает его по алфавиту: работает при нажатии ALT+стрелка_вниз. Но это работает только для текстов, кроме того пропущенная строка (пустая = "") заставляет Excel начинать формировать новый список.

Но мы ведь не ищем легких путей  ::)  ;D
Название: Списки в Excel
Отправлено: Любовь от 17.01.2009, 20:49
Как сделать выпадающий список, чтобы значения списка были "залиты" разными цветами? Т. е. при нажатии на стрелку, нвыпадает список, одно значение которого написано на синем фоне, другое - на красном, третье - на зелёном, и т. д.
Название: Re: Списки в Excel
Отправлено: SNAS от 06.03.2009, 15:43
Добрый день!
Какое событие генерится при выборе из списка ("проверка данных")?
Надо написать макрос, чтобы при выборе из списка ("проверка данных") вместо значения подставить ссылку на ячейку.
Зарание спасибо!
Название: Re: Списки в Excel
Отправлено: Шпец Докапыч от 06.03.2009, 16:19
Цитата: SNAS от 06.03.2009, 15:43
Какое событие генерится при выборе из списка ("проверка данных")?

Worksheet_Change конечно же.
Название: Re: Списки в Excel
Отправлено: SNAS от 11.03.2009, 13:17
ЦитироватьWorksheet_Change конечно же.
Но, в случае маскирования Worksheet_Change придется как-то проверять установлено ли в измененной ячейке "проверка данных"?
Я думал, что есть отдельное событие именно по ячейке с "проверка данных".
И еще - не нашел, где можно взять по ячейке с "проверка данных" ссылку на установленный список.
Название: Re: Списки в Excel
Отправлено: Шпец Докапыч от 11.03.2009, 14:20
Если у Вас эта ячейка плавающая по листу, то проверить проверку (сорри за тавтологию) можно так:
  On Error Resume Next
  testVal = Target.Validation.Formula1
  On Error GoTo 0

  If IsEmpty(testVal) Then
    MsgBox "Нет": Exit Sub
  Else
    MsgBox "Есть"
  End If


Ссылку на список возвращает как раз это свойство (Formula1):
  If Asc(Left(testVal, 1)) = 61 Then
    MVal = Application.Transpose(Evaluate(testVal))
  Else
    MVal = Split(testVal, ";")
  End If
  MsgBox Join(MVal, Chr(10))


Полезный совет: Если будете изменять ячейку в событии "Изменение", чтобы не зациклило отключайте и включайте Application.EnableEvents.
Название: Re: Списки в Excel
Отправлено: SNAS от 11.03.2009, 19:32
Спасибо!
Ячейка не плавающая, просто их много и в разных местах листа.
С ходу получился такой код. Работает, но может можно оптимальнее?

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim strTemp, strTemp1 As String, SearchValue As Variant, rngCurTmp, rngCurTmp1 As Range, shCurTemp As Worksheet
    strTemp = Empty
    If Target.Cells.Count > 1 Then Exit Sub
    On Error Resume Next
        strTemp = Target.Validation.Formula1
    On Error GoTo 0
    If IsEmpty(strTemp) Then
        Exit Sub
    Else
        rngCurTmp = ActiveCell.Address
        shCurTemp1 = ActiveCell.Worksheet.name
        SearchValue = Target.Value
        strTemp = Right(strTemp, Len(strTemp) - 1)
       
        Application.Goto Reference:=[strTemp]
        Selection.Find(What:=Target.Value, After:=ActiveCell, LookIn:=xlValues, _
            LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False).Activate
       
        Application.EnableEvents = False
        Target.Formula = "=" & ActiveCell.Worksheet.name & "!" & ActiveCell.Address
        Application.EnableEvents = True

        Sheets(shCurTemp1).Select
        ActiveSheet.Range(rngCurTmp).Select
    End If
End Sub


Добавление:
Метод Goto Reference выделяет указанный диапазон, что не очень удобно (корректно). Иной способ поиска строки и взятие ее адреса, насколько я понимаю - перебор строк диапазона списка?
Название: Re: Списки в Excel
Отправлено: твой друг от 26.03.2009, 18:29
Здравствуйте! Проблема: есть два списка регионов России с рядами данных по годам, один с 90 до 99 года, другой после 2000 и до 2007. Регионы в списках расположены в совершенно разном порядке, в одном списке на 2 региона больше, чем в другом. Как с помощью excel сделать из этих двух списков один с рядами данных с 90 по 07 годы? очень уж не хочется делать это вручную...
Название: Re: Списки в Excel
Отправлено: IKor от 26.03.2009, 19:25
Вам поможет функция ВПР(_) (https://msexcel.ru/content/view/20/2/) и немного терпения для того, чтобы привести в соответсвие написание регионов на обоих листах.
Посмотрите вложенный файл - и заканчивайте сравнение списков.
Название: Re: Списки в Excel
Отправлено: твой друг от 27.03.2009, 00:00
IKor, спасибо большое за ответ, все очень четко сопоставляется, буду доделывать. подскажите, где можно прочитать про ВПР(_) (https://msexcel.ru/content/view/20/2/), чтоб было просто и доступно, по Вашей ссылке пишет:"Сервер перегружен, попробуйте зайти позже".
Название: Re: Списки в Excel
Отправлено: IKor от 27.03.2009, 09:34
Цитата: твой друг от 27.03.2009, 00:00
IKor, спасибо большое за ответ, все очень четко сопоставляется, буду доделывать. подскажите, где можно прочитать про [url=https://msexcel.ru/content/view/20/2/]ВПР(_) (http://msexcel.ru/content/view/20/2/)[/url], чтоб было просто и доступно, по Вашей ссылке пишет:"Сервер перегружен, попробуйте зайти позже".
Ну и попробуйте зайти позже  ::) , а лучше читайте справку Excel - там все написано очень подробно.
Название: Re: Списки в Excel
Отправлено: Fatat от 21.04.2009, 18:48
Уважаемые формучане, помогите срочно бедному чайнику желательно на уровне ясельной группы растолкуйте:

Есть два листа в Ехцеле на двух вкладках в одном файле.

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

Нужно сравнить списки и найти гостей, имена которых совпадают в обоих списках (полностью или частично) и самое главное чтобы все имена которые хоть как-то совпали выделялись или разным цветом или копировались на отдельную вкладку.
Название: Re: Списки в Excel
Отправлено: IKor от 22.04.2009, 09:57
Цитата: Fatat от 21.04.2009, 18:48
Уважаемые формучане, помогите срочно бедному чайнику желательно на уровне ясельной группы растолкуйте:
Это проще показать, чем рассказать.
На вскидку: можно воспользоваться условным форматированием (меню ФОРМАТ) и выделять цветом те ячейки, для которых выполняется условие:
=СЧЁТЕСЛИ(Диапазон_второго_листа;Текущая_ячейка_первого_листа)>0
В этом случае изо всех гостей будут ОДИНАКОВО выделены те, кто приглашен на этой неделе.
Название: Re: Списки в Excel
Отправлено: Fatat от 22.04.2009, 13:15
Так как оба листа очень длинные скажем несколько тысяч строк, то искать потом в нем выделенные слова будет невозможно долго, нужно что бы они копировались в отдельный лист. Попробовала через VLOOK UP, но теперь проблема в том, что имя одного и того же человека может быть написано в разных вариантах:

Например: Грегори Баскет/ Марианна Саво - 1 лист,
а во втором листе только Марианна Саво,
соответсвенно нужно, что бы при вводе во второй лист Имени "Марианна" у меня на третьем листе появлялась полностью строчка (в 4 колонки)/ или несколько строк (так как имена или фамилии могут повторятся несколько раз по дате приезда) из базы данных содержащая имя "Марианна".
Название: Re: Списки в Excel
Отправлено: IKor от 22.04.2009, 23:46
Цитата: Fatat от 22.04.2009, 13:15
Так как оба листа очень длинные скажем несколько тысяч строк, то искать потом в нем выделенные слова будет невозможно долго, нужно что бы они копировались в отдельный лист.
И все-таки было бы гораздо проще разобраться в Вашем вопросе, если бы Вы приложили короткий пример того, что у есть и того, что нужно получить... (без секретной информации)

Может ли Марианна встречаться на первом листе не единожды? Что нужно выводить на третьем листе в этом случае?

Правильно ли я понимаю, что выделяться должны совпадающие на обоих листах персоналии? не будет ли проще на втором листе выделить (или дополнить нужной информацией с первого листа) лица, присутствующих в обоих листах - это несколько проще, чем создавать третий лист и в нем формировать новый список.
Название: Re: Списки в Excel
Отправлено: Fatat от 23.04.2009, 17:24
Прилагаю файл. Да, возможно также решение проблемы, если совпадающие имена будут высвечиваться и добавляться колонки в листе на заезд, а не в базе так как конечно он короче, но проблема в том, что в одной строке может быть записано 2-3 имени через /,  а в новом заезде может повторятся только одно из имен, будут ли строки добавлятся в таком случае?? или решение проблемы может быть, только если  в одной строке листа будет только одно имя?
Название: Re: Списки в Excel
Отправлено: IKor от 24.04.2009, 00:44
Мне удалось сделать половину дела: на листе Результат выводится список прибывающих гостей построчно.
Настоятельно рекомендую Вам использовать либо "/", либо " / " в качестве разделителя.

К сожалению, моя формула-массива отказалась работать корректно ??? Я попробую что-нибудь придумать завтра, а Вы пока взгляните на решение и скажите в ту ли сторону мы движимся...

P.S. Конечно, можно разбить и базу данных на столбцы, содержащие по одному имени, по аналогии с первым листом, а затем искать каждое имя во всех столбцах - но получится достаточно громоздско...
Название: Re: Списки в Excel
Отправлено: Fatat от 24.04.2009, 13:16
Не понятно, что за числа и откуда они взялись?  ???нужно, что бы либо в первый лист заезда, либо в третий лист результатов добавлялись столбцы из дата басе с датой предыдущего заезда, адресом, и т.д. если имена совпали, разбивать каждое имя в отдельную ячейку не реально. :(
Название: Re: Списки в Excel
Отправлено: IKor от 24.04.2009, 20:50
Цитата: Fatat от 24.04.2009, 13:16
Не понятно, что за числа и откуда они взялись?  ???нужно, что бы либо в первый лист заезда, либо в третий лист результатов добавлялись столбцы из дата басе с датой предыдущего заезда, адресом, и т.д. если имена совпали, разбивать каждое имя в отдельную ячейку не реально. :(
Что было сделано:
1. Из перечня прибывающих (arrival list) на последнем листе (Result) был сформирован список лиц, содержащий по одному имени в каждой строке. Разделитель имен в базовом списке "/" без пробелов.
2. Из полного перечня (data base) по тому же принципу были выделены отдельные имена в отдельных столбцах на том же листе.
3. На листе Result для каждого лица из списка прибывающих были найдены строки data base, в которых присутствуют данные имена.
4. Из найденных строк data base для каждого имени в таблице Result находим данные прочих столбцов.

Подходит ли Вам такое решение?

P.S. Для быстрой cмены разделителей на одинаковые во всех строках можно использовать две автоматические замены: "/ " на "/" и " /" на "/".

UPDATED
как обычно Вариант ШД быстрее и короче  ;D
Но по-моему, все-таки строки с Arrival list придется "растерзать" по разным строкам
Название: Re: Списки в Excel
Отправлено: Шпец Докапыч от 24.04.2009, 21:03
Цитата: Fatat от 23.04.2009, 17:24
проблема в том, что в одной строке может быть записано 2-3 имени через /,  а в новом заезде может повторятся только одно из имен, будут ли строки добавлятся в таком случае?? или решение проблемы может быть, только если  в одной строке листа будет только одно имя?

Дам небольшую заметку: в качестве "искомое_значение" можно указывать неточное, например:
Название: Re: Списки в Excel
Отправлено: Fatat от 26.04.2009, 12:55
IKor и Шпец Докапыч, Спасибо за помощь, последний вариант в принципе отражает  идею, но ,вот например, в первой строке результатов Кawthar находится в data base в 13 и 274 строке, а в результате выдается только одна дата предыдущего заезда из строки 13. :-\
Название: Re: Списки в Excel
Отправлено: Fanatik от 08.05.2009, 09:52
Помогите сделать в Excele раскрывающийся список и что бы при выборе разных значений в списке, менялись значения в таблице
Название: Re: Списки в Excel
Отправлено: kimtan от 08.05.2009, 11:24
Приложенный пример значительно ускорит помощь.
Название: Re: Списки в Excel
Отправлено: Fanatik от 08.05.2009, 11:57
Чтобы при изменении выделенных параметров, менялись выделенные параметры в таблице
Название: Re: Списки в Excel
Отправлено: IKor от 08.05.2009, 12:22
Цитата: Fanatik от 08.05.2009, 09:52
Помогите сделать в Excele раскрывающийся список и что бы при выборе разных значений в списке, менялись значения в таблице
Посмотрите вложение - то ли это, что Вам нужно?
Название: Re: Списки в Excel
Отправлено: Fanatik от 08.05.2009, 12:41
в принципи да, а можно сделать что бы таблицы из которых беруться данные были на других страницах?
Спасибо за помощь.
Название: Re: Списки в Excel
Отправлено: kimtan от 08.05.2009, 12:48
Именованный список можете перемещать на любой лист книги - программа его найдет  :)
Название: Re: Списки в Excel
Отправлено: hammer7 от 16.05.2009, 20:09
Прошу помощи профессионалов!!!
Во вложении по возможности объяснил проблему.
Понимаю, что нужно присваивать имена, но не знаю как автоматизировать присвоение имен.
Сложность в том, что нужно ссылаться на лист со штаткой, которая частенько меняется. Буду просто вставлять лист с новой штаткой с прежним именем, удаляя имеющийся.

Очень расчитываю на Ваши понимание и помощь!
Благодарю заранее!
Название: Re: Списки в Excel
Отправлено: IKor от 17.05.2009, 09:03
Правильно ли я понял, что на листе TDSheet присутствует таблица подразделений, структура которой всегда одинакова:
- во втором столбце указывается название подразделения;
- ниже в строках в 12-ом столбце перечисляются сотрудники подразделения (переменное количество, но без пустых строк);
- первый (верхний) из сотрудников - начальник;
- ниже - несколько строк, не представляющих интереса

Что я предлагаю:
- Определить разницу между строками содержащими название подразделения, строками с именами и прочими строками; например с помощью доп. столбца.
В принципе критерием строки с именем может являться наличие текста в 12 столбце, но названия подразделений нужно как-то выделять (идеальный вариант использовать доп. столбец с уникальными числовыми индексами названий подразделений - например, номерами строк).
- Сформировать отдельную таблицу, содержащую названия подразделений в превом столбце и количество сотрудников в каждом из них:
- - функция НАИМЕНЬШИЙ()
- - функция СЧЁТЗ(СМЕЩ())
- Далее передавать данные о подчиненных из этой таблицы на лист Опись с помощью СМЕЩ(текущее_подразделение;...;соответствующее_количество_сотрудников;...)
- На каком из листов хранить новую таблицу - решать Вам.

P.S. Сейчас нет времени реализовать это в Excel - если вопрос останется актуальным до вечера, то постараюсь помочь.
Название: Re: Списки в Excel
Отправлено: hammer7 от 17.05.2009, 09:33
Структура всегда одинакова, но пустые строки в 12-м столбце со временем могут появиться (сейчас все вакансии сокращены, но пройдет время ...)
Начальник подразделения как правило верхний(в случае назначения нового начальника, может сместиться в самый низ, но чуть позже все-равно вернется на свое место - чел. фактор).
Нижние строки с порядковыми номерами и пустые действительно интереса не представляют, даже мешают создать выпадающий список подразделений.

IKor, я чего только не перепробовал, желаемого результата пока не добился. Более-менее получается после часовой переделки самой штатки, но так каждый раз - замудохаешься переделывать, поэтому обращаюсь к знатокам.
Пока не совсем понял Вашу задумку, и уж точно не смог ее реализовать.
Название: Re: Списки в Excel
Отправлено: kimtan от 17.05.2009, 10:43
hammer7
Попробуйте в книге создать имя (вкладка Формулы-Определенные имена-Присвоить имя), например "начальник" с формулой:
=ИНДЕКС(TDSheet!$B:$D;ПОИСКПОЗ("*Директор департамента*";TDSheet!$B:$B;0);3)
А на листе "Опись" в любой ячейке ввести формулу =начальник и если структура листа TDSheet особо менятся не будет - в этой ячейке всегда будет Ваш начальник. По аналогии можно поступить и с остальными.
Название: Re: Списки в Excel
Отправлено: hammer7 от 17.05.2009, 11:10
Спасибо, kitman, все правильно работает, но этот начальник должен сам выскакивать в столбце Р листа Опись! и ФИО этого начальника должна быть связана со столбцом О этого же листа.
Кроме Директора департамента есть еще управления, отделы, службы и т.д. со своими начальниками.

С этим пунктом проще всего - через ВПР все решается, а вот с остальными ...  ???
Название: Re: Списки в Excel
Отправлено: hammer7 от 17.05.2009, 13:02
В принципе, задача сводится к:
1. Создать выпадающий список в столбце О, выбрав из листа со штаткой(столбец А) только текстовые значения, без пустых строк и числовых значений.
2. Автоматизировать присвоение имен группам ячеек(подчиненных сотрудников), "принадлежащим" каждому подразделению, либо по названию подразделения(желательно), либо по фамилии руководителя для дальнейшего создания списков в столбце Q. По возможности(не обязательно), нужно учесть структуру (департамент-управление-отдел), т.е. отдел входит в состав и управления(если есть), и департамента; управление - в департамент.

Раньше не сталкивался с подобными задачами - у меня мозг кипит, а вам-спецам, думаю, по силам. Почитав форум, вижу, что здесь ассы Екселя!!!   ;)
Название: Re: Списки в Excel
Отправлено: IKor от 17.05.2009, 19:20
Посмотрите вложенный файл - комментарии внутри.
Название: Re: Списки в Excel
Отправлено: hammer7 от 17.05.2009, 20:13
Цитата: IKor от 17.05.2009, 19:20
Посмотрите вложенный файл - комментарии внутри.

Выше всяких похвал!!! Огромное спасибо, все работает!!!
Есть 2 маленьких но:
1. Нужно вручную ставить крестики. В TDSheet порядка 1000 строк, нужно поставить около 80-ти крестиков(и так после каждого мало-мальского изменения в штатке-листы со штаткой планирую менять на новые) - очень нудное занятие, требующее внимательности исполнителя, а хотелось бы исключить человеческий фактор.
2. Шаврин остался один. Таблица пришла немного не по маске.

P.S. Сам не люблю объединенные ячейки, специально оставил в том виде, в котором штатка приходит из кадров (выборка из 1С).

Буду признателен за доведение таблицы до надлежащего вида!
Название: Re: Списки в Excel
Отправлено: IKor от 17.05.2009, 22:14
Цитата: hammer7 от 17.05.2009, 20:13
1. Нужно вручную ставить крестики. В TDSheet порядка 1000 строк, нужно поставить около 80-ти крестиков(и так после каждого мало-мальского изменения в штатке-листы со штаткой планирую менять на новые) - очень нудное занятие, требующее внимательности исполнителя, а хотелось бы исключить человеческий фактор.
Пришлось поломать голову - но я придумал как отделить овец от козлищ...
Если используется не универсальный критерий (т.е. реальная таблица сложнее), то нужно думать дальше.

Цитата: hammer7 от 17.05.2009, 20:13
2. Шаврин остался один. Таблица пришла немного не по маске.
В выложенном варианте Шаврин - единственный сотрудник Упрпавления по работе со СМИ. По логике он является начальником этого управления. Т.к. у него нет подчиненных (никто не записан сразу ниже него), то список получателей в данной строке пуст. Если нужно, то исправить формулу получателей, чтобы в ней отображался Шаврин просто, но тогда и остальные начальники окажутся в соответствующих списках.
Название: Re: Списки в Excel
Отправлено: Waalan от 17.05.2009, 22:42
Помогите пож с проблемкой: необходимо автоматизировать составление плана по дате заказа, т.е. при появлении заказа согласно проставленной даты в диспетчере он попадал в план вместе с другими характеристиками этого заказа, но при этом нужно учесть очередность (если она есть), т.е. заказ с очередностью 1 должен встать на первую строку плана, с "2" - на вторую и т.д., если очередность не проставлена - то по очереди как есть (без разницы).
Название: Re: Списки в Excel
Отправлено: hammer7 от 17.05.2009, 22:54
Цитата: IKor от 17.05.2009, 22:14
Цитата: hammer7 от 17.05.2009, 20:13
1. Нужно вручную ставить крестики. В TDSheet порядка 1000 строк, нужно поставить около 80-ти крестиков(и так после каждого мало-мальского изменения в штатке-листы со штаткой планирую менять на новые) - очень нудное занятие, требующее внимательности исполнителя, а хотелось бы исключить человеческий фактор.
Пришлось поломать голову - но я придумал как отделить овец от козлищ...
Если используется не универсальный критерий (т.е. реальная таблица сложнее), то нужно думать дальше.

:D :D :D Супермастер!!!
Я ошибся! Стал проверять, оказалось в штатке 3054 строки, формула посчитала - 2577(!) крестиков нужно было поставить
:o
IKor, благодаря Вам, необходимость в этом отпала. Наверняка, можно сделать так, чтобы все формулы находились на дополнительном листе (исполнителям сложно объяснить что такое ЕКСЕЛЬ, не говоря уже о функциях, формулах, именах и т.д., присваивать одно имя, думаю, научу)

Цитата: IKor от 17.05.2009, 22:14
Цитата: hammer7 от 17.05.2009, 20:13
2. Шаврин остался один. Таблица пришла немного не по маске.
В выложенном варианте Шаврин - единственный сотрудник Упрпавления по работе со СМИ. По логике он является начальником этого управления. Т.к. у него нет подчиненных (никто не записан сразу ниже него), то список получателей в данной строке пуст. Если нужно, то исправить формулу получателей, чтобы в ней отображался Шаврин просто, но тогда и остальные начальники окажутся в соответствующих списках.

На самом деле, у него есть подчиненные - строки 36 и 45.

Немного потестирую, проверю как будет функционировать, но уже вижу - в случае ваканта начальника ...
Название: Re: Списки в Excel
Отправлено: IKor от 17.05.2009, 23:25
Зеленый столбец придется оставить на листе Журнала - иначе не будет гарантии, что охвачены все строки журнала.

Я честно не вижу подчиненных у Шаврина, но Вам, разумеется виднее ;)

В случае вакантной начальственной должности желательно сохранить строку с названием должности, но без ФИО (или с TBA в качестве последней)
Название: Re: Списки в Excel
Отправлено: hammer7 от 18.05.2009, 00:05
Шаврин, по-хорошему, должен быть в 37 строке, сдвигая все остальные вниз
Название: Re: Списки в Excel
Отправлено: hammer7 от 18.05.2009, 00:13
Цитата: IKor от 17.05.2009, 22:14
Если используется не универсальный критерий (т.е. реальная таблица сложнее), то нужно думать дальше.

Есть проблемы со списком подчиненных - некоторым "маленьким начальникам" дана почти неограниченная власть - уж очень много подчиненных у них (и свои, и чужие).
Файлик отправил на мыло, не сочтите за труд.  :-\
Название: Re: Списки в Excel
Отправлено: IKor от 18.05.2009, 01:15
Ответил по почте.
Чтобы не возникало такой ошибки формулу в последнем столбце ДопТаблицы
=ЕСЛИ($A2="";"";СЧЁТЗ(СМЕЩ(Журнал;$A2;3;ЕСЛИ(ЕЧИСЛО($A3);$A3;ЧСТРОК(Журнал))-$A2;1)))
можно заменить на
=ЕСЛИ($A2="";"";СЧЁТЗ(СМЕЩ(Журнал;$A2;3;ЕСЛИ(ЕЧИСЛО(СМЕЩ($A2;1;0));(СМЕЩ($A2;1;0);ЧСТРОК(Журнал))-$A2;1)))
и протянуть по всему столбцу
Название: Re: Списки в Excel
Отправлено: IKor от 18.05.2009, 01:37
2 Waalan

Если в дополнительном столбце на листе Диспетчер вы сложите числа:
=E4+(10-F4)*10^-2+СТРОКА()*10^-5
или: =дата_заказа + (очередность 3 => 2 => 1=> пустой)*10^-2 + Номер_строки*10^-5
То в новом столбце появяться числа [пока не] отсортированные [почти] так как Вам нужно.

После этого на любом другом листе Вы сможете отсортировать список по возрастанию с помощью функции:
=НАИМЕНЬШИЙ(ДопСтолбец;СТРОКА()-Const), здесь СТРОКА()-Const выполняет роль счетчика по строкам: 1, 2, 3, ... k
А функция ПОИСКПОЗ(НАИМЕНЬШИЙ(ДопСтолбец;СТРОКА()-Const);ДопСтолбец;0) вернет список номеров строк в дополнительном столбце, отсортированный по возрастанию значений

Далее с помощью функции ИНДЕКС() Вы легко сможете записать любой нужный ВАМ столбец таблицы диспетчера в нужной последовательности.
Название: Re: Списки в Excel
Отправлено: hammer7 от 18.05.2009, 10:46
Цитата: IKor от 18.05.2009, 01:15
Ответил по почте.
Чтобы не возникало такой ошибки формулу в последнем столбце ДопТаблицы
=ЕСЛИ($A2="";"";СЧЁТЗ(СМЕЩ(Журнал;$A2;3;ЕСЛИ(ЕЧИСЛО($A3);$A3;ЧСТРОК(Журнал))-$A2;1)))
можно заменить на
=ЕСЛИ($A2="";"";СЧЁТЗ(СМЕЩ(Журнал;$A2;3;ЕСЛИ(ЕЧИСЛО(СМЕЩ($A2;1;0));(СМЕЩ($A2;1;0);ЧСТРОК(Журнал))-$A2;1)))
и протянуть по всему столбцу

Огроменное спасибо!!! Вы очень помогли мне!
На почту кинул образцы ошибочек. Вторая формула не работает.
Название: Re: Списки в Excel
Отправлено: Fanatik от 18.05.2009, 11:36
Как сделать что бы при изменении заголовка списка подставлялось определёное значение в формулу?
Название: Re: Списки в Excel
Отправлено: IKor от 18.05.2009, 17:01
Цитата: Fanatik от 18.05.2009, 11:36
Как сделать что бы при изменении заголовка списка подставлялось определёное значение в формулу?
Вы это имели в виду?
Название: Re: Списки в Excel
Отправлено: Fanatik от 18.05.2009, 19:40
да спасибо
Название: Re: Списки в Excel
Отправлено: Waalan от 18.05.2009, 20:33
Цитата: IKor от 18.05.2009, 01:37
2 Waalan

Если в дополнительном столбце на листе Диспетчер вы сложите числа:
=E4+(10-F4)*10^-2+СТРОКА()*10^-5
или: =дата_заказа + (очередность 3 => 2 => 1=> пустой)*10^-2 + Номер_строки*10^-5
То в новом столбце появяться числа [пока не] отсортированные [почти] так как Вам нужно.
Не получается чего то, если не сильно трудно можете файлик прикрепить с примером...
Название: Re: Списки в Excel
Отправлено: IKor от 18.05.2009, 23:56
Цитата: Waalan от 18.05.2009, 20:33
Не получается чего то, если не сильно трудно можете файлик прикрепить с примером...
Мне не очень понятно, что Вы должны получить в результате, но я имел в виду сортировку строк по сложному (составному) условию с помощью функции НАИМЕНЬШИЙ() - посмотрите вложенный файл.
Чтобы избежать вывода ошибок - формулы можно дополнить проверками, но для скорости я их опустил.
Название: Re: Списки в Excel
Отправлено: sayak от 03.06.2009, 09:25
Доброго времени суток!

Прошу помочь с задачкой:

Имеется таблица с итоговой колонкой. В следующей колонке обычно вручную забивается простенькая формула - цифра из итоговой колонки, умноженная на коэффициент.
Коэффициентов 3 - X, Y, и Z.
Условие таково - на X умножается только самая большая цифра ("1-е место"), на Y - вторая по величине ("2-е место"), остальные - на Z ("прочие").


Надоело это дело забивать вручную и решил автоматизировать. Однако...

С X я справился быстро, с условием выбора одного из двух:
=ЕСЛИ("ячейка"=МАКС(диапазон);"ячейка"*X;"ячейка"*Z)

А как выбирать из трех? Тем более, что Y должен быть вторым по величине, а в Хелпе экселя я не нашел ничего похожего.

P.S. Также нужно как-то отметить ячейки с результатами по X и Y - в колонке с коэффициентами (разными цветами), или текстом "1-е место", "2-е место" в соответствующих строчках следующей...
Название: Re: Списки в Excel
Отправлено: IKor от 03.06.2009, 16:40
Цитата: sayak от 03.06.2009, 09:25
А как выбирать из трех? Тем более, что Y должен быть вторым по величине, а в Хелпе экселя я не нашел ничего похожего.
P.S. Также нужно как-то отметить ячейки с результатами по X и Y - в колонке с коэффициентами (разными цветами), или текстом "1-е место", "2-е место" в соответствующих строчках следующей...
Обратите внимание на функцию НАИБОЛЬШИЙ(массив,k-тый) и условное_форматирование (https://msexcel.ru/content/view/116/2/) (меню ФОРМАТ).
Если приложите кусочек Вашей таблицы, то можно будет показать на этом примере.
Название: Re: Списки в Excel
Отправлено: sayak от 03.06.2009, 20:07
Цитата: IKor от 03.06.2009, 16:40
Цитата: sayak от 03.06.2009, 09:25
А как выбирать из трех? Тем более, что Y должен быть вторым по величине, а в Хелпе экселя я не нашел ничего похожего.
P.S. Также нужно как-то отметить ячейки с результатами по X и Y - в колонке с коэффициентами (разными цветами), или текстом "1-е место", "2-е место" в соответствующих строчках следующей...
Обратите внимание на функцию НАИБОЛЬШИЙ(массив,k-тый) и условное_форматирование (https://msexcel.ru/content/view/116/2/) (меню ФОРМАТ).
Если приложите кусочек Вашей таблицы, то можно будет показать на этом примере.
Функция НАИБОЛЬШИЙ(массив,k-тый) - то, что доктор прописал. условное_форматирование (https://msexcel.ru/content/view/116/2/) - я, честно говоря, не понял :( Надо будет почитать повнимательнее - в каких случаях применять.

Спасибо большое за совет.

Кстати, с приклеплением таблички проблемы какие-то у мну. Закинул на сервак http://filebar.kg/files/451803066/tips rating.zip (http://filebar.kg/files/451803066/tips%20rating.zip). Если есть замечания - ну, я не знаю - неизящная формула, можно сделать лучше - жду с нетерпением :)

Название: Re: Списки в Excel
Отправлено: kimtan от 03.06.2009, 21:18
sayak
Насчет изящества Вашей формулы не мне судить, просто прилагаю уже известную здесь многим альтернативу :)
P.S. Исправленному верить...
Название: Re: Списки в Excel
Отправлено: IKor от 03.06.2009, 21:49
2 sayak Посмотрите вложение.
Название: Re: Списки в Excel
Отправлено: sayak от 03.06.2009, 23:28
Цитата: IKor от 03.06.2009, 21:49
2 sayak Посмотрите вложение.
Вы программист, да? То же самое, но - изящней  :o Из-за отвычки пришлось долго вкуривать :)

И кстати, как вы выделили цветом первые 2 места??? Можно это тоже формулой забить?
Название: Re: Списки в Excel
Отправлено: BOBS от 04.06.2009, 21:14
вечер добрый,подскажите,пожайлуста,более менее толковую ссылочку на самоучитель ексель,раздел макросы.Буду очень признателен ???
Название: Re: Списки в Excel
Отправлено: IKor от 04.06.2009, 22:33
Цитата: sayak от 03.06.2009, 23:28
И кстати, как вы выделили цветом первые 2 места??? Можно это тоже формулой забить?
Я же писал выше - условное_форматирование (https://msexcel.ru/content/view/116/2/) (меню ФОРМАТ). Посмотрите - если появятся вопросы - ообращайтесь.
P.S. Я совсем не программист. :)
Название: Re: Списки в Excel
Отправлено: GhostKU от 05.06.2009, 13:07
Есть таблица товаров (Товар - Поставщик - Цена) обновляемая периодически.
Есть таблица покупок с теми же полями соответственно в таблице покупок нужно Выбрать товар из списка доступных (тут проблема с повторяющимися записями), а потом выбрать поставщика у которого есть данный товар. Я пробовал найденной здесь же решение с использование ДВССЫЛ но так как одинаковые товары идут не по порядку то ДВССЫЛ выдает ошибку при попытке адрессовать такой диапазон.
Нуждаюсь в совете.
Название: Re: Списки в Excel
Отправлено: sayak от 05.06.2009, 21:49
Цитата: IKor от 04.06.2009, 22:33
Цитата: sayak от 03.06.2009, 23:28
И кстати, как вы выделили цветом первые 2 места??? Можно это тоже формулой забить?
Я же писал выше - условное_форматирование (https://msexcel.ru/content/view/116/2/) (меню ФОРМАТ). Посмотрите - если появятся вопросы - ообращайтесь.
P.S. Я совсем не программист. :)
Вопросы появились! Полностью скопировал форматирование, но:
Сперва у меня закрашивалась не вся строка, а первая ячейка строки, а именно - фамилия.
А потом и вовсе никакой реакции...

100 раз сравнивал - вроде все так сделал, как у вас было...

Файл прилагается. Прошу указать на ошибку.
Название: Re: Списки в Excel
Отправлено: IKor от 05.06.2009, 22:32
Цитата: sayak от 05.06.2009, 21:49
Прошу указать на ошибку.
Порядок действий для исправления ошибки:
1. Выделяем ячейку A3 (или всю строку 3 по ширине таблицы)
2. Заходим в меню ФОРМАТ / условное_форматирование (https://msexcel.ru/content/view/116/2/)
3. Видим условие1 =$AH$3=НАИБОЛЬШИЙ($AH$3:$AH$68;1) и удаляем выделенный красным символ $
4. Аналогичное изменение проводим со вторым условием.
5. Сохраняем изменения и выходим из Условного форматирования.
6. Выделенную ячейку (или строку) копируем в память.
7. Выделяем ячейку A5 (или всю пятую строку в пределах таблицы) и копируем в нее только_форматы (ПКМ / Специальная вставка)
8. Открываем условное_форматирование (https://msexcel.ru/content/view/116/2/) и убеждаемся, что условие 1 выглядит так =$AH4=НАИБОЛЬШИЙ($AH$3:$AH$68;1)
9. Копируем форматы ячеек (строк) с откорректированным условным форматированием в оставшиеся строки.
Название: Re: Списки в Excel
Отправлено: IKor от 06.06.2009, 10:09
2 GhostKU
Посмотрите вложение.
Получилось достаточно неуклюже из-за большого количества доп. столбцов.
Если быть точно увереным, что количество товаров всегда меньше количества продавцов одного товара, то таблицу выбора продавцов каждого товара можно несколько модифицировать и упростить.
Но лучшее решение - после модификации базовой таблицы сортировать ее по товарам - так, чтобы все поставщики одного товара шли бы подряд. В этом случае отпадет необходимость в извращениях и формулах массива и все можно будет решить просто с помощью функции СМЕЩ() (посмотрите решение в этой же теме выше).

В файле используются формулы массива - для их ввода нажимайте CTRL+SHFT+ENTER вместо ENTER
Название: Re: Списки в Excel
Отправлено: sayak от 06.06.2009, 10:10
Цитата: IKor от 05.06.2009, 22:32
Спасибо, помогло. Не пойму, почему раньше не срабатывало. Вроде все так же и делал? :-\
Название: Re: Списки в Excel
Отправлено: IKor от 06.06.2009, 10:23
2 sayak
В Вашем случае Вы во всех строках сравнивали с первым местом значение из первой строки $AH$3 - знак $ перед тройкой при копировании в другую ячейку сохранял ссылку именно на третью строку. После модификации формулы стали сравниваться значения текущей строки, т.к. при копировании в пятую строку ссылка $AH3 превращалась в $AH5
Название: Re: Списки в Excel
Отправлено: sayak от 06.06.2009, 14:42
Цитата: IKor от 06.06.2009, 10:23
2 sayak
В Вашем случае Вы во всех строках сравнивали с первым местом значение из первой строки $AH$3 - знак $ перед тройкой при копировании в другую ячейку сохранял ссылку именно на третью строку. После модификации формулы стали сравниваться значения текущей строки, т.к. при копировании в пятую строку ссылка $AH3 превращалась в $AH5
Понял, пасиб :)
Название: Списки в Excel
Отправлено: teymur от 25.09.2009, 11:56
mne nujna forula,cto bi ya zadaval nachalnoe chislo,naprimer 00001 i konechnoe 00100,a programma sama zapolnila promejutochnie zancheniya,t.e oooo2,00003,00004....do 00100
pomogite,kto chem mojet...
Название: Re: Списки в Excel
Отправлено: TimSha от 25.09.2009, 16:36
Уже изобретено, смотрим:
2003 - правка, заполнить, прогрессия.
2007 - редактирование, заполнить, прогрессия.
Название: Re: Списки в Excel
Отправлено: Bulls от 10.10.2009, 16:03
Помогите связать данные с выпадающего списка (ячейка В3) с наличием /скрытием строк по номерам платежей.  Заранее спасибо!
Название: Списки в Excel
Отправлено: Juli от 12.10.2009, 12:34
Здравствуйте, у меня следущая задача:
имеется документ, состоящий из 2 листов, на одном листе общая таблица - прайс, в котором порядка 1500 артикулов, у каждого артикула есть размер, всего разновидностей размеров около 10, необходимо, чтобы на втором листе, при выборе из списка необходимого типа размера (причём, чтобы был список уникальных записей, например, если подряд идёт 100 артикулов одного размера, чтобы в списке не 100 раз высвечивался один размер, а всего 1 раз) применить макрос, который выведет все значения данного размера и выведет данные только по выбранным столбцам

заранее спасибо за помощь
Название: Re: Списки в Excel
Отправлено: TimSha от 12.10.2009, 17:25
Juli! Лучше обратиться в общий форум и, главное, приложить небольшой пример, т.к. "заочно" вопрос не решить. Хотя, думаю, есть подходы: расширенный фильтр, свобная таблица. А если именно макрос и писать его для вас с нуля ничего не видя, согласитесь, это по силам телепатам от XL...
Название: Списки в Excel
Отправлено: Juli от 13.10.2009, 06:22
На самом деле, мне бы понять как это сделать, именно список с уникальными значениями. И как потом вывести на другой лист необходимый перечень значений, а макрос я сама запишу))
Название: Re: Списки в Excel
Отправлено: IKor от 13.10.2009, 13:03
2 Juli
Цитата: IKor от 15.01.2009, 22:31
Список уникальных значений может формироваться и без макросов (при помощи дополнительного столбца и функции НАИМЕНЬШИЙ()) - посмотрите, например сообщение по ссылке в заголовке цитаты.
Название: Re: Списки в Excel
Отправлено: Juli от 14.10.2009, 09:14
Спасибо!
Название: Re: Списки в Excel
Отправлено: IKor от 30.10.2009, 15:20
Правильно ли я понимаю, что требуется вычленить из списка текстовых значений вычленить уникальные текстовые значения?
Если это требуется делать на постоянной основе, то посмотрите "формульное" решение с дополнительным столбцом и функцией НАИМЕНЬШИЙ().
Если же это разовая операция - то воспользуйтесь Расширенным фильтром (меню Данные / Фильтр / Расширенный фильтр)
Название: Re: Списки в Excel
Отправлено: Disper от 31.10.2009, 23:14
Добрый день.
К сожалению прочитав советы по решению аналогичных задач, не смог справиться с тем что необходимо сделать мне.
(http://img203.imageshack.us/img203/5290/excel.png)
Необходимо чтобы ячейка принимала тот цвет, который был выбрал из выпадающего списка. Спасибо.
Название: Re: Списки в Excel
Отправлено: IKor от 31.10.2009, 23:31
С помощью именованного списка (меню ВСТАВКА / ИМЯ / ПРИСВОИТЬ) ограничиваем значения, которые можно ввести в ячейку (см. меню ДАННЫЕ / ПРОВЕРКА / список), => получается выпадающий список.
С помощью условного форматирования (см. меню ФОРМАТ / условное_форматирование (https://msexcel.ru/content/view/116/2/)) выбираем цвета для каждого из возможных выборов пользователя (в Excel2000 есть только три условия для одной ячейки - насколько мне известно, в Excel2007 их больше)
Название: Re: Списки в Excel
Отправлено: Disper от 31.10.2009, 23:47
Цитата: IKor от 31.10.2009, 23:31
С помощью условного форматирования (см. меню ФОРМАТ / условное_форматирование (https://msexcel.ru/content/view/116/2/)) выбираем цвета для каждого из возможных выборов пользователя (в Excel2000 есть только три условия для одной ячейки - насколько мне известно, в Excel2007 их больше)
Спасибо. Как раз то, что нужно.
Название: Re: Списки в Excel
Отправлено: Morgan от 05.11.2009, 17:44
Здравствуйте.
Что я имею:
Итак, есть строка "прогноз", она считает совпадения, значений "1";"Х";"2" которые находятся в более чем 65 столбцах. при каждом совпадение, в ячейке появляется единица, и в конечной ячейке считается сумма единиц в строке. Таким образом меняя прогноз я могу отслеживать количество угаданных результатов по каждой строке.
Моя задача:
Сделать так что бы при вводе новых значений ячейки "1,Х" "1,2" и "Х,2" при совпадении хотя бы одного значения 1,Х или 2. Появлялась единица. Надеюсь понятно.
Буду очень признателен за любую помощь.
Картинки
здесь я ищу совпадения
(http://i033.radikal.ru/0911/20/3a9a15f3cfc7.jpg)
Здесь появляются результаты
(http://s49.radikal.ru/i125/0911/e3/4740f08a2310.jpg)
Название: Re: Списки в Excel
Отправлено: kimtan от 05.11.2009, 20:15
Morgan
Так?
Название: Re: Списки в Excel
Отправлено: Morgan от 05.11.2009, 20:34
Цитата: kimtan от 05.11.2009, 20:15
Morgan
Так?
Да! Все так!  :) Я ведь и не думал что в excel такое можно! Мне еще учится и учится.
Вам большое спасибо, помощь неоценимая.
PS
Это здорово!  :)
Название: Re: Списки в Excel
Отправлено: Morgan от 05.11.2009, 23:11
Цитата: kimtan от 05.11.2009, 20:15
Morgan
Так?
Вот только не пойму как менять в зеленом столбике значения. Мне ведь нужно самому менять. А то я на радостях только сейчас это проверил  :)
Название: Re: Списки в Excel
Отправлено: Morgan от 05.11.2009, 23:20
Ну вот подумал, и нашел способ. если убрать вот это "ЦЕЛОЕ(СЛЧИС()*6+1)" и поставить число от 1 до 6 то появится 1 из 6 вариантов прогноза. Это наверно единственый способ вставить свое значение.
Название: Re: Списки в Excel
Отправлено: Tory от 12.11.2009, 01:21
Здравствуйте.
Помогите, пожалуйста, решить задачу.

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

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

Вопрос в том, как существующую строчку в исходном файле заменить на список с уже выбранной нужной позицией.
Есть таблица соответствия (столбец А - исходное название должности, столбец Б - название, которое есть в списке).
Название: Re: Списки в Excel
Отправлено: IKor от 12.11.2009, 11:39
2 Tory

Дополнительно к имеющимся столбцам A и B создайте таблицу перевода (можно на отдельном листе или даже в отдельной книге - важно только, чтобы эта книга была открыта).
В левом столбце доп. таблицы напишите все возможные варианты названий должностей. В правом столбце напротив каждой должности укажите ее правильное название.
Назовите эту таблицу, например КоррДолжности (меню ВСТАВКА / ИМЯ / ПРИСВОИТЬ)
Затем вернитесь к столбцу A (c неправильными названиями должностей) и напротив каждой ячейки в столбце B запишите формулу
=ВПР(A1;КоррДолжности;2;0)
и "протяните" формулу по всему столбцу B.
Тогда в столбце B будут записаны значения из правого столбца доп. таблицы, ТОЧНО соответствующие значениям из столбца A.

Если доп. таблица находится в отдельном файле, то укажите полное имя таблицы
'C:\Мои файлы\Правильные названия.xls'!КоррДолжности

P.S. Тоже можно сделать и для закрытой внешней книги, но вместо [url=https://msexcel.ru/content/view/20/2/]ВПР(_) (http://msexcel.ru/content/view/20/2/)[/url] следует использовать конструкцию ИНДЕКС(...ПОИСКПОЗ()...)

=================
Update
Если потребуется, после формирования столбца B его можно скопировать и вставить ТОЛЬКО ЗНАЧЕНИЯ (правая кнопка мыши - специальная вставка) в столбец А - тогда столбец B станет не нужным и его можно будет удалить.
Название: Re: Списки в Excel
Отправлено: gamaun от 18.11.2009, 16:38
Всем привет!!!!!!!! Срочно очень нужна Ваша помощь!!!!! Проблема вот в чём: Имеется таблица на Листе 1, в которой в столбце А забиты в каждой строке индивидуальные номера (не порядковые!), в других, например 20-ти, столбцах напротив каждых индивидуальных номеров в ячейках забиты числовые значения, например 10 и 11, при чём у некоторых встречаются только 10, у некоторых только 11, у некоторых пустые ячейки, а так же и вперемешку встречаются! Пожалуйста, подскажите, как мне в другую таблицу такого же размера (например, на лист 2) извлечь индивидуальные номера, у которых встречается хоть одна 10, а в третью таблицу такого же размера (например, на лист 3) - у которых встречается хоть одна 11! Причём так что бы эти номера шли в других таблицах компактно, т.е. не оставалось пустых строк, что бы потом не заниматься перетаскиванием инд. номеров в ручную из ячейки в ячейку(исходная таблица большого размера) или скрывать или удалять пустые строки, т.к. размеры таблиц должны быть одинаковыми! Можно так же присылать примеры в Excel на мой адрес alex_sky@mail.ru Заранее благодарен!!!!!!!!
Название: Re: Списки в Excel
Отправлено: IKor от 18.11.2009, 20:38
2 Gamaun
1. Для каждого цифрового значения (10, 11 и т.д.) добавьте дополнительный столбец, в который записывайте номер строки, если в ней есть нужное число.
Пусть Ваша таблица находится в ячейках A2:F100
Запишем в ячейке G1 число 10
а в ячейке G2 формулу =ЕСЛИ(СЧЁТЕСЛИ($A2:$F2;G$2)>0;СТРОКА();"")
и скопируйте ее в остальные ячейки столбца G
то же самое сделайте для других цифровых значений (в столбцах H, I и т.д.).

2. Перейдите на лист2
и в ячейке A1 запишите формулу:
=ЕСЛИ(СТРОКА()>СЧЁТ(Лист1!$G:$G);"";ИНДЕКС(Лист1!A:A;НАИМЕНЬШИЙ(Лист1!$G:$G;СТРОКА());1))
а затем скопируйте эту формулу во все ячейки диапазона A1:G100
Эта формула вернет значения строк таблицы Листа1, в которых присутствует число 10.

3. Для листа 3 выполните аналогичные действия, за исключением ссылки на соответствующий столбец Лист1!$H:$H
Название: Re: Списки в Excel
Отправлено: forum от 19.11.2009, 13:42
Всем привет.
Объясните, плиз, как в таблице, (во вложении),
в столбце В (город) скопировать название города на ячейки расположенные ниже, НО до следующего города. Таблица на самом деле очень длинная, поэтому вручную копипастить мона очень долго.
Спасибо за помощь
Название: Re: Списки в Excel
Отправлено: IKor от 19.11.2009, 18:24
2 forum
Запишите в ячейке F2 формулу =ЕСЛИ(B2="";F1;B2) и скопируйте ее во все ячейки столбца А, кроме первой :)
Если нужно, затем скопируйте столбец F и вставьте только значения в столбец B (ПКМ => спец. вставка => только значения)
Название: Re: Списки в Excel
Отправлено: gamaun от 19.11.2009, 18:30
Ikor, большое спасибо за подсказку! Но у меня что-то не очень получается это приделать к своей таблице, или я уже заработался или что-то не то ввожу в формулу из-за количества областей или ещё что-то... Буду конкретен! На Листе1 индивидуальные номера забиты в нескольких массивах, начиная с B6:B55;B68:B117;B120:B169 и так далее, всего их 20 штук, в столбце AS присваиваются номера строк если встречается 10, но диапазон также получается, из-за конфигурации таблицы, из нескольких областей, начиная с AS6:AS55;AS68:AS117;AS120:AS169 и так далее, их тоже соответственно 20 штук. При чём в таблице, соответственно и в этих массивах по 25 строк(эксселевские, объединённые по две), только промежутки в столбцах между массивами идут с не объединёнными строками! Пожалуйста, помогите вернуть значения строк таблицы Листа1, в которых присутствует число 10 на Листе2 в массивы B6:B55;B68:B117;B120:B169 и так далее!!!!!! Моя благодарность не будет знать границ!!!!!!
Название: Re: Списки в Excel
Отправлено: forum от 19.11.2009, 19:01
Цитата: IKor от 19.11.2009, 18:24
2 forum
Запишите в ячейке F2 формулу =ЕСЛИ(B2="";F1;B2) и скопируйте ее во все ячейки столбца А, кроме первой :)
Если нужно, затем скопируйте столбец F и вставьте только значения в столбец B (ПКМ => спец. вставка => только значения)

Класс. Спасибо. Выручил
Название: Re: Списки в Excel
Отправлено: gamaun от 20.11.2009, 08:14
IKor! Прилагаю аналог файла с моей задачей!
Название: Re: Списки в Excel
Отправлено: IKor от 20.11.2009, 11:40
Гамаюн,
Вы немного не поняли - дополнительные столбцы нужно создавать около исходных таблиц; и к ним же привязываться из вновьсоздаваемых таблиц.
Посмотрите пример.

И примите мой добрый совет - постарайтесь избегать объединения ячеек - они существенно усложняют использование формул - это ложно понимаемая красота :)
Название: Re: Списки в Excel
Отправлено: gamaun от 20.11.2009, 16:22
IKor, огромное спасибо!!! Это уже ближе к желаемому результату. В Вашем примере в верхней таблице выбор по 10 идёт, а в нижней по 11! В том же файле я справа показал желаемый результат по 10 (только в место 10 что бы присваивалась 1, это в принципе не сложно). Как видите там после заполнения верхней таблицы начинается заполняться следующая. Как я упоминал ранее таких исходных таблиц идёт 20, соответственно что бы и заполнялись по критерию 10 начиная с первой последующие на др. листе. Там могут быть такие варианты, что в исходных таблицах 10 будет совсем мало, соответственно может даже и одна исходящая таблица будет заполнена не полностью, а может быть и очень много 10 и исходящие таблицы могут быть заполнены и полностью все 20! Если Вы поможете по 10 сделать так, как отображено в желаемом результате, то будет супер! А с выбором по 11 и заменой на 7, я тогда и сам скорее всего справлюсь! Заранее Вам огромное спасибо!!!
P.S. Ячейки я объединил не для красоты. В оригинале файла 15 листов с разными типами и размерами таблиц, в очень многих таблицах напротив инд. номера заполняются две строки. Немного поломав голову над заполнением таких таблиц и удобством протягивания формул по строкам, я подумал,что мне было бы проще в остальных таблицах просто сдвоить строки.
Название: Re: Списки в Excel
Отправлено: gamaun от 20.11.2009, 17:18
IKor, огромное спасибо!!! Я уже кажется разобрался! :D Дело пошло дальше!!! Ещё раз огромное спасибо! Если вдруг возникнут очередные сложности снова обращусь к Вам за помощью! Надеюсь не откажете! ::)
Название: Re: Списки в Excel
Отправлено: gamaun от 22.11.2009, 12:38
IKor, снова нужна Ваша помощь! В файле Парам. В столбце К в массив К19:К49 забиты месяца, а в столбце  L  в массив L19:L49 забиты числа месяцев , эти два массива будут периодически редактироваться, т.е. меняться как и числа, так месяцы, а так же и их количество в массиве, в примере массивы максимально возможно заполнены. В файле Вопрос 3 на Листе1 всё та же исходная таблица, а на Листе2 такая же таблица, в которую теперь в зависимости от месяца в ячейке R1 (например сентябрь), нужно  извлечь только числовые значения (любые, будь то 1,2,3,...10,11...и т.д.) из столбцов массивов G6:AK54, G68:AK116 и т.д. соответствующих числам (например сентября), указанным в файле Парам. столбце  L  в массиве L19:L49, относящихся к сентябрю! В файле Вопрос 3 Лист2 в таблицы из исходных извлечь числовые значения  у меня получилось, а как сделать чтобы извлекались числовые значения только из столбцов соответствующих, указанным  в файле Парам. столбце  L  в массиве L19:L49, числам сентября я не знаю!  И то заполнив все строки в исходной таблице числами (столбец АК, значение 33) в итоговую таблицу извлекаются не все строки, 7 пропущено, как и в файле Вопрос 2(_____) при выборе 10 пропускает 6 строк (В оригинальном файле из 100 строк с данными пропускает 11 строк). Почему так происходит понять не могу! Рассчитываю на Вашу помощь!
Название: Re: Списки в Excel
Отправлено: kimtan от 22.11.2009, 14:35
Я прошу прощения, что вмешиваюсь (прежде всего у Уважаемого IKor), руки просто чесались.
Посмотрел пока файл Вопрос 3, там можно немного оптимизировать и ускорить работу формул. Столбец B не трогал.
Обещаю больше не вмешиваться  :)
Название: Re: Списки в Excel
Отправлено: Vetrennay от 22.11.2009, 15:27
Здравствуйте всем! Прошу помощи у вас в двух небольших вопросах, самостоятельно решить не получается, а сроки сдачи задания поджимают,последняя надежда на вас :)
Есть  таблица  для поддержки работы с постоянными клиентами (в помощь продацу-консультанту в сетевом маркетинге),не могу реализовать следующее:
1) Каким образом настроить перенос формула в ячейке J4, при добавлении последующих дат покупки?
2)Аналогично с добавлением клиента на первый лист. можно ли сделать так,что бы формулы переносились при добавлении нового клиента?

Файл прикреплен, очень жду ответа!

Название: Re: Списки в Excel
Отправлено: kimtan от 22.11.2009, 16:30
Vetrennay
Вы так имели ввиду?
Название: Re: Списки в Excel
Отправлено: Vetrennay от 22.11.2009, 21:48
Наверно я не достаточно точно описала то что мне необходимо. Изначально задание было следующее: Разработать таблицу для работы с постоянными клиентами. Постоянными считаются клиенты,у которых интервалы между заказами не превашаю  2-х месяцев.Такие клиенты имеют скидки.Если накопленая сумма покупок достигает некой заданной,клиента переводят в VIP-клиенты,для которых скидки больше.Таблица должна содержать список клиентов.Для каждого клиента в ней регистрируются все сделанные заказы (дата и сумма),а так же подсчитывается общая сумма заказов.Таблица должна обращать внимание консультанта на:
-на клиентов которым до конца 2-месячного срока осталось 2 недели (позвонить)
-2 месячный срок истек
-накопденная сумма достигла требуемого для перевода в VIP клиенты уровня.
Обеспечить настройку уонкретной суммы,по достижении которой переводят в VIP

Предполагалось что при добавлении нового клиента, мы создаем новый лист и данные о клиенте (статус,сумма заказа и напоминание) отображается в сводной таблице, возможно ли это реализовать?
И второе,как сделать так что бы при добовлении новой даты покупки, в ячейке разницы между заказами в днях,отображалось разница в днях и проверялись три нижеуказанных условия?
Название: Re: Списки в Excel
Отправлено: kimtan от 22.11.2009, 22:42
Цитата: Vetrennay от 22.11.2009, 21:48
Постоянными считаются клиенты,у которых интервалы между заказами не превашаю  2-х месяцев.Такие клиенты имеют скидки.
Давайте-ка по порядку: разница в днях считается по последним двум заказам?
Если так, то:
В ячейку J4 =ПРОСМОТР(9E+307;$B$3:$B$24)-ПРОСМОТР(9E+307;$B$3:ИНДЕКС($B$3:$B$24;ПОИСКПОЗ(9E+307;$B$3:$B$24)-1))
Название: Re: Списки в Excel
Отправлено: Vetrennay от 22.11.2009, 22:48
да!это работает :)
Название: Re: Списки в Excel
Отправлено: kimtan от 22.11.2009, 23:01
Судя по Вашему ответу, задача решена?
Название: Re: Списки в Excel
Отправлено: Vetrennay от 22.11.2009, 23:09
А в сводной таблице не возможно автоматически переносить формулы для ячеек, при добавлении нового клиента?
Название: Re: Списки в Excel
Отправлено: kimtan от 22.11.2009, 23:17
Попробуйте в файле который я прикреплял скопировать/переместить в конец, какой-то лист, например Клиент 3, а затем переименуйте его в Клиент 4. Можете там занести какую-нибудь другую фамилию. А затем перейдите на лист Сводный и посмотрите что получилось.
Название: Re: Списки в Excel
Отправлено: Vetrennay от 22.11.2009, 23:32
к сожелению у меня не получается ???
спасибо огромное за то,что получилось!!! надеюсь этого будет достаточно для защиты моей работы :)
Название: Re: Списки в Excel
Отправлено: kimtan от 22.11.2009, 23:41
Вот, что получилось у меня:
Название: Re: Списки в Excel
Отправлено: Vetrennay от 22.11.2009, 23:56
спасибо огромное :) теперь я точно сдам!
Название: Re: Списки в Excel
Отправлено: gamaun от 24.11.2009, 14:05
Уважаемый kimtan! Я изначально обратился за помощью ко всем кто сможет мне помочь! Первым на призыв о помощи откликнулся IKor! И за это ему ещё раз огромное спасибо! kimtan! я просмотрел файл, Вами скорректированный. И Вам огромное спасибо! Но вот проблемы описанные в том сообщении (ответ#135) у меня так и не получилось решить! Я уже мозг себе сломал, пытаясь решить эти проблемы! Может не только уважаемые IKor и kimtan смогут мне помочь, а кто нибудь ещё, сталкивавшийся с подобными проблемами, или, имеющий профессиональные навыки работы в Excel! Но в первую очередь сейчас я обращаюсь за помощью к Вам IKor и kimtan, т.к. Вы уже больше других в курсе моих проблем!
Название: Re: Списки в Excel
Отправлено: IKor от 25.11.2009, 16:54
2 Gamaun
Решение Вашей задачи можно разбить на три этапа:

1. Получение из внешнего файла информации об "используемых" датах указанного месяца
- Решений может быть много, я выбрал вариант с дополнительной строкой и использованием функции
=ЕСЛИ(СУММПРОИЗВ((первый_столбец=указанный_месяц)*(второй_столбец=текущая_дата))>0;текущая_дата;"").
Обратите внимание на то, что внешний файл должен быть ОТКРЫТ => К сожалению, у меня не сработало не требующее открытие файла решение с конструкцией =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ()))) - по всей видимости из-за особенностей открытия файлов *.xlsx в Excel2003.
Таким образом, в дополнительной строке показаны только те даты выбранного месяца, которые присутствуют во внешнем файле.

2. Для каждой из строк таблицы подсчитать количество числовых значений, находящихся в столбцах с найденными ранее датами.
Здесь я предлагаю использовать {формулу массива}, для ввода которой вместо ENTER требуется нажимать CTRL+SHFT+ENTER.
Её суть в следующем: для всех ячеек текущей строки формируем виртуальный диапазон значений по следующим условиям:
Если значение текущего_столбце_доп._строки не равно пустоте и значение текущего_столбца_текущей_строки больше нуля, то принимаем текущее значение текущей_строки, в противном случае - пустоту.
После чего при помощи функции СЧЁТ() подсчитываем количество чисел в виртуальном диапазоне.
И если количество чисел больше нуля, то выводим в ячейку дополнительного столбца номер строки, а в противном случае - пустоту.

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

3. В дополнительной таблице вывести только те строки, которые попали в выборку дополнительного столбца.
Решение этой задачи извествно - используйте функцию НАИМЕНЬШИЙ().
Название: Re: Списки в Excel
Отправлено: gamaun от 25.11.2009, 19:40
IKor, спасибо!!! Завтра попытаюсь разобраться!
Название: Re: Списки в Excel
Отправлено: MishaOkrushko от 30.11.2009, 21:05
Здравствуйте!
Я только начал разбираться с Excel`ем и у меня возникла задача, с которой я не могу справится, был бы весьма признателен , если бы кто -нибудь помог.

" Есть массив с данными (n x m) R=n, C=m, в колонках есть повторения. Вопрос: " Как можно получить элемент из строки, по заданым 2, 3, ... из неё значениям? Есть какая-нибудь функция(аналог ВПР(_) (https://msexcel.ru/content/view/20/2/)), которая это просто реализует?"
Название: Re: Списки в Excel
Отправлено: IKor от 30.11.2009, 21:53
Цитата: MishaOkrushko от 30.11.2009, 21:05
" Есть массив с данными (n x m) R=n, C=m, в колонках есть повторения. Вопрос: " Как можно получить элемент из строки, по заданым 2, 3, ... из неё значениям? Есть какая-нибудь функция(аналог [url=https://msexcel.ru/content/view/20/2/]ВПР(_) (http://msexcel.ru/content/view/20/2/)[/url]), которая это просто реализует?"
Не вполне понятен вопрос что нужно сделать. Могли бы Вы приложить пример (без секретной информации) с тем, что у Вас есть и с тем, чего требуется достичь. Будет проще разобраться.

На вскидку: попробуйте сформировать дополнительный столбец, содержащий все столбцы вашей таблицы. Обрабатывайте его с помощь функций ВПР(_) (https://msexcel.ru/content/view/20/2/) или ПОИСКПОЗ(), агализируйте результат, а уже потом выдавайте то, что требуется.
Название: Re: Списки в Excel
Отправлено: MishaOkrushko от 30.11.2009, 22:31
quote author=IKor link=topic=6.msg14069#msg14069 date=1259607218]
Не вполне понятен вопрос что нужно сделать. Могли бы Вы приложить пример (без секретной информации) с тем, что у Вас есть и с тем, чего требуется достичь. Будет проще разобраться.

На вскидку: попробуйте сформировать дополнительный столбец, содержащий все столбцы вашей таблицы. Обрабатывайте его с помощь функций [url=https://msexcel.ru/content/view/20/2/]ВПР(_) (http://msexcel.ru/content/view/20/2/)[/url] или ПОИСКПОЗ(), агализируйте результат, а уже потом выдавайте то, что требуется.
[/quote]

Да, объединение столбцов и ВПР решает проблему в частном случае, но в общем задача остается.
Задача поиска в базе данных(Excel) по нескольким атрибутам.

PS Файл прилагается. Надеюсь, изложил проблему понятнее.

Название: Re: Списки в Excel
Отправлено: IKor от 30.11.2009, 23:16
Цитата: MishaOkrushko от 30.11.2009, 22:31
PS Файл прилагается. Надеюсь, изложил проблему понятнее.
Цитировать
Задача: получить значение, к примеру,  из ячейки J14, это значение определяется точно 2мя параметрами колонкой А
и колонкой Б в совокупности, (значение и в A ,и в Б могут повторятся) как можно это сделать?
Все равно не понятна зависимость между выбором конкретного значения J14 в зависимости от значений двух левых колонок (так?). Кто-то выбирает заранее какие значения первых колонок значимы?

Кстати, если сложность заключается в составном выборе (по двум условиям), то можно создать доп. столбец и СЦЕПИТЬ значения значимых колонок. Совет: для дат используйте функцию ТЕКСТ(...;"ММ.ДД.ГГГГ")
Название: Re: Списки в Excel
Отправлено: MishaOkrushko от 01.12.2009, 01:44
Результат, который я хочу получить, объяснить легко)
Нужно найти строку в массиве, и выбрать из неё элемент.
Тоже делает и ВПР(_) (https://msexcel.ru/content/view/20/2/), но мне нужно , чтобы
=Впр*([Элемент столбца1, Элемент столбца2,...];таблица; номер столбца; [интервальный просмотр]).
Попробую объяснить на примере:
Пусть  мы продаем машины, и у каждой машины нет уникального идентификационного номера. Машины имеют характеристики: Марка, дата выпуска, цвет, комплектация (много параметров), ..., цена (которую мы и хотим найти, но заранее её не знаем)
Все атрибуты могут встречаться неоднократно, а могут не встречаться (= «»).
У нас есть база данных со всеми машинами на складе (допусти, что база огромная и атрибутов у машины много на столько, что в ручную мы перебрать их не можем). Приходит запрос с конкретными характеристиками, и надо узнать есть ли на складе такой вариант и если есть, то сколько стоит.

С помощью & и для 2х я решил, но думал, что можно сделать как то по-другому и  для всех случаев. :)
Название: Re: Списки в Excel
Отправлено: kimtan от 01.12.2009, 02:02
Можно конечно это все формулами накрутить, но по-моему для Вас лучший вариант - [url=https://msexcel.ru/content/view/27/2/]_автофильтр_ (http://msexcel.ru/content/view/27/2/)[/url] или сводная.
Название: Re: Списки в Excel
Отправлено: ivas от 01.12.2009, 08:27
Здравствуйте!
Подскажите пожалуйста, как увеличить окно выпадающего списка. (Очень много значений приходится прокручивать) Ввод с клавиатуры неподходит.
Название: Re: Списки в Excel
Отправлено: Шпец Докапыч от 01.12.2009, 13:15
Для увеличения выпадающего списка стандартных настроек не предусмотрено. Можно лишь увеличивать масштаб листа (https://forum.msexcel.ru/microsoft_excel/vypadayuschiy_spisok_format-t388.0.html;msg1972#msg1972), чтобы список был крупнее, но нам, я так понимаю, сейчас это не нужно.
А вот в эл.упр. "Поле со списком" это можно задать:

Вот образец того, как можно "Поле со списком" использовать вместо (читай – аналогично) Выпадающего списка из проверки данных, а соответственно и задать его размер: SuperComboBox.rar (https://forum.msexcel.ru/index.php?action=dlattach;topic=1538.0;attach=1760)
Название: Re: Списки в Excel
Отправлено: MishaOkrushko от 01.12.2009, 15:37
kimtan, Большое спасибо! Очень хороший вариант решения  :)
Название: Re: Списки в Excel
Отправлено: Novel от 11.12.2009, 22:11
Здравствуйте, подскажите пожалуйста как в одном столбце таблицы написать в столбик слова (в Wordе энтер нажал вот тебе и следующая строка в этом же столбце) а здесь как? ставить в формате ячейки переносить по словам проблематично так как разные по длине слова, ставить пробелы придется везде по разному. спасибо
Название: Re: Списки в Excel
Отправлено: Шпец Докапыч от 11.12.2009, 22:38
Alt+Enter?
Название: Re: Списки в Excel
Отправлено: Novel от 11.12.2009, 22:43
спасибо
Название: Re: Списки в Excel
Отправлено: Novel от 11.12.2009, 22:49
а как сделать так чтобы был автоподбор по высоте строки или строка сама расширялась при написании текста сдвигалась вниз
Название: Re: Списки в Excel
Отправлено: artiom_com от 12.12.2009, 13:33
Здравствуйте !
Подскажите пожайста как создать список но что-бы нельзя было выбирать значения из списка (т.е. только чтоб показывал) и имя ячейки всегда отавалось темж же. Например есть 1-группа,2-группа и тд до 14 - это наименования ячеек... В эти группы входят списки из сотрудников (иванов Петров....). Смысл в том чтобы нажать на допустим 1-груупу ячейка выдавла список с фамилиями но при дезактивации ячейки возвращало наименование т.е. 1-группа
ЗЫ Помогить плиз с данным вопросом это сурьезно для работы
ЗЫ как еще можно защитить ячейку от ввода
Большое спасибо
Название: Re: Списки в Excel
Отправлено: Шпец Докапыч от 12.12.2009, 14:27
Цитата: Novel от 11.12.2009, 22:49
автоподбор по высоте строки или строка сама расширялась при написании текста сдвигалась вниз
Вроде как, непосредственно при написании, изменение размера строки/столбца заблокировано. автоподбор = автозавершение или что?

PS: Если заюзать этот пример (https://forum.msexcel.ru/index.php?action=dlattach;topic=1538.0;attach=1760), то размеры и всё остальное в наших руках. :)
Название: Re: Списки в Excel
Отправлено: Novel от 13.12.2009, 09:57
к примеру я пишу в одном столбце:
Иванов,(alt+Enter)
Петров,(alt+Enter)
Сидоров, (alt+Enter)
Захаров.
и вот у меня в каком то столбце все 4 фамилии показываются (сама табличка раскрылась), а вписываю в следующем столбце из 4 только иванов а остальное приходится растягивать вниз чтобы показало все что напечатал, не пойму вроде и формат ячейки одинаков?  :(
Название: Re: Списки в Excel
Отправлено: Papiroha от 21.12.2009, 16:10
всем привет!
Не могли бы вы мне помочь с таким вопросом по поводу Выпадающих списков:
возможно ли сделать так, чтобы в выпадающем списке вместо текста были изображения? Вариант, когда в соответствии с выбранным текстом в вып списке появляется соответствующее изображение я рассмотрел, а вот чтобы сразу изображение выбрать - никак не получается.

И ещё вопросик: кто-нибудь сталкивался с проблемой в функции "условное_форматирование (https://msexcel.ru/content/view/116/2/)", когда выскакивает окошко с ошибкой "Недостаточно системных ресурсов для полного вывода на экран" (Excel 2007)

заранее спасибо.
Название: Re: Списки в Excel
Отправлено: Gnork от 29.01.2010, 14:37
Добрый день.
При вводе каких-то данных в столбик, автоматически формируется некий список автоподстановки, значение из которого можно вставить, нажав на ячейку правой кнопкой и выбрав "выбрать из раскрывающегося списка". Этот список автоматически дополняется при появлении в этой колонке новых значений и сортируется по алфавиту.
Можно ли этот "самосоздающийся" список использовать для создания собственного выпадающего списка?
Пример:
Имеется некая таблица, при вводе в колонку "А" которой, повторяется некоторое число вариантов. В дальнейшем варианты дополнения могут добавляться. Имеется ячейка с выпадающим списком, созданная например из "данные-проверка данных". Надо, чтобы список был именно тот, который появляется по правой кнопке при заполнении таблицы.

MsExcel2007 Русский
Название: Re: Списки в Excel
Отправлено: IKor от 30.01.2010, 10:09
Цитата: Gnork от 29.01.2010, 14:37
Добрый день.
Можно ли этот "самосоздающийся" список использовать для создания собственного выпадающего списка?
Да, можно, но Вам потребуется дополнительный столбец (можно на отдельно листе) для формирования исходных данных выпадающего списка. Если же для Вас критична сортировка по алфавиту, то может потребоваться несколько дополнительных столбцов. Посмотрите 42 сообщение в этой теме (3 стр.) по-моему, оно похоже на то, что Вам требуется.
Цитата: IKor от 15.01.2009, 22:31
Особое внимание рекомендую обратить на приписку внизу сообщения (выделите ее правой кнопкой, чтобы прочитать) :)
Название: Re: Списки в Excel
Отправлено: Gnork от 01.02.2010, 10:02
Спасибо, мне так подходит.
Интересное решение получилось. А прямое использование именно того "автоматического" списка в ёкселе не предусмотрено?

При разборе формул завис на фразе "Сформируем виртуальный столбец Номера..." Не смог в справке ничего найти про такие столбцы :( Предполагаю, что имелся в виду "столбец" массива, но не понял в какой ячейке он формируется.
Название: Re: Списки в Excel
Отправлено: IKor от 01.02.2010, 18:08
Цитата: Gnork от 01.02.2010, 10:02
При разборе формул завис на фразе "Сформируем виртуальный столбец Номера..." Не смог в справке ничего найти про такие столбцы :( Предполагаю, что имелся в виду "столбец" массива, но не понял в какой ячейке он формируется.
Под виртуальным столбцом здесь подразумевается имя, присвоеннное не конкретному диапазону ячеек, а динамически изменяющейся последовательности значений (одномерному массиву), хранящейся в памяти компьютера под именем Номера (см. меню ВСТАВКА / ИМЯ / ПРИСВОИТЬ).
В Эксель имена можно присваивать костантам, ячейкам или результатам вычисления формул (в том числе, возвращающим массив данных).
К сожалению, этот виртуальный диапазон невозможно использовать в качестве источника данных для формирования поля со списком (меню ДАННЫЕ / ПРОВЕРКА / Список), поэтому для формирования этого источника данных приходится выводить виртуальный диапазон в виде конкретных ячеек с данными (в произвольном месте книги).
Название: Списки в Excel
Отправлено: Марийка от 03.02.2010, 21:42
Доброй ночи! Есть небольшая задачка, ни как не могу ее решить. Есть список с повторяющимися значениями (названиями), нужно составить новый список без повторений и отдельный.
Название: Re: Списки в Excel
Отправлено: Zaal от 03.02.2010, 22:39
здесь поможет только макрос, если сами не осилите - обращайтесь
Название: Re: Списки в Excel
Отправлено: _Boroda_ от 03.02.2010, 23:05
Пять способов без макросов. Если еще немного подумать, то, я уверен, можно предложить и другие.
Название: Re: Списки в Excel
Отправлено: Zaal от 04.02.2010, 00:17
хм... согласен :), век живи - век учись :)
Название: Re: Списки в Excel
Отправлено: DV68 от 04.02.2010, 00:24
Несколько формул для 4-го способа:
Название: Re: Списки в Excel
Отправлено: Марийка от 04.02.2010, 09:54
всем спасибо огромное ::)
Название: Re: Списки в Excel
Отправлено: Dimi от 09.02.2010, 10:55
Добрый день, подскажите в чем я не прав.
В список вставляю формулу ЕСЛИ(А2="1";Статьи_1;ЕСЛИ(А2="2";Статьи_2;ЕСЛИ(А2="3";Статьи_3;)))

Статьи_ это именной диапазон.

Ну не хочет работаь зараза.
Что-то с формулой намудрил((((
Спаситеееее!
Название: Re: Списки в Excel
Отправлено: Михаил С. от 10.02.2010, 00:10
Цитата: Dimi от 09.02.2010, 10:55
Добрый день, подскажите в чем я не прав.
В список вставляю формулу ЕСЛИ(А2="1";Статьи_1;ЕСЛИ(А2="2";Статьи_2;ЕСЛИ(А2="3";Статьи_3;)))
Статьи_ это именной диапазон.

Используйте такую формулу =ДВССЫЛ("Статьи_"&A2)
Название: Re: Списки в Excel
Отправлено: Dimi от 12.02.2010, 17:10
Доброго времени суток и с Пятницой всех))
Проштудировал тему со списками, но для себя ответа так и не нашел(

Данные из выпадающего списка не соответствуют размерав ячеек. То есть ячейка имеет один размер, а выпадающий список в 2 раза больше в ширину. Сначала список был в одну ширину с ячейкой, а сейчас  увеличился.
Отсюда вопрос можно ли настроить ширину выпадающего списка, за одно и формат его!?
Название: Re: Списки в Excel
Отправлено: Rimsky от 08.06.2010, 12:01
Доброе время суток!
Трэба сделать в экселе 2003 следующее:
Формула
Z=2*a
где а выбирается пользователем из ячейки с выпадающим списком
"до 100лс" а=1
"до 250лс" а=2
Спасибо!
Название: Re: Списки в Excel
Отправлено: IKor от 08.06.2010, 14:16
Посмотрите - во вложении два варианта.
Возможно второй Вам покажется более привлекательным.
Название: Re: Списки в Excel
Отправлено: Rimsky от 08.06.2010, 15:39
Еще вопросик
пытаюсь в данные - проверка внести список из чисел "1.25;1.33;1.50;1.78;1.85;2.00;2.35;" Эксель по великому своему уму преобразует их в даты (цука). Формат Числовой. Где грабли?
Название: Re: Списки в Excel
Отправлено: Rimsky от 08.06.2010, 16:48
Пробовал зпт подставить, тогда в формуле со ссылкой на эту ячейку с выбором возникает ошибка #ЗНАЧ. Сейчас в Данные-Проверка такой список "1.25;1.33;1.50;1.78;1.85;2.00;2.35;сукаперебиваетчислонадату". Если выбрать число - таки работает.
И еще по первому вопросу моему:
Трэба сделать в экселе 2003 следующее:
Формула
Z=2*a
где а выбирается пользователем из ячейки с выпадающим списком
"до 100лс" а=1
"до 250лс" а=2
Спасибо!

я написал два выбора для затравки, на самом деле там намного больше перебора, причем слова "до 100лс" должны присутвовать обязательно, т.к. требуется не только мощность посчитать, но и учесть тип транспорта и пр. нюансы. В общем нужен экселевский Case
"до 100лс" а=1
"до 150лс" а=2
"до 200лс" а=3
"до 250лс" а=4

Функцию СМЕЩ знаю, но не догоняю как она работает.
Название: Re: Списки в Excel
Отправлено: Rimsky от 08.06.2010, 17:24
Кладу. Это мой битрейт-калькулятор
Название: Re: Списки в Excel
Отправлено: _Boroda_ от 08.06.2010, 17:43
Можно так:
Название: Re: Списки в Excel
Отправлено: Rimsky от 09.06.2010, 08:21
Цитата: _Boroda_ от 08.06.2010, 17:43
Можно так:
СПАСИБИЩЕ!!!
Название: Re: Списки в Excel
Отправлено: alfadmin от 06.07.2010, 14:31
Добрый день ! Интересует такой момент - есть очень большой список, все позиции списка уникальны, можно ли как то автоматизировать ввод данных из списка в таблицу(цы), таким образом, чтоб при вводе первых букв(символов) в ячейку, выпадающий список "прокручивался" бы на позиции, которые начинаются с этих букв, (примерно как в словаре лингво когда набираем слово, или скажем в строке поискового сайта - когда начинаем набирать запрос, яндекс- в выпадающем списке показывает возможные варианты),  заранее благодарю.
Название: Re: Списки в Excel
Отправлено: Verter от 08.07.2010, 06:43
Здравствуйте уважаемые. Есть достаточно большая таблица с исходными данными, значения в одном столбце уникальны, в другом повторяются по нескольку раз. Есть необходимость сделать пустую таблицу в которой в одном столбце из раскрывающегося списка выпадал набор неповторяющихся значений из того столбца таблицы с данными в котором эти значения повторялись, а в другом столбце уже в соответствие с выбором в первом столбце формировался выпадающий список с выборкой значений из исходной таблицы.
Короче, если сказать по другому. Есть две проблемы: Как создать выпадающий список с уникальными значениями, если исходные значению повторяются? И как создать выпадающий список из одного столбца если выполняется условие соответствия в противоположном?
Название: Re: Списки в Excel
Отправлено: IKor от 09.07.2010, 15:59
2 Verter
несколько комментариев по Вашему вопросу:

1. Описание способов формирования списка уникальных значений (для ДАННЫЕ / ПРОВЕРКА / Список) описывалось многократно - в том числе в этой теме (смотрите, например 3 страницу). Без использования VBA Вам потребуется разобраться с работой функций НИАМЕНЬШИЙ(), ПОИСКПОЗ(), ИНДЕКС() и СЧЁТ()

2. Формирование списков значений, зависимых от значения в соседней ячейке (a-la подменю) также описывалось в этой теме. Важно отметить, что решение этой задачи требует отсортировать исходную таблицу так, чтобы спики зависимых значений шли подряд. Можно провести сортировку самой таблицы или сформировать дополнительную отсортированную таблицу. Альтернативное решение - использовать дополнительные столбцы для каждого значения главного списка. Здесь Вам пригодится формула СМЕЩ() помимо вышеуказанных. Кроме того, рекомендую разобраться с работой с именами (ВСТАВКА / ИМЯ / ПРИСВОИТЬ), содержащими формулы с относительными ссылками на управляющие ячейки.

3. Если Вы приложите свой упрощенный пример (без секретной информации), то станет проще разобраться с Вашей задачей и подсказать решение.
Название: Re: Списки в Excel
Отправлено: Verter от 09.07.2010, 23:15
2  IKor

Все эти функции мне знакомы, но не понятно как все это прикрутитть  к проверке данных через список :)

Пример во вложении.

P.S. Спасибо за ответ
Название: Re: Списки в Excel
Отправлено: DV68 от 10.07.2010, 06:43
Для уникальных нужен будет доп столбец. Если данных много, то лучше как написано в заголовке столбца E, но можно и формулами, как в столбце F:
Название: Re: Списки в Excel
Отправлено: Verter от 10.07.2010, 21:03
2 Dmitr68

Спасибо большое за пример. Буду разбираться!
Название: Re: Списки в Excel
Отправлено: Katy29d от 15.07.2010, 14:28
Всем добрый день :)


Ребята, возникла такая проблема!!!У меня есть список 1000 человек, в котором некоторые личности повторяются!Я всё отсортировала и теперь повторяющиеся фамилии стоят рядом!!Как мне удалить эти повторы??А то вручную это очень долго :-\
Название: Re: Списки в Excel
Отправлено: _Boroda_ от 15.07.2010, 14:32
в соседнем столбце в В2 написать =A1=A2, протянуть вниз, отфильтровать все ИСТИНА и убить.
В 2007 есть Данные - Удалить дубликаты
Название: Re: Списки в Excel
Отправлено: Katy29d от 15.07.2010, 14:44
Спасибо ОГРОМНОЕ))))))))

Вы даже не представляете, как вы мне помогли))) :-*
Название: Re: Списки в Excel
Отправлено: sveta22 от 09.08.2010, 15:27
помогите пожалуйста , создаю поле со списком , но при печати видно что это поле со списком , создаю через разработчик и вставляю элемент управления , убираю галочку выводить при печати , но и значение тогда не выводится , а через проверку данных меня не утраивает больше 8 пунктов показывать надо... хелп  :'(
Название: Re: Списки в Excel
Отправлено: Шпец Докапыч от 09.08.2010, 18:54
В ячейке под невидимым для печати списком можно отобразить какое значение был выбрано:
Где список – адрес диапазона; A1 – связь с ячейкой, в которой указывается номер выбранного пункта.
Название: Re: Списки в Excel
Отправлено: lopatochka от 15.09.2010, 20:01
Доброго дня, всем!
может все таки кто-нибудь подскажет: список создан, список содержит 2000 строк, нажимаем стрелочку
из выпадающего списка вниз, и поскольку список большой, хотелось бы вводить первые буквы нужной надписи и мы бы приближались к этому слову. Трудно выбирать из списка значение, если он просто огромный. :)
Название: Re: Списки в Excel
Отправлено: Irina222 от 26.09.2010, 01:19
Здравствуйте. Подскажите пжл, как сделать в ЕХ2003 таблицу с выпадающими списками. Списки необходимо сделать на вспомогательном листе( список "отрасль" включает 11 строк, список "вид работ" содержит 13 позиций). Выбираю Данные-Параметры-Тип данных-список. В окне "источник" не могу сделать ссылку на нужный лист ("вспомогательный").
Если размещаю списки на рабочем листе - все получается, списки " выпадаеют" там, где надо. Но это неудобно. Очень хочеться иметь их на отдельном листе...
Название: Re: Списки в Excel
Отправлено: Шпец Докапыч от 26.09.2010, 01:37
Irina222, присвой спискам имена (Вставка → Имя), тогда всё получится.
Название: Re: Списки в Excel
Отправлено: Irina222 от 28.09.2010, 16:39
Спасибо.Все получилось. Классный сайт!!!!!!!!!!!!!!!!
Название: Re: Списки в Excel
Отправлено: bq52 от 08.10.2010, 12:16
Здравствуйте.
 
 Прошу помощи по вопросу списков. Файл прилагается.

Суть: расчет стоимости печати; я ввожу только ширину и высоту макета, и выбираю тип материала (самоклейка, банер и т.д.), дальше мне показывает какая квадратура получилась, и дальше со второго списка я выбираю тип стоимости (<10 м.кв /  >10м.кв. / и сетевые форматы) и дальше цена автоматически высчитывается.

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

В файле более понятно чем я излагаю скорее всего, так как там наглядно.

С уважением и надеждой на помощь, Сергей.

---------------------------------------------------
Задачу уже решил) решил  через "Динамическая выборка из списка функциями ИНДЕКС и ПОИСКПОЗ". Выпадающие списки, грубо говоря, вертикаль и горизонталь давали, и на их пересечении нужное значение выдергивал.

Отдельное спасибо вашему форуму за помощь в решении моей задачи.

Название: Re: Списки в Excel
Отправлено: bq52 от 13.10.2010, 11:12
Столкнулся с новой задачей, прошу помощи.

Как привязатьть к разворачивающемуся списку (ComboBox) функцию ЕСЛИ. это в VBA чтоли делать? т.е. мне нужно, в зависимости от типа данных  определенной ячейки, чтобы в этот список подгружался разный диапазон.

например: если А1=100, то в список грузится диапазон С1:С10,
а если А1=200, то в список грузится диапазон F1:F10

вот незнаю как сделать, ибо диапазон в свойствах ComboBox прописывается и все, а как условие задать.
Название: Re: Списки в Excel
Отправлено: ХАха от 13.10.2010, 13:06
Bq52 попробуйте комбобокс приписать к диапазону, в который данные попадают по ВПР (или ее аналог поиска по горизонтали - ГПР)
Название: Re: Списки в Excel
Отправлено: ХАха от 13.10.2010, 13:11
Эх, не успела решить задачу с самоклейкой :)
Вместо индекс и поискпоз лучше использовать ВПР - она проще :)
Название: Re: Списки в Excel
Отправлено: ХАха от 13.10.2010, 13:41
Все же выложу свой вариант :)
Название: Re: Списки в Excel
Отправлено: bq52 от 13.10.2010, 14:59
Честно говоря не понял как ВПР пользоваться, дело в том, что я вообще первый раз так глубоко с экселем столкнулся, а примеров внятных не нашел, а вот по менее удобному методу, как то получилось. Файлик приложу.

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

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

С уважением, Сергей.

p.s. Спаибо большое за ответ)
Название: Re: Списки в Excel
Отправлено: Irina222 от 18.10.2010, 20:02
Снова нужна ваша помощь. Как сделать в Ех2010 выплывающий список.
Название: Re: Списки в Excel
Отправлено: alexkonakov от 24.10.2010, 11:48
Цитата: alfadmin от 06.07.2010, 14:31
Добрый день ! Интересует такой момент - есть очень большой список, все позиции списка уникальны, можно ли как то автоматизировать ввод данных из списка в таблицу(цы), таким образом, чтоб при вводе первых букв(символов) в ячейку, выпадающий список "прокручивался" бы на позиции, которые начинаются с этих букв, (примерно как в словаре лингво когда набираем слово, или скажем в строке поискового сайта - когда начинаем набирать запрос, яндекс- в выпадающем списке показывает возможные варианты),  заранее благодарю.
Название: Re: Списки в Excel
Отправлено: nikitan95 от 29.10.2010, 11:49
Цитата: ХАха от 13.10.2010, 13:11
Эх, не успела решить задачу с самоклейкой :)
Вместо индекс и поискпоз лучше использовать ВПР - она проще :)

Доброго)
Насчет фунции ВПР...
Пожалуйста, помогите со следующей ситуацией: надо было составить БД автомашин с их картинками. Сделал в двух листах. В первом - общий список со всеми техн.характеристиками (в т.ч. картинки), а во-втором просмотр, через выпадающий список (выбирается машина с госномером, а остальные поля автоматом заполняются через функцию ВПР). Все работает путем, а вот как вставить показать картинки соответственно но машинам, ну никак мне не удалось. Помогите, пожалуйста, прошу. Попробовал как в http://www.planetaexcel.ru/tip.php?aid=57 но увы или мои руки кривые)) или я не понял суть команд.. надежда на Вас, очень прошу.. Заранее благодарен..
Название: Re: Списки в Excel
Отправлено: ХАха от 29.10.2010, 15:19
А вы свой файлик прикрепите, мы попробуем его поправить  ::)
Название: Re: Списки в Excel
Отправлено: nikitan95 от 30.10.2010, 10:37
Цитата: ХАха от 29.10.2010, 15:19
А вы свой файлик прикрепите, мы попробуем его поправить  ::)

С удовольствием)
Очень надеюсь, что Вы укажете мне на допущенные мною ошибки (молю Бога, чтобы их было немного)))
Название: Re: Списки в Excel
Отправлено: liza_cetera от 30.10.2010, 23:49
Добрый вечер.
Поскажите, пожалуйста.
1) У меня есть список сотрудников (фамилии), мне нужно посчитать их количество, причем при добавлении нового сотрудника количество обновлялось.
2) Также у меня есть список клиентов(фамилия, дата принятия на обслуживание, дата снятия с обслуживания). Мне нужно посчитать количество принятых, количство снятых с обслуживания, и количество принятых в текущем месяце, или в прошлом.
Название: Re: Списки в Excel
Отправлено: С.М. от 30.10.2010, 23:52
nikitan95, подправил Ваш фотоальбом.
Название: Re: Списки в Excel
Отправлено: nikitan95 от 01.11.2010, 06:23
Цитата: С.М. от 30.10.2010, 23:52
nikitan95, подправил Ваш фотоальбом.
Огромное Вам спасибо!!! Дай Бог Вам всегда)
Название: Re: Списки в Excel
Отправлено: NecroTYN от 02.11.2010, 14:34
Добрый день Уважаемые !!!!
Я здесь совсем недавно и совсем еще новенький(и зеленый) в Excel
Взялся делать вот такой вот документ(см.ниже), загвоздка вот в чем,как сделать чтоб данные из листа 1((прием заказа)выделено синим)),сохранялись в листе 3(Клиентская база)выделено синим))

Забыл добавить,действие это должно происходить по нажатию на кнопку "принять"(не создана в данной версии файла...)
Название: Re: Списки в Excel
Отправлено: С.М. от 03.11.2010, 15:21
NecroTYN, посмотрите вложение.

P.S. А почему у Вас форма такая мрачная (в черной окантовке), там не домовины часом делают ?
Название: Re: Списки в Excel
Отправлено: NecroTYN от 03.11.2010, 18:42
Цитата: С.М. от 03.11.2010, 15:21
NecroTYN, посмотрите вложение.
Да!!! Это то что нужно!!!!  :D  :D  :D СПАСИБО ОГОРОМНОЕ!!!!

А можно поподробнее,как исполнить такое самому???

ЦитироватьP.S. А почему у Вас форма такая мрачная (в черной окантовке), там не домовины часом делают ?

Форма это временно,особо над ней еще не работал.... (это черновик)
Т.К. работы еще много,еще обращусь....
Название: Re: Списки в Excel
Отправлено: С.М. от 04.11.2010, 16:50
Цитата: NecroTYN от 03.11.2010, 18:42
А можно поподробнее,как исполнить такое самому ? ? ?
??? На рояле ? ? ?
Название: Re: Списки в Excel
Отправлено: NecroTYN от 04.11.2010, 18:55
Цитата: С.М. от 04.11.2010, 16:50
??? На рояле ? ? ?
Если поможет,то можно и на рояле.... ;D  ;D  ;D

Если серьезно,можно формулы или макрос с описанием?????
Название: Re: Списки в Excel
Отправлено: С.М. от 04.11.2010, 23:58
NecroTYN, добавил описание работы макроса в его код.
Название: Re: Списки в Excel
Отправлено: zzl от 02.01.2011, 13:56
Здравствуйте.
Имеется таблица. Лист Данные содержит данные по объектам. На листе ТО есть даты проведения ТО по группам объектов. Как создать выпадающий список на листе График, чтобы при  при добавлении номера группы на листе Данные Объект появлялся в этом списке и при его выборе отображался соответствующий номер договора из Данные и даты соответствующие данной группе из ТО.
Нашел пример - Динамически обновляемый раскрывающийся список для ввода данных, чувствую похоже, но ума не хватает, бьюсь неделю - результат по задаче 0, только ощущение правильности направления.
Просветите если можете, желательно с небольшим ликбезом. Файл прилагаю.
Спасибо.
Название: Re: Списки в Excel
Отправлено: VictorK от 05.01.2011, 22:33
zzl
Посмотрите, вроде получилось то, что Вам нужно. Для рабочего файла придётся кое что подправить.
Название: Re: Списки в Excel
Отправлено: kimtan от 05.01.2011, 23:30
zzl, сделал первые две ячейки, остальное попробуйте сами по аналогии.
Название: Re: Списки в Excel
Отправлено: ВЕВ от 18.01.2011, 13:24
Я извиняюсь, 16 страниц нет времени прочитать. Мой вопрос (может он был уже):
есть 3 колонки:
1 - номер по-порядку;
2 - ФИО;
3 - сумма.

Список людей одинаков каждый месяц, если нет суммы (пусто в ячейке), я скрываю эту строку с фамилией (чтобы не печатать). Так вот можно ли сделать так чтобы остальные строки нумеровалиь попорядку, т.е. 1,2,3,4,... (скрытые не учитывались).
Название: Re: Списки в Excel
Отправлено: _Boroda_ от 18.01.2011, 14:06
Пара вариантов во вложении
Название: Re: Списки в Excel
Отправлено: ВЕВ от 18.01.2011, 14:35
а _автофильтр_ (https://msexcel.ru/content/view/27/2/) надо применять ?
Название: Re: Списки в Excel
Отправлено: С.М. от 18.01.2011, 16:13
Ещё вариант:
Название: Re: Списки в Excel
Отправлено: ВЕВ от 18.01.2011, 16:36
Не получается - вот пример. А фильтр обязателен в примерах?
Название: Re: Списки в Excel
Отправлено: _Boroda_ от 19.01.2011, 00:19
Вот уж никак не мог предположить, что Вы скрывать пустые будете не автофильтром, а вручную.

Тогда в обеих формулах не ПРОМИТОГИ(3;....., а ПРОМИТОГИ(103;.....

см. вложение
Название: Re: Списки в Excel
Отправлено: ВЕВ от 19.01.2011, 09:50
Спасибо! Получилось. Я так понял можно использовать любую из 2 формул.
Название: Re: Списки в Excel
Отправлено: Lola_Esposito от 26.01.2011, 08:55
К сожалению нет времени читать все страницы, может кто ссылку даст если подобное уже было, ну или вдруг кто поможет - буду премного благодарна.
Есть список соискателей (с должностями и подразделениями), отправляем на согласование разным руководителям их документы и в excel ставим дату когда это сделали. Мне нужно отслеживать какие не вернулись с согласования, например, через 3 дня. Вот как сделать, чтобы какое-либо оповещение вылезало, что на Иванова и Петрова документы не вернулись?
Название: Re: Списки в Excel
Отправлено: IKor от 26.01.2011, 20:34
Из Вашего запроса неочевидно зачем требуются списки.
Посмотрите пример - то ли это, что Вы хотите сделать?
Если нет, то сформулируйте задачу по-другому и желательно приложите Ваш пример.
Название: Re: Списки в Excel
Отправлено: Diml от 01.02.2011, 20:55
Здравствуйте!
Есть таблица из 4 столбцов: тип, шифер, железо, металлочерепица. Тип выбирается из списка. Нужно в графе норма стоим-ти проверить наименование  объекта и в зависимости из чего он сделан выбрать норму стоимости. Заранее благодарен!
Название: Re: Списки в Excel
Отправлено: IKor от 02.02.2011, 19:11
2 Diml
Для того, чтобы найти строку в столбце $B$54:$B$71, соответствующую значению, выбранному в ячейке $B$11 можно воспользоваться следующей формулой:
=ПОИСКПОЗ($B$11;$B$54:$B$71;0)
Для того, чтобы вернуть значение стоимости из найденной строки таблицы $B$54:$E$71, соответствующее одному из правых столбцов можно воспользоавться формулой:
=ИНДЕКС($B$54:$E$71;ПОИСКПОЗ($B$11;$B$54:$B$71;0);номер_столбца)?
здесь номер_столбца равен 2 для железа, 3 для шифера и 4 для металлочерепицы.
Но вот на основании данных из какого столбца строки G11:I11 определять указанный пользователем материал (= номер столбца) - лично мне непонятно.

Уточните свой запрос :)
И удачи!

P.S. Совет - почитайте на формуе об именованных диапазонах - с ними удобнее работать.
Название: Re: Списки в Excel
Отправлено: Diml от 03.02.2011, 10:13
Цитата: IKor от 02.02.2011, 19:11
2 Diml
Но вот на основании данных из какого столбца строки G11:I11 определять указанный пользователем материал (= номер столбца) - лично мне непонятно.
Уточните свой запрос :)
И удачи!

Уточнение:
D ячейку N11 нужна формула, чтобы он проверил ячейку В11 и I11. В данном случае 1 этажный кирпичный дом, кровля металлочерепица, соответственно формула должна выбрать ячейку Е57, если бы в I11 стояло железо - то D57.
Помогите пожалуйста с формулой
Название: Re: Списки в Excel
Отправлено: IKor от 03.02.2011, 13:54
2 Diml
формула для ячейки N11:
=ВПР($B11;$B$53:$E$105;ПОИСКПОЗ($I11;$B$53:$E$53;0);0)
если скопировать эту формулу в ячейку N12, то она изменится для работы с ячейками B12 и I12, соответственно.
Название: Re: Списки в Excel
Отправлено: Diml от 03.02.2011, 14:41
IKor, огромное спасибо! все работает sm_beer
Название: Re: Списки в Excel
Отправлено: zaharova от 13.03.2011, 13:24
Здравствуйте, помогите пожалуйста! :)
дана таблица(база данных), фамилия,имя,отчество,отдел,оклад,пол,дата рождения,возраст,дата приема на работу
надо найти информацию о работниках Отдела,имеющих оклад от Оклада1 до Оклада2, с помощью расширенного фильтра.
Как это сделать??? :)
Название: Re: Списки в Excel
Отправлено: hottabych69 от 22.06.2011, 12:28
Доброго времени суток, многоуважаемые гуру!
Время катастрофически не хватает на перечитку 17 страниц сообщений и поиск нужной инфы, поэтому перейду сразу к делу...
Есть файл в который необходимо заносить ежедневно информацию о проделанных рейсах автомобильным парком(подвижным составом), так называемый "ежедневный отчет". Отчет за каждый день занимает отдельный лист в файле, именуемый по дате, например: имя листа - 20110701 соответствует отчету за 01.07.2011 и так далее до 30-31-го дня в месяце. Парк машин по структурным подразделениям может колебаться от 10 до 30 машин, кол-во рейсов сделанных каждой машиной тоже может колебаться от 2-х до 4-х, соответственно и записей в одном отчете может быть от 20-ти(10 авто * 2 рейса) до 120-ти(30 авто * 4 рейса). Для сокращения времени на заполнение отчета решил применить выпадающие списки и в соответствии с выбором в нем подстановку нужных данных. Для этого создал справочники на первых двух листах, все бы хорошо, но не получается связать два списка, помогите связать списки. Надо чтобы при выборе модели авто автоматом группировались и показывались гос. номера авто той модели, которую выбрали(это нужно для сокращения поиска нужных данных в выпадающем списке), т.е. если диспетчер выбрал модель "ЗИЛ-131" то в ячейке столбца "гос. номер" в выпадающем списке были отобраны все гос. номера ЗИЛ-131, вот и все. Если этого нельзя сделать с помощью инструментария Excel 2003-2007, то был бы рад за помощь в написании макроса VBA(хотя я в нем ничего не смыслю). Файл отчета прилагаю.
Название: Re: Списки в Excel
Отправлено: cheshiki1 от 23.06.2011, 12:25
hottabych69. Так? Сделал только на листе 20110701
Название: Re: Списки в Excel
Отправлено: С.М. от 23.06.2011, 15:59
To hottabych69: может быть так ?
Название: Re: Списки в Excel
Отправлено: hottabych69 от 24.06.2011, 22:42
to cheshiki1:
                 Честно говоря никаких изменений не обнаружил, но все равно спасибо за то, что откликнулся!

С.М.:
       Огромное спасибо, как раз то, что я хотел! Только есть некоторые нюансы:
1. Список уникальных уже есть на листе "Справочник" в столбце А
2. Какое ограничение на кол-во строк в ежедневном отчете, если есть такое?
3. Можно переименовать название диапазонов или это повлияет на работу списков и макроса.
4. Объясните функционал макросов ClearRecords и Test1
Название: Re: Списки в Excel
Отправлено: С.М. от 25.06.2011, 02:03
Hottabych, в прядке убывания:
4.2. Макрос Test1 - это отладочный тест - я просто забыл удалить вместе с ним уже не нужный модуль.
4.1 ClearRecords - макрос, назначенный кнопке "Очистить". Задумка такая: сделал что-то вроде защиты заполненных строк отчёта (но без защиты листа), записи можно удалить или поячеечно очищая первый столбик или все сразу кнопкой (возможно перемудрил).
3. Новые: "Отчет, ОтчетВысота, Модель, Табл.1" - нет (или перелопачивать код), остальные - Ваши - да.
2. 65536 минус высота_шапки минус пять :)  ,  но (пока) есть ограничение на число Гос. номеров авто <= 25, т.к. формула в этом выпадающем списке (Гос.ном.) - строка с номерами через запятую, макс.число знаков = 255 , если будет больше - будут отрезаны (это в моём 2003-ем, на новых моделях Ex'а - не "катался").
1. В Вашем списке уникальных 11 моделей, а в гараже из них "стоят" только 3, насколько я понял, в отчёт должны попасть только записи из таблицы 'Подвиж_состав'.
Название: Re: Списки в Excel
Отправлено: cheshiki1 от 29.06.2011, 14:12
ЦитироватьЧестно говоря никаких изменений не обнаружил, но все равно спасибо за то, что откликнулся!
плохо смотрел. на листе 20110701 в столбце "В" я оставил как у тебя, зато в "С" исправлено, при выборе в ячейка В... марки машины в С.. показываются только номера данной марки а не все как раньше было.
сделал как Вы просили
Цитироватьт.е. если диспетчер выбрал модель "ЗИЛ-131" то в ячейке столбца "гос. номер" в выпадающем списке были отобраны все гос. номера ЗИЛ-131,
Если я что то не до понял не обессудьте как понял так и сделал.
Название: Re: Списки в Excel
Отправлено: hottabych69 от 29.06.2011, 18:10
Цитата: cheshiki1 от 29.06.2011, 14:12
ЦитироватьЧестно говоря никаких изменений не обнаружил, но все равно спасибо за то, что откликнулся!
плохо смотрел. на листе 20110701 в столбце "В" я оставил как у тебя, зато в "С" исправлено, при выборе в ячейка В... марки машины в С.. показываются только номера данной марки а не все как раньше было.
сделал как Вы просили
Цитироватьт.е. если диспетчер выбрал модель "ЗИЛ-131" то в ячейке столбца "гос. номер" в выпадающем списке были отобраны все гос. номера ЗИЛ-131,
Если я что то не до понял не обессудьте как понял так и сделал.


Ещё раз пересмотрел и как показывало при выборе любой марки машины, так и показывает!!! Изменений не нашел!
Название: Re: Списки в Excel
Отправлено: vidam от 11.07.2011, 14:22
Привет. Хочу разобраться со связанными списками, никак не могу наайти то, что мне нужно. Есть лист с двумя столбцами
location1   code1
location2   code2
locaion3    code 3
...
locaion10  code 10



на другом листе в ячейке я сделал выпадающий список, где выбираю location (из десяти). Мне нужно, чтобы когда я выбираю location3, во второй ячейке автоматически ставился code3,
а если я не выбираю, а вношу новое значение location, то во второй ячейке ставилось LOCAL

Как мне с этим разобраться, подскажите пожалуйста.
Название: Re: Списки в Excel
Отправлено: cheshiki1 от 12.07.2011, 10:31
в соседней ячейке будет формула типа:
=ЕСЛИ(ВПР($B$2;Лист1!$B$2:$C$31;2;ЛОЖЬ)=0;"LOCAL";ВПР($B$2;Лист1!$B$2:$C$31;2;ЛОЖЬ))
Название: Re: Списки в Excel
Отправлено: vidam от 12.07.2011, 20:27
Спасибо, я так и думал, что необходимо применять функцию ВПР.
Но это немного не то, подобный пример я уже видел на одном из сайтов, он не подходит. У меня есть 10 фиксированных location, и когда их выбираешь из списка в ячейке Лист2!$B$2, должен автоматически подставляться code в ячейке Лист2!$C$2, соответствующий этому locaion. В вашем примере это реализовано, но нужно еще, чтобы я мог ввести в ячейку Лист2!$B$2 с клавиатуры новое location, например airport, и в ячейке Лист2!$C$2 всегда в этом случае появлялось LOCAL. Также, мне не нужно, чтобы эти новые location в последующем появлялись в моем выпадающем списке, там их должно быть только 10.
Название: Re: Списки в Excel
Отправлено: cheshiki1 от 13.07.2011, 09:18
по вкладке лист2 клик правой кнопкой - Исходный код. Удаляешь весь код макроса.
в ячейке С2 заменяешь формулу на : =ЕСЛИ(ЕОШИБКА(ВПР($B$2;Лист1!$B$2:$C$31;2;ЛОЖЬ));"LOCAL";ВПР($B$2;Лист1!$B$2:$C$31;2;ЛОЖЬ))
формула написана в 2007, в 2003 не знаю есть ли функция  "ЕОШИБКА" если нет работать не будет - нужно искать аналог.
Название: Re: Списки в Excel
Отправлено: vidam от 13.07.2011, 15:28
Спасибо! :)  Это то, что нужно. У меня Эксель 2007 и дома и на работе, так что все работает. Я примерно понял, как это делается, но надо получше изучить фунцию ВПР.
Название: Re: Списки в Excel
Отправлено: cheshiki1 от 13.07.2011, 17:25
в ВПР есть недостаток если искомых значений два в таблице, то он берет первый в списке.
Название: Re: Списки в Excel
Отправлено: vidam от 24.07.2011, 01:11
Цитата: cheshiki1 от 29.06.2011, 14:12
на листе 20110701 в столбце "В" я оставил как у тебя, зато в "С" исправлено, при выборе в ячейка В... марки машины в С.. показываются только номера данной марки а не все как раньше было.

не могли бы вы поподробнее объяснить, как вы так сделали? мне нужно сделать то же самое, но я не могу разобраться. То есть чтобы в соседней ячейке появлялся список из номеров именно той марке, которая выбрана в первой ячейке. Файл я скачал, но разобраться не могу.
Название: Re: Списки в Excel
Отправлено: IKor от 24.07.2011, 09:02
2 Vadim
Цитата: vidam от 24.07.2011, 01:11
То есть чтобы в соседней ячейке появлялся список из номеров именно той марке, которая выбрана в первой ячейке. Файл я скачал, но разобраться не могу.
Выделите соответствующую ячейку в столбце B или C и перейдите в меню Данные/Проверка. Вы увидите, что на возможные значения ячейки наложено ограничение - выбор из списка. Список формируется при помощи формулы - скопируйте формулу, вставьте ее на на листе отдельно и разберитесь с ней.
Вероятно, вам будет удобнее понять как она работает, если Вы выделите сразу несколько ячеек на листе, расположенных друг под другом, вставите в в верхнюю скопированную формулу и нажмете CTRL+SHFT+ENTER вместо ENTER - таким образом вводятся формулы массива.
Возможно, Вам придется разобраться с использованием именованных ячеек (диапазонов ячеек) - смотрите меню ВСТАВКА/ИМЯ/Присвоить.

Ну и последнее, выше, в начале этой темы разбирались несколько сходных примеров; кроме того, вы всегда можете воспользоваться поиском по ключевым словам "зависимые списки" или "связянные списки".

Желаю удачи!
Название: Re: Списки в Excel
Отправлено: cheshiki1 от 24.07.2011, 12:47
Мой вариант у хотабыча почему то не прошел. Скачай у С.М. следующий пост вроде там рабочий.
Для того чтоб разобраться согласен с цитированием IKor и обратите внимание на именованные диапазоны.
Название: Re: Списки в Excel
Отправлено: hottabych69 от 25.07.2011, 12:21
Цитата: vidam от 24.07.2011, 01:11
Цитата: cheshiki1 от 29.06.2011, 14:12
на листе 20110701 в столбце "В" я оставил как у тебя, зато в "С" исправлено, при выборе в ячейка В... марки машины в С.. показываются только номера данной марки а не все как раньше было.

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

Там без скрипта на VBA не обойтись! Обратись за помощью к С.М. - он МОНСТР в программировании VBA! Мне он помог и огромное человеческое спасибо ему за это! Как только появится возможность - отблагодарю материально  ;)
Название: Re: Списки в Excel
Отправлено: cheshiki1 от 25.07.2011, 13:38
vidam
Цитата: vidam от 24.07.2011, 01:11
Цитата: cheshiki1 от 29.06.2011, 14:12
на листе 20110701 в столбце "В" я оставил как у тебя, зато в "С" исправлено, при выборе в ячейка В... марки машины в С.. показываются только номера данной марки а не все как раньше было.

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

почитай здесь http://www.planetaexcel.ru/tip.php?aid=64 (http://www.planetaexcel.ru/tip.php?aid=64)
может поможет понять.
Название: Re: Списки в Excel
Отправлено: oliakras от 05.08.2011, 13:04
Здравствуйте, у меня схожая проблема (на мой взгляд) есть таблица, в которой данные из второго столбика зависят от первого, из 3-го от 2-го и т д Нужно сделать "форму" в которой:
1. в первой ячейке выбираются данные из первого столбика
2 во второй ячейке выбираются данные из второго, но которые зависят от значения в первой
3 в третьей ячейке автоматически подставляется значение соответствующее.... в общем табличка во вложении и там же чего хотелось бы видеть. Заранее благодарю за любую помощь
Или подскажите хотябы тему))
Название: Re: Списки в Excel
Отправлено: cheshiki1 от 05.08.2011, 16:03
Сделано для значений 6-9, Для остальных по аналогии сделайте именованные диапазоны.
Название: Re: Списки в Excel
Отправлено: war777 от 09.08.2011, 16:24
Цитата: cheshiki1 от 13.07.2011, 17:25
в ВПР есть недостаток если искомых значений два в таблице, то он берет первый в списке.
А как нибудь с этим можно бороться...я имею ввиду чтоб Впр находил все значения??
Название: Re: Списки в Excel
Отправлено: Serge 007 от 09.08.2011, 16:32
Цитата: war777 от 09.08.2011, 16:24
Цитата: cheshiki1 от 13.07.2011, 17:25
в ВПР есть недостаток если искомых значений два в таблице, то он берет первый в списке.
А как нибудь с этим можно бороться...я имею ввиду чтоб Впр находил все значения??
Во-первых это не недостаток. Функции предназначены для определённых действий. Бессмысленно требовать от телевизора, что бы он копал картошку. Он попросту для этого не предназначен.
Во-вторых нельзя. ВПР ищет первое вхождение искомого в массив. Это цель работы этой функции - найти первое вхождение. Если нужно что-то другое - используйте другую функцию. Или несколько функций. Или формулы. Или макросы. Или сводную таблицу.
Название: Re: Списки в Excel
Отправлено: war777 от 09.08.2011, 17:28
Цитата: Serge 007 от 09.08.2011, 16:32
Цитата: war777 от 09.08.2011, 16:24
Цитата: cheshiki1 от 13.07.2011, 17:25
в ВПР есть недостаток если искомых значений два в таблице, то он берет первый в списке.
А как нибудь с этим можно бороться...я имею ввиду чтоб Впр находил все значения??
цель работы этой функции - найти первое вхождение. Если нужно что-то другое - используйте другую функцию. Или несколько функций. Или формулы. Или макросы. Или сводную таблицу.
Дак всё таки чем лучше воспользоваться??
Название: Re: Списки в Excel
Отправлено: Serge 007 от 09.08.2011, 19:17
Цитата: war777 от 09.08.2011, 17:28
Цитата: Serge 007 от 09.08.2011, 16:32
Цитата: war777 от 09.08.2011, 16:24
Цитата: cheshiki1 от 13.07.2011, 17:25
в ВПР есть недостаток если искомых значений два в таблице, то он берет первый в списке.
А как нибудь с этим можно бороться...я имею ввиду чтоб Впр находил все значения??
цель работы этой функции - найти первое вхождение. Если нужно что-то другое - используйте другую функцию. Или несколько функций. Или формулы. Или макросы. Или сводную таблицу.
Дак всё таки чем лучше воспользоваться??
Зависит от того какие у Вас данные, как расположены, что именно и где надо получить, много от чего...
Выбирайте то, что подойдёт именно под вашу задачу.
Название: Re: Списки в Excel
Отправлено: GWolf от 09.08.2011, 22:56
Цитата: war777 от 09.08.2011, 17:28
Цитата: Serge 007 от 09.08.2011, 16:32
Цитата: war777 от 09.08.2011, 16:24
Цитата: cheshiki1 от 13.07.2011, 17:25
в ВПР есть недостаток если искомых значений два в таблице, то он берет первый в списке.
А как нибудь с этим можно бороться...я имею ввиду чтоб Впр находил все значения??
цель работы этой функции - найти первое вхождение. Если нужно что-то другое - используйте другую функцию. Или несколько функций. Или формулы. Или макросы. Или сводную таблицу.
Дак всё таки чем лучше воспользоваться??

Доброй ночи!

На мой взгляд, наиболее универсальным инструментом является VBA. Хотя, нужно признать, это инструмент требующий творческого им владения. Впрочем, как и все остальные инструменты выше перечисленные.
Название: Re: Списки в Excel
Отправлено: IKor от 10.08.2011, 15:06
Цитата: war777 от 09.08.2011, 16:24
Цитата: cheshiki1 от 13.07.2011, 17:25
в ВПР есть недостаток если искомых значений два в таблице, то он берет первый в списке.
А как нибудь с этим можно бороться...я имею ввиду чтоб Впр находил все значения??
Как мы понимаем принципиально одна функция возвращает результат своей работы только в одну ячейку.
Очень вероятно, что Вам не требуется перечисление найденных функцией "a-la ВПР" значений в одной ячейке через запятую. Значит как минимум придется использовать дополнительные ячейки (столбцы) для формирования результата.

Если по каким-то причинам вас не устраивает "статичность" сводных таблиц и "чрезмерная универсальность" VBA, то скорее всего Вам придется близко познакомиться с многообразием функций Excel, а именно с функциями ЕСЛИ(), ЕОШИБКА(), ПОИЗПОЗ(), ИНДЕКС(), СМЕЩ(), НАИМЕНЬШИЙ(), СЧЁТЕСЛИ(); а также с понятиями {формул массива}, именованных диапазонов и до кучи :) условного форматирования.

Подробнее можно будет сказать после ознакомления с Вашим примером, желаемым результатом и граничными условиями.
Название: Re: Списки в Excel
Отправлено: Serge 007 от 10.08.2011, 17:29
Цитата: IKor от 10.08.2011, 15:06
Как мы понимаем принципиально одна функция возвращает результат своей работы только в одну ячейку.
Или в несколько, если это формула массива введённая как массив в несколько ячеек ;)
Название: Re: Списки в Excel
Отправлено: IKor от 10.08.2011, 17:41
Цитата: Serge 007 от 10.08.2011, 17:29
Цитата: IKor от 10.08.2011, 15:06
Как мы понимаем принципиально одна функция возвращает результат своей работы только в одну ячейку.
Или в несколько, если это формула массива введённая как массив в несколько ячеек ;)
Согласен, но даже формулу массива не получится ввести в заранее неизвестное количество ячеек.
Название: Re: Списки в Excel
Отправлено: Ukraino4ka от 16.08.2011, 22:40
привет всем!новенькая я у вас,но не особо новенькая в данной программке
взяла на дом работу и никак не могу вспомнить следующее:

у меня есть исходный список (назовем его А) и есть список (Б) с которым надо его сравнить с первым список на предмет уникальности.

каждый список состоит из 6-7 доп столбцов,которые по сути одинаковые (А2=Б2,А3=Б3 итд).

то есть сопоставить 2 списка мне надо по столбцУ Х,а 6-7 доп надо чтоб выбились с ними

не знаю как правильно объяснить: вообще мне надо проверить есть ли в А такие же позиции как в Б,вытащить не повторяющиеся для дальнейшей работы,при этом чтоб список был законченным мне надо чтоб инфо сдоп столбцов также отразились рядом в колнках и получился лист2 к примеру(столбец С +доп столбца с1,с2,с3=а1,а2,а3 итд соот-но)

помнится мне тя это делала через функцию MATCH

всем спасибо

жду!
Название: Re: Списки в Excel
Отправлено: IKor от 17.08.2011, 21:57
2 Ukrainochka
Для того, чтобы проверить входит ли конкретное значение списка Б в состав списка А есть множество вариантов формул, например:
=ЕСЛИ(СЧЁТЕСЛИ(Список_А;значение_Б)>=1;"значение присутствует";"значение отсутствует")
=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(значение_Б;Список_А;0));"значение отсутствует";"значение присутствует")
а вот прочая часть ваших рассуждений ускользнула от моего понимания.

Цитата: Ukraino4ka от 16.08.2011, 22:40
не знаю как правильно объяснить: <...>
жду!
И на прощанье совет: не ждите напоминаний - прикладывайте файл примера без секретной информации.
Название: Вывод среднего числа из списка с последующим разложением
Отправлено: dlack82 от 19.08.2011, 01:36
Добрый вечер уважаемые гуру Exel, помогите пожалуйста решить проблему(для меня) связанную с автоматизацией расчета, с самой программой связан поверхностно, а тут надо очень по глубже знания.Задача, все значения практически известны, надо брать известное число находить среднее между двумя в одном столбике, далее разложить эти два найденных значения(пример 10 и 20 разложить 10,11,12,13,14,15......20)и выбрать ближайшее к искомому. Более подробно изложил во вложении.
Можно ли все это уложить в одну формулу? или нужно писать макрос.
Если есть возможность подскажите с формулой.
За ранее спасибо.
Название: Re: Списки в Excel
Отправлено: kimtan от 19.08.2011, 02:30
Уложить можно.
Название: Re: Списки в Excel
Отправлено: megafon от 19.08.2011, 09:27
Здраствуйте! можно ли скопировать текст из ячейки, если он выводится туда по средством формулы? Например: есть три столбца таблицы Ф.И.О. я сцепил их в один на другой лист, а скопировать текст не получается, или можно объденить столбцы, без потери информации както по другому???
Название: Re: Списки в Excel
Отправлено: Teklya от 29.08.2011, 18:09
Findirector
       Списки в Excel
« : 20 Февраль 2008, 15:39:16 »       Цитировать

________________________________________
Hello users? Вопрос, как из выпадающего списка при нажатии на стрелочку вниз и при вводе первой буквы слова появлялось слово из списка.

vadkol
       Автоввод в выпадающем списке
« Ответ #31 : 09 Октябрь 2008, 19:32:53 »       Цитировать

________________________________________
Всем привет. Может кто поможет, в выпадающем списке очень большое колличество текстовых элементов, можно ли сделать  автоввод с клавиатуры в списке

alfadmin
       Re: Списки в Excel
« Ответ #188 : 06 Июль 2010, 14:31:53 »       Цитировать

________________________________________
Добрый день ! Интересует такой момент - есть очень большой список, все позиции списка уникальны, можно ли как то автоматизировать ввод данных из списка в таблицу(цы), таким образом, чтоб при вводе первых букв(символов) в ячейку, выпадающий список "прокручивался" бы на позиции, которые начинаются с этих букв, (примерно как в словаре лингво когда набираем слово, или скажем в строке поискового сайта - когда начинаем набирать запрос, яндекс- в выпадающем списке показывает возможные варианты),  заранее благодарю.

lopatochka
       Re: Списки в Excel
« Ответ #199 : 15 Сентябрь 2010, 20:01:48 »       Цитировать

________________________________________
Доброго дня, всем!
может все таки кто-нибудь подскажет: список создан, список содержит 2000 строк, нажимаем стрелочку
из выпадающего списка вниз, и поскольку список большой, хотелось бы вводить первые буквы нужной надписи и мы бы приближались к этому слову. Трудно выбирать из списка значение, если он просто огромный.

Я не нашла ответа, подскажите, плиз....
Название: Re: Списки в Excel
Отправлено: С.М. от 04.09.2011, 17:44
Правда не "стрелочка", а кнопочка вызывающая формочку:
https://forum.msexcel.ru/microsoft_excel/mozhno_li_osuschestvit_perehod_v_spiske_po_pervym_bukvam-t3608.0.html;msg21096#msg21096 (https://forum.msexcel.ru/microsoft_excel/mozhno_li_osuschestvit_perehod_v_spiske_po_pervym_bukvam-t3608.0.html;msg21096#msg21096)
Название: Re: Списки в Excel
Отправлено: Teklya от 05.09.2011, 16:15
Цитата: С.М. от 04.09.2011, 17:44
Правда не "стрелочка", а кнопочка вызывающая формочку:
https://forum.msexcel.ru/microsoft_excel/mozhno_li_osuschestvit_perehod_v_spiske_po_pervym_bukvam-t3608.0.html;msg21096#msg21096 (https://forum.msexcel.ru/microsoft_excel/mozhno_li_osuschestvit_perehod_v_spiske_po_pervym_bukvam-t3608.0.html;msg21096#msg21096)
спасибочки, не знала, что есть такая ветка, сейчас буду разбираться....
Название: Re: Списки в Excel
Отправлено: cheshiki1 от 06.09.2011, 00:07
Цитата: Teklya от 05.09.2011, 16:15
Цитата: С.М. от 04.09.2011, 17:44
Правда не "стрелочка", а кнопочка вызывающая формочку:
https://forum.msexcel.ru/microsoft_excel/mozhno_li_osuschestvit_perehod_v_spiske_po_pervym_bukvam-t3608.0.html;msg21096#msg21096 (https://forum.msexcel.ru/microsoft_excel/mozhno_li_osuschestvit_perehod_v_spiske_po_pervym_bukvam-t3608.0.html;msg21096#msg21096)
спасибочки, не знала, что есть такая ветка, сейчас буду разбираться....

Соорудил такую штуку, может кому пригодится или кто доработает, упростит.
Название: Re: Списки в Excel
Отправлено: Мадина от 09.09.2011, 14:45
друзья, подскажите пожалуйста, как быть с такими списками:

имеются 2 списка за разные годы.всего строк с параметрами больше 3 000.

нужно сравнить, как что изменилось за эти годы.

проблема в том, кто не все параметры совпадают. т.е., в одном списке нет парочки одних параметров, в другом - парочки других.  проверять все эти 3000 - анреал!

как свести все  одну сводную таблицу, исключив несовпадающие параматры?

буду очень благодарна, если кто донесет до меня ход действий! :)
Название: Re: Списки в Excel
Отправлено: С.М. от 09.09.2011, 14:59
Что такое:
Цитата: Мадина от 09.09.2011, 14:45
исключив несовпадающие параматры
?
Название: Re: Списки в Excel
Отправлено: Мадина от 10.09.2011, 18:35
то есть, нужно сравнить 2 таблицы. но в одной есть несколько параматров, которых нет в другой, и во второй тоже есть парочка, которых нет в первой. их  очень сложно найти, потому что всего параметров более 3000. а сравнить нужно только совпадающие параметры.

сама вижу, что вопрос получился не совсем понятный.

как сделать из 2х таблиц одну так, чтобы за 2 года имелся ряд одинаковых по названию показателей.
Название: Re: Списки в Excel
Отправлено: С.М. от 10.09.2011, 23:41
Типа так :
?
Название: Re: Списки в Excel
Отправлено: Мадина от 11.09.2011, 09:34
Ух ты!! С.М, спасибо большое!!!! :D
Название: Re: Списки в Excel
Отправлено: catherine от 15.09.2011, 13:05
Здравствуйте! Хотела бы обратиться к вам за помощью:
Предположим, в нашей организации есть Управления, которые дробятся на Департаменты, те в стою очередь содержат Отделы, а в некоторых отделах есть секторы. Мне нужно автоматизировать процесс подстановки названий таким образом, чтобы на одной строчке в 4 столбцах были: Управление, департамент, отдел, сектор, или просто Управление, или просто Управление, Департамент. Ну и вот так заполнять часть листа, пробовала делать выпадающий список через ActiveX, но там можно привязаться только к 1 ячейке (LinkedCell), тогда как мне нужно 4, ну и опять же, в BoundColumn показывается только одна из позиций.
Как можно от этого отвязаться? Пробовала делать через Проверку данных, так, чтобы группировалось так: Департамент, из него выбираем отделы, и т.п., но ничего не получилось :) Может, посоветуете пошагово для Excel2007?
Название: Re: Списки в Excel
Отправлено: С.М. от 15.09.2011, 16:06
А обычный _автофильтр_ (https://msexcel.ru/content/view/27/2/) не подойдёт (?) :
Название: Re: Списки в Excel
Отправлено: IKor от 15.09.2011, 17:22
Цитата: catherine от 15.09.2011, 13:05
Предположим, в нашей организации есть Управления, которые дробятся на Департаменты, те в стою очередь содержат Отделы, а в некоторых отделах есть секторы. Мне нужно автоматизировать процесс подстановки названий таким образом, чтобы на одной строчке в 4 столбцах были: Управление, департамент, отдел, сектор, или просто Управление, или просто Управление, Департамент.
<...>
Пробовала делать через Проверку данных, так, чтобы группировалось так: Департамент, из него выбираем отделы, и т.п., но ничего не получилось :) Может, посоветуете пошагово для Excel2007?
Попробуйте еще раз. Должно получиться :)

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

На втором листе показан пример использования Проверки данных для формирования связанных списков.
В каждой ячейке используется один и тот же динамический диапазон данных, зависящий от значения соседней слева ячейки (если нужно изменить - правьте имя HiLevel). Главный "недостаток" в том, что зависимость от смежной ячеки закольцована - как только функция не может продолжиться, она начинает формировать самый верхний список. Если это сильно мешает, то можно изменить формулу.
Название: Re: Списки в Excel
Отправлено: catherine от 16.09.2011, 09:41
Это именно то, что мне нужно, спасибо огромное! :)
Название: Re: Списки в Excel
Отправлено: toker от 26.09.2011, 12:55
Мне передают такие списки (вложение input). Как автоматизировать, чтобы на место пропущенных квартир вставлялась строчка  ул. дом и номер пропущенной квартиры (выделил жирным в output) или хотя-бы пропущенная кв. Подскажите, ничего не получается.
Название: Re: Списки в Excel
Отправлено: sanitarmisha от 26.09.2011, 20:28
Доброго времени суток!) подскажите каким образом легче решить поставленную задачу:
есть:
списки групп людей (не одинаковые по длинне), их предположительно (мое мнение) лучше разместить отдельно каждый на странице
надо:
на другом листе или в другом файле (наверное лучше), из выпадающего списка видеть эти все листы, при выборе одного из них автоматически заполнять таблицу (переносить некоторые данные)...
посоветуйте:
1. с какой стороны подойти, что лучше использовать? т.к. я поверхностно знаком с excel, а задачу хотелось бы начать решать по быстрее)
2. возможно лучше не списки держать не на отдельных листах, а просто дать название диапазонам?
3. как сделать выпадающий список из листов книги?
4. каким образом можно приклееть событие (т.е. выбор листа)?

Заранее благодарен вам добрые люди!) Хорошего вечера всем =)
Название: Re: Списки в Excel
Отправлено: sz от 28.10.2011, 17:53
Уважаемые коллеги, подскажите пожалуйста каким образом можно добиться, чтобы выпадающие списки всегда отражались вот так:

(http://i30.fastpic.ru/thumb/2011/1028/63/84cbd30b1d4c5c94712ba3852e51a763.jpeg) (http://fastpic.ru/view/30/2011/1028/84cbd30b1d4c5c94712ba3852e51a763.jpeg.html) (http://i30.fastpic.ru/thumb/2011/1028/e3/ac4d4ee742e8425ddbcaff1e8a5b02e3.jpeg) (http://fastpic.ru/view/30/2011/1028/ac4d4ee742e8425ddbcaff1e8a5b02e3.jpeg.html) (http://i30.fastpic.ru/thumb/2011/1028/b9/718747c457e07c86f676834ceec679b9.jpeg) (http://fastpic.ru/view/30/2011/1028/718747c457e07c86f676834ceec679b9.jpeg.html)

в настоящий момент они выглядят так:

(http://i30.fastpic.ru/thumb/2011/1028/08/c1926d6c2e6b097032843e5caabe3a08.jpeg) (http://fastpic.ru/view/30/2011/1028/c1926d6c2e6b097032843e5caabe3a08.jpeg.html) (http://i30.fastpic.ru/thumb/2011/1028/5b/1361a74b2803a53f72f41ef7046cf05b.jpeg) (http://fastpic.ru/view/30/2011/1028/1361a74b2803a53f72f41ef7046cf05b.jpeg.html) (http://i30.fastpic.ru/thumb/2011/1028/e8/fdc624910ce2ce2cc23d5806b03f86e8.jpeg) (http://fastpic.ru/view/30/2011/1028/fdc624910ce2ce2cc23d5806b03f86e8.jpeg.html)

причем переключение между этими двумя вариантами происходит по неизвестным мне причинам.
Название: Re: Списки в Excel
Отправлено: adidas от 01.11.2011, 23:39
Помогите плиз в
экселе сделать
1)Разработать систему расчета
среднего пробега автомобиля
без дозаправки в зависимости
от ряда факторов: городское
или загородское движение,
емкость бензобака, литраж
двигателя .
2) В процедуре вводятся
четыре
целых числа. Разделить
максимальное из них на
минимальное. Вывести
делитель, делимое и частное.
Определить имеют ли они
одинаковую четность (или не
четность) и вывести
соотвествующее сообщение.
3) даны 5 чисел. Определить ,
сколько из них
отрицательных, сколько
положительных и сколько
нулей. Если введены не числа
вывести сообщение об
ошибке. Вычислить сумму
положительных чисел и
произведение отрицательных.
Название: Re: Списки в Excel
Отправлено: С.М. от 02.11.2011, 01:07
adidas, Вы разместили своё сообщение в разделе "Списки в Excel" потому что это список Ваших "хочу: 1) 2) 3)" ?
Название: Re: Списки в Excel
Отправлено: Bob1976 от 28.01.2012, 22:07
Здравствуйте!!!
Возможно ли, чтобы вместо списка, появлялся календарь, для ввода нужного года, месяца, числа?
Название: Re: Списки в Excel
Отправлено: cheshiki1 от 02.02.2012, 10:08
Цитата: Bob1976 от 28.01.2012, 22:07
Здравствуйте!!!
Возможно ли, чтобы вместо списка, появлялся календарь, для ввода нужного года, месяца, числа?
поищите это samradDatePicker.xla может как раз то что вам нужно.
Название: Re: Списки в Excel
Отправлено: Bob1976 от 03.02.2012, 20:21
Цитата: cheshiki1 от 02.02.2012, 10:08
Цитата: Bob1976 от 28.01.2012, 22:07
Здравствуйте!!!
Возможно ли, чтобы вместо списка, появлялся календарь, для ввода нужного года, месяца, числа?
поищите это samradDatePicker.xla может как раз то что вам нужно.
Большое спасибо!!! У меня Excel2007 все работает, если кому надо, то вкладываю тот самый samradDatePicker.xla
Название: Re: Списки в Excel
Отправлено: nash_t от 20.02.2012, 10:16
Необходимо что бы в поле адреса после набора хотя бы первой буквы выводился список только с этой буквой. Как это сделать, кто поможет.?
Название: Re: Списки в Excel
Отправлено: nash_t от 20.02.2012, 12:54
Цитировать

Соорудил такую штуку, может кому пригодится или кто доработает, упростит.

Посмотрел, не могу только пока понять как в нескольких ячейках сделать поиск. Для моих целей очень даже подходит.
Название: Re: Списки в Excel
Отправлено: cheshiki1 от 21.02.2012, 09:35
этот пример посмотрите может тор что нужно.
Название: Re: Списки в Excel
Отправлено: Buka от 11.03.2012, 19:11
Добрый день.
Помогите с решением такой "задачки". Сразу извиняюсь, если где-то ранее было решение подобной задачи. если было киньте ссылку плиз.
Файл с тестом прикрепила.
Есть два типа товаров - НСП и ПСН, оба в разных модификациях. Оба из них изготовляются из одинаковых материалов - зеркального и матированного алюминия. и каждый в итоге получает свое дополнительное название (например, НСП200 GMX607 - т.е. товар НСП модификации 200 с корпусом GMX607). Мне нужно, чтобы появлялся список выбора корпуса, зависящий от двух списков - Товара и Материала.
Возможно очень сумбурно объяснила, надеюсь по файлу более понятно станет.

Заранее спасибо.
Название: Re: Списки в Excel
Отправлено: С.М. от 11.03.2012, 22:43
Воть:
Название: Re: Списки в Excel
Отправлено: Buka от 12.03.2012, 23:00

С.М. большое спасибо за помощь. попробую сгенерить это в свой файл.
Название: Re: Списки в Excel
Отправлено: lenkamath от 16.04.2012, 22:38
Добрый вечер! Подскажите, как сделать подсписок?
Название: Re: Списки в Excel
Отправлено: IKor от 12.05.2012, 10:02
Существует несколько вариантов, например так:
1. Выбираете ячейку, в которой должен находиться выпадающий список;
2. Переходите в меню ДАННЫЕ\ПРОВЕРКА_ДАННЫХ и выбираете ограничение СПИСОК
3. В появившемся окне даете ссылку на ячейки, содержащие источник данных:
   a. =$B$3:$B$6 - прямая ссылка (работает только для ячеек, расположенных на том же листе, что и сам список)
   b. =Животные - именованная ссылка (работает вне зависимости от взаимного расположения источника_данных и списка, но предварительно требуется присвоить имя Животные указанному диапазону => см. меню ВСТАВКА\ИМЯ\ПРИСВОИТЬ)
   с. =СМЕЩ($B$2;1;0;СЧЁТЗ($B:$B)-1;1) - формула, возвращающая динамически изменяемый список животных; ограничения: новых животных в список нужно добавлять без пропусков, ненужные значения в столбце B не допускаются;
4. Проверить работоспособность вновь-созданного поля со списком
5. Скопировать ячейку во все остальные места, где требуется организовать такое же поле со списком.
Название: Re: Списки в Excel
Отправлено: gamaun от 26.06.2012, 12:13
Здравствуйте, специалисты! Очень нужна Ваша помощь! Есть файлик, на котором листы защищены паролем от изменения и выделения(за исключением некоторых ячеек). На вкладке «рабочие» нужно отсортировать определённый диапазон без снятия защиты. Я попытался записать макрос, но он выполняет сортировку данных только той области которая была выделена при записи. В VBA я ни в зуб ногой, что бы подправить макрос. Остаётся надеяться на Вашу помощь. Надо что бы макрос выполнял сортировку выделенной области или привязать параметры сортировки к диапазону указанному в ячейках AY4 и AZ4, BA4 и BВ4 и критерию BС4. На вкладке «ИТР тариф» такая же задача, но там диапазон с разрывом. Заранее благодарен!
Название: Re: Списки в Excel
Отправлено: GWolf от 27.06.2012, 06:42
Цитата: gamaun от 26.06.2012, 12:13
Здравствуйте, специалисты! Очень нужна Ваша помощь! Есть файлик, на котором листы защищены паролем от изменения и выделения(за исключением некоторых ячеек). На вкладке «рабочие» нужно отсортировать определённый диапазон без снятия защиты. Я попытался записать макрос, но он выполняет сортировку данных только той области которая была выделена при записи. В VBA я ни в зуб ногой, что бы подправить макрос. Остаётся надеяться на Вашу помощь. Надо что бы макрос выполнял сортировку выделенной области или привязать параметры сортировки к диапазону указанному в ячейках AY4 и AZ4, BA4 и BВ4 и критерию BС4. На вкладке «ИТР тариф» такая же задача, но там диапазон с разрывом. Заранее благодарен!

Я стесняюсь спросить: - А какое отношение Ваша проблема имеет к теме "Списки в Excel"? Может, все же, следовало просто создать отдельную тему?

По проблеме: Пример посмотреть не удалось. У меня 2003! Пишет что сбой конвертера. Но, полагаю, если Вы поиском посмотрите на тему "установка диапазона", то найдете много для себя интересного. Ну, или попробуйте выложить отдельно записанный Вами макрос; файл примера в формате Excel 2003.

Успеха!
Название: Re: Списки в Excel
Отправлено: gamaun от 27.06.2012, 07:56
Спасибо! Попытаюсь найти, в темах. Если что то тогда создам отдельную тему.
Название: Re: Списки в Excel
Отправлено: gamaun от 27.06.2012, 10:36
К сожалению подходящего ничего не нашёл. Создам отдельную тему и файл в 2003 выложу.
Название: Re: Списки в Excel
Отправлено: GWolf от 28.06.2012, 09:54
И в этой теме ссылку на созданную тему. Постараюсь помочь.
Название: Re: Списки в Excel
Отправлено: gamaun от 28.06.2012, 12:11
Ссылка новой темы:
https://forum.msexcel.ru/empty-t8218.0.html (https://forum.msexcel.ru/empty-t8218.0.html)
Там файл в 2003 формате. Спасибо!
Название: Re: Списки в Excel
Отправлено: Bonne_journee от 29.06.2012, 13:55
Добрый день!
У меня наверно самый простой вопрос, но я никак не погу найти ответ на него!
Вот в чем суть:
Есть таблица, первый столбец-наименование позиции, второй-наличие ее на складе; нужно создать таблицу в которую автоматически заносятся названия только имеющихся позиций. Это не должен быть выпадающий список, это должна быть обычная таблица.
Пытался сделать через если, но как сделать так, чтобы пропускались строчки с тем чего нет на складе не могу понять...
Заранее спасибо!
Название: Re: Списки в Excel
Отправлено: Barbarian12 от 29.06.2012, 14:04
Цитата: Bonne_journee от 29.06.2012, 13:55
Добрый день!
У меня наверно самый простой вопрос, но я никак не погу найти ответ на него!
Вот в чем суть:
Есть таблица, первый столбец-наименование позиции, второй-наличие ее на складе; нужно создать таблицу в которую автоматически заносятся названия только имеющихся позиций. Это не должен быть выпадающий список, это должна быть обычная таблица.
Пытался сделать через если, но как сделать так, чтобы пропускались строчки с тем чего нет на складе не могу понять...
Заранее спасибо!
Наверное, так: =ЕСЛИ(B1="";A1;""). Функция работает следующим образом: если ячейка В1 пустая, то отображается значение ячейки А1, если нет - то отображается просто пустое значениею
Название: Re: Списки в Excel
Отправлено: Bonne_journee от 29.06.2012, 14:51
Спасибо, но мне как раз нужно чтобы не было пустых полей, чтобы вместо них шла следующая позиция имеющаяся на складе.
Название: Re: Списки в Excel
Отправлено: cheshiki1 от 29.06.2012, 15:54
ну раз это не список то создаем новую тему и туда прикладываем свой файл.
возможны два решения:
1. с доп. столбцом.
2.  формула массива.  Но это точней будет видно после рассмотрения примера.
Название: Re: Списки в Excel
Отправлено: Xkirill от 11.07.2012, 22:47
Доброго всем времени суток.
интересно получилось ли у кого реализовать такое, только списками без колонок.
имеем таблицу
Номер Категории ; Имя Категории ; Номер Подкатегории ; Имя подкатегории
например(пустые номера, они нам в принципе не нужны):

   LICORES      TEQUILA
   LICORES      VERMOUTH
   LICORES      VODKA
   SAKE      JAPON
   SAKE      OTROS
   ZUMOS      FRUTAS
   ZUMOS      VEGETALES
   ZUMOS      OTROS ZUMOS
   AGUAS      AGUA CON GAS
   AGUAS      AGUA SIN GAS
   SOFT DRINKS      BOTELLA
   SOFT DRINKS      LATA
   SOFT DRINKS      B I B
   SOFT DRINKS      PLASTICO
   SOFT DRINKS      JARABES
   VINOS GENEROSOS      NACIONAL
   VINOS GENEROSOS      IMPORTACION

сформировать список уникальных категорий без колонки у меня  не получилось, это ладно, делаю его руками и создаю список.
а вот как сформировать динамическую выборку для подкатегорий.
например:
я заполняю страницу продукта, в колонке категория выбираю из заранее сформированного списка("=CategoryList") категорию
далее хочу выбрать в колонке подкатегория, значения из общего списка для  соответствующей выбранной категории.
пока что получилось это реализовать тока генерацией макросом списков. и в проверке данных " =ДВССЫЛ(СЦЕПИТЬ("G_SC_";..предыдущя колонка...."

если кто сталкивался подскажите пожалуйста

п.с. без макросов :)



Название: Re: Списки в Excel
Отправлено: Serge 007 от 11.07.2012, 22:59
Ничего не понятно.
Выкладывайте пример "Есть так - надо так"
Название: Re: Списки в Excel
Отправлено: Xkirill от 12.07.2012, 12:48
колонки с цветным(желтым) заголовком это выпадающие списки.
хотелось бы при заполнении страницы products:
1. выпадающий список категорий, не по отдельно сформированной колонке, а динамическим по целой таблице, с возможностью добавлять.
2. выпадающий список подкатегорий, не по сгенерённому макросом списку("G_CS_....") для этой категории, а чтобы тоже список формировался какбуд-то по фильтру с общей таблицы.
Название: Re: Списки в Excel
Отправлено: IKor от 12.07.2012, 15:50
Во-первых, хочу процитировать самого себя:
Цитата: IKor от 28.06.2012, 07:15
2 Alexandr Zlobin
Посмотрите поиском по ключевым словам: зависимые или связанные списки:

http://msexcel.ru/content/view/103?cx=partner-pub-4363176700647597%3A6761453897&cof=FORID%3A10&ie=Windows-1251&q=%E7%E0%E2%E8%F1%E8%EC%FB%E5+%F1%EF%E8%F1%EA%E8&sa=%CF%EE%E8%F1%EA (http://msexcel.ru/content/view/103?cx=partner-pub-4363176700647597%3A6761453897&cof=FORID%3A10&ie=Windows-1251&q=%E7%E0%E2%E8%F1%E8%EC%FB%E5+%F1%EF%E8%F1%EA%E8&sa=%CF%EE%E8%F1%EA)

https://forum.msexcel.ru/microsoft_excel/spiski_v_excel-t6.255.html (https://forum.msexcel.ru/microsoft_excel/spiski_v_excel-t6.255.html)

https://forum.msexcel.ru/microsoft_excel/zavisimye_spiski-t1333.0.html (https://forum.msexcel.ru/microsoft_excel/zavisimye_spiski-t1333.0.html)
Во-вторых, могу сказать, что формируя динамически-изменяемый список не получиться совершенно отказаться от вспомогательного столбца - для формирования источника строк понадобится ссылка на реальные ячейки, а не на виртуальные значения - зато можно на вспомогательном листе предусмотреть столбец достаточной высоты для того, чтобы в нем поместились все уникальные значения категорий (подкатегорий).

В-третьих, надо сказать, что к сожалению ДВССЫЛ() не работает с результатами функций а-ля СМЕЩ() в качестве аргумента. Поэтому для генерации зависимых списков придется экспериментировать с текстовыми строками вида "A2:A"&СЧЁТЗ(A:A)
Название: Re: Списки в Excel
Отправлено: Xkirill от 12.07.2012, 15:55
Цитата: IKor от 12.07.2012, 15:50
зато можно на вспомогательном листе предусмотреть столбец достаточной высоты
вроде если потом всю строку удалить, или какие то ячейки со смещением в исходной большой таблице. то потом "сыпется" формула и в дополнительном столбце
Название: Re: Списки в Excel
Отправлено: IKor от 16.07.2012, 17:21
Цитата: Xkirill от 12.07.2012, 15:55
вроде если потом всю строку удалить, или какие то ячейки со смещением в исходной большой таблице. то потом "сыпется" формула и в дополнительном столбце
Это как повезет: если удалять строки, содержащие ячейки, на которые именно ссылаются формулы дополнительного столбца, то действительно возникнет проблема #ССЫЛКА.
Но если аккуратно удалять строки из середины, сохраняя первое и последнее значения, то дополнительный столбец должен продолжать работать.
Кроме того, в дополнительном столбце можно использовать формулу вида =СМЕЩ(Ссылка_на_заведомо_неудаляемую_ячейку;...) - тогда будет меньше проблем...
Название: Re: Списки в Excel
Отправлено: tulyanin от 01.08.2012, 17:57
Здравствуйте. Задача в приложенном файле.
Выбрал след. вариант решения, но не знаю получится ли:
создал поле со списком , к ячейке привязал рисунок.Возможно ли сделать так, чтобы при выборе пункта из списка, рисунок вставлялся в любую нужную мне ячейку?
Нужны любые варианты решения.
Заранее спасибо!
Название: Re: Списки в Excel
Отправлено: cheshiki1 от 02.08.2012, 09:26
здесь почитайте http://www.planetaexcel.ru/tip.php?aid=57 (http://www.planetaexcel.ru/tip.php?aid=57)
Название: Re: Списки в Excel
Отправлено: tulyanin от 02.08.2012, 10:25
Цитата: cheshiki1 от 02.08.2012, 09:26
здесь почитайте http://www.planetaexcel.ru/tip.php?aid=57 (http://www.planetaexcel.ru/tip.php?aid=57)

Спасибо.
Название: Re: Списки в Excel
Отправлено: Старик от 31.08.2012, 23:43
доброго времени суток. сломал мозг но просьбу руководителя выполнил. есть 2 листа на 2ом таблица с наименованием изделия с данными. на 1ом листе пустая таблица. сделал выпадающий список. все хорошо. но таблица на 2ом листе увеличивается => удлинняется выпадающий список на 1ом листе. говорят что неудобно. просят сделать так чтобы когда начинаеш вводить наименование он сразу предлогал похожие варианты по мере ввода, итог если продолжить вводить слово останется 1 которое полностью совпадает. Например, начинаю вводить слово: проф, в выпадающем списке остаются только наименования профиль. далее продолжаю профиль С, остаються в списке профиль С. как это сделать в моем примере.
Название: Re: Списки в Excel
Отправлено: cheshiki1 от 09.09.2012, 10:29
http://www.planetaexcel.ru/forum.php?thread_id=36826 (http://www.planetaexcel.ru/forum.php?thread_id=36826) - это посмотрите.
Название: Re: Списки в Excel
Отправлено: Alxxxx74 от 28.09.2012, 14:18
Цитата: IKor от 12.05.2012, 10:02
Существует несколько вариантов, например так:
1. Выбираете ячейку, в которой должен находиться выпадающий список;
2. Переходите в меню ДАННЫЕ\ПРОВЕРКА_ДАННЫХ и выбираете ограничение СПИСОК
3. В появившемся окне даете ссылку на ячейки, содержащие источник данных:
   a. =$B$3:$B$6 - прямая ссылка (работает только для ячеек, расположенных на том же листе, что и сам список)
   b. =Животные - именованная ссылка (работает вне зависимости от взаимного расположения источника_данных и списка, но предварительно требуется присвоить имя Животные указанному диапазону => см. меню ВСТАВКА\ИМЯ\ПРИСВОИТЬ)
   с. =СМЕЩ($B$2;1;0;СЧЁТЗ($B:$B)-1;1) - формула, возвращающая динамически изменяемый список животных; ограничения: новых животных в список нужно добавлять без пропусков, ненужные значения в столбце B не допускаются;
4. Проверить работоспособность вновь-созданного поля со списком
5. Скопировать ячейку во все остальные места, где требуется организовать такое же поле со списком.
Я такой же список в 2007 офисе делаю с помощью таблиц. Принцип тот же, однако он удобнее тем, что при расширении диапазона не нужно вносить изменения в формулу имени диапазона.
1. В любой книге обычно создаю лист, на котором у меня хранятся таблицы со справочниками. Обзаваю его что-то типа _СПР.
2. За тем в нем делаю список каких-либо данных, например контрагентов. Первая строка слово "Контрагенты", ниже их список. Выделяю диапазон вместе с первой строкой и вниз до конца данных. Потом меню - ВСТАВКА-ТАБЛИЦА - Таблица с заголовками-ОК. Обзываю таблицу типа Таб_СПР_Контрагенты.
2. Меню - ФОРМУЛЫ-ДИСПЕТЧЕР ИМЕН-СОЗДАТЬ... обзываю имя типа _СПР_Контрагенты. Все эти подчеркивания лишь для удобства. Когда в диспетчере имен полно этих имен и имен таблиц, бывает удобно когда именя диапазонов не перемешиваются с именами таблиц, тогда легче искать нужное.
3. В строке формулы имени прописываю следующее: =СМЕЩ(Таб_СПР_Контрагенты;0;0;СЧЁТЗ(Таб_СПР_Контрагенты);1). Сохраняю имя.
4. Ну а далее как обычно ДАННЫЕ-ПРОВЕРКА ДАННЫХ-СПИСОК в строке формулы списка =_СПР_Контрагенты.
В дальнейшем, если список контрагентов необходимо будет увеличить, сначала увеличивается сама таблица, вносятся дополнительные контрагенты, которые автоматом попадают в выпадающее меню. Таблица еще удобнее тем, что есть возможность ее сортировки и уже отсортированные данные мы имеем в выпадающем меню нашего списка.
Название: Re: Списки в Excel
Отправлено: Анна Томина от 29.11.2012, 13:23
excel 2010, имеется выпадающий список "Срок кредита" состоящий из чисел от 6 до 24. Вопрос: как создать таблицу (если конкретнее, то строки ниже шапки таблицы) зависящую от выбранного срока, т.е. если выбрали 6 мес., то автоматически на другом листе формируется таблица с шестью строками (каждая строка ровна одному платежу), если выбрать 12, то таблица с 12 месяцами и т.д?
Название: Re: Списки в Excel
Отправлено: IKor от 29.11.2012, 17:40
Не совсем понятен вопрос: на другом листе должна формироваться пустая таблица с указанным количеством строк или таблица, должна содержать информацию, подтянутую из отдельного источника?

Для первого варианта ИМХО проще всего создать типовую таблицу на 24 строки и при помощи условного форматирования закрашивать "ненужные" строки

Во втором случае (если данные где-то хранятся) проще использовать стандартный инструмент Excel - _автофильтр_ (https://msexcel.ru/content/view/27/2/) (меню Данные). В крайнем случае придется добавить столбец с формулой =ОКРУГЛ((СЕГОДНЯ()-дата_платежа)/30;0) для расчета количества месяцев между текущей датой и датой платежа для каждой из строк.

Если же Вам удобнее подтягивать информацию из источника в новую таблицу, то почитайте о формулах ВПР(_) (https://msexcel.ru/content/view/20/2/), ПОИСКПОЗ() и ИНДЕКС().
Название: Re: Списки в Excel
Отправлено: Анна Томина от 30.11.2012, 12:11
Цитата: IKor от 29.11.2012, 17:40
Не совсем понятен вопрос: на другом листе должна формироваться пустая таблица с указанным количеством строк или таблица, должна содержать информацию, подтянутую из отдельного источника?
посмотрите, в файле "шаблон" все работает как мне хочется сделать, а в "графике" то что делаю я. Так вот я никак не пойму какими действиями в "шаблоне " было сделано, что выборе в поле (Срок договора (мес)) например 23, то на следующих листах в Графиках платежей формируется таблица с количеством заполненных строк равное 23
Название: Re: Списки в Excel
Отправлено: IKor от 03.12.2012, 13:25
Цитата: Анна Томина от 30.11.2012, 12:11
посмотрите, в файле "шаблон" все работает как мне хочется сделать, а в "графике" то что делаю я. Так вот я никак не пойму какими действиями в "шаблоне " было сделано, что выборе в поле (Срок договора (мес)) например 23, то на следующих листах в Графиках платежей формируется таблица с количеством заполненных строк равное 23
Откройте файл "шаблон" и на листе график выделите строки ниже итоговой - вы увидите нули, прописанные белым шрифтом на синем фоне :)
Для того, чтобы понять как это было сделано вам потребуется выделить таблицу и посмотреть условное_форматирование (https://msexcel.ru/content/view/116/2/) / Управление правилами (на вкладке Главная / Стили).
Говоря кратко: автор таблицы в зависимости от указанного количества месяцев меняет не формулы в ячейках, но и цвет шрифта ячейки - при необходимости делая его равным цвету фона. Кроме того, тем же условным форматированием "скрываются" и границы таблицы.
Название: Re: Списки в Excel
Отправлено: Анна Томина от 04.12.2012, 14:19
про то что использовалось усл.форматирование я поняла, едиственное зациклилась на том как он прописывает условия, а оказалось все просто. Спасибо за помощь
Название: Re: Списки в Excel
Отправлено: Ярослав от 10.01.2013, 09:13
Добрый день!
Делаю зависимый выпадающий список на листе 'платежи столбец G. Подскажите, пожалуйста, как сделать, чтобы формула имени Подстатьи искала нужные данные в зависимости от выбранного значения в столбце Статья (F) по соответствующей строке столбца. Приведенная в шаблонах формула жестко привязана к конкретной ячейке. Думал если вместо абсолютной ссылки прописать столбец таблицы, то получиться как надо, а получилось, что значение берется только из первой строки  :( Вобщем выкладываю пример.
Название: Re: Списки в Excel
Отправлено: _Boroda_ от 10.01.2013, 11:34
Подстатьи перепишите так
=СМЕЩ(справочник_подстатьи!$A$1;ПОИСКПОЗ(платежи!$F2;справочник_подстатьи!$A:$A;0)-1;1;СЧЁТЕСЛИ(справочник_подстатьи!$A:$A;платежи!$F2); 1)
Название: Re: Списки в Excel
Отправлено: Ярослав от 10.01.2013, 12:20
блин как все просто  :) спасибо большое!
Название: Re: Списки в Excel
Отправлено: Compareto от 28.01.2013, 22:28
Связанные списки можно создавать в Palo. Топик про основы Palo (https://forum.msexcel.ru/index.php/topic,8345.msg46798.html#msg46798)
Название: Re: Списки в Excel
Отправлено: Змiцер от 29.01.2013, 16:11
Приветствую. Уж извините, если спрошу глупость, НО.... Можно ли, и главное как, в экселе создать список связанный с другим списком... Т.е. есть список "глав" и список "пунктов" к каждой главе, можно ли сделать так, чтобы при выборе из списка "глав" какую-нить главу в другой ячейке появился список "пунктов"....

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

Заранее спасибо за ответы и советы)
Название: Re: Списки в Excel
Отправлено: cheshiki1 от 29.01.2013, 17:03
Можно. Давайте пример таблицы и опишите что где.
Вот пример смотрите изучайте.
Название: Re: Списки в Excel
Отправлено: Змiцер от 29.01.2013, 20:21
Всё, разобрался по вашему примеру) Спасибо вам огромное....
Название: Re: Списки в Excel
Отправлено: Змiцер от 30.01.2013, 17:40
Вновь упёрся в проблему связанную со списками.... Подскажите, а возможна ли такая вот картина..... Кпримеру если в одной ячейке будет "1" то в другой ячейке появлялся "список_1", если "2", то "список_2" и т.д.Это вобще возможно?
Название: Re: Списки в Excel
Отправлено: TimSha от 01.02.2013, 10:02
Цитата: Змiцер от 30.01.2013, 17:40
Вновь упёрся...
Не надо упираться!..
Вам предложили готовое - https://forum.msexcel.ru/index.php?action=dlattach;topic=6.0;attach=12164 - почему не воспользуетесь?!. ;)
Название: Re: Списки в Excel
Отправлено: cheshiki1 от 01.02.2013, 16:37
приложите пример с вашими попытками - будем посмотреть.
Название: Re: Списки в Excel
Отправлено: Змiцер от 04.02.2013, 09:22
Спасибо, разобрался... Применил функцию ДВУССЛ.... просто прошлый раз писал в совсем уж усталом состоянии))
Название: Re: Списки в Excel
Отправлено: Артем Ким от 16.03.2014, 10:21
Здравствуйте, уважаемые, магистры!  :)  Помогите пожалуйста, создать "Дополняющийся" выпадающий список из списка находящегося на другом листе + к тому же, при выборе нужного значения в списке, справа от него (т.е. списка)  в той же строке появлялась таблица, необходимая для заполнения параметров "выбранного из списка". Подробнее в примечаниях в файле. 3 дня мучаюсь, надеюсь на вашу помощь, ребята.
Название: Re: Списки в Excel
Отправлено: Антон Махов от 01.05.2014, 08:31
Здравствуйте, задача такова. нужно сделать зависимость цен при изменении значений выпадающего списка. для значения СПО24 при значениях простой = 3100, энерг. = 4400, при значении СПД32 значение простой = 4500, энерг = 5800 и т.д.
Название: Re: Списки в Excel
Отправлено: ShAM от 03.05.2014, 09:41
Если правильно понял:
Название: Списки в Excel
Отправлено: Danata от 14.05.2014, 17:29
Добрый день! Я совсем не асс и довольно посредственный пользователь программы EXCEL, но очень бы хотела как-то оптимизировать работу со многими документами.  В частности: часто имею дело с договорами, возможно ли в данной программе сделать так, чтобы при вводе фамилии, имени и отчества из списка (Лист-ОСНОВНОЙ СПИСОК) в строке "и гр-н(ка)" на листе ДОГОВОР, внизу в блоке "Исполнитель" данные этого гр-на(ки) вводились автоматически. Или, возможен вариант, когда каждому из списка будет присвоен свой код и при вводе данного кода, будет автоматически вводится данные относящиеся к этому конкретному человеку? Вообще, что можно придумать, чтобы договор формировался автоматом? Возможно ли это? Спасибо большое. Возможно задаю глупые вопросы, но я только учусь, так что простите..
Название: Re: Списки в Excel
Отправлено: cheshiki1 от 18.05.2014, 10:52
вот, только к спискам это ни какого отношения не имеет. на счет формулы для адреса у меня сомнения так что разберитесь и дотачивайте сами.
Название: Re: Списки в Excel
Отправлено: Danata от 18.05.2014, 20:32
Спасибо за ответ. Попробую разобраться..

19.05.2014  Спасибо огромное! Всё получилось и с адресом тоже всё как надо. А на счёт списков.....я предупреждала... самоучка....Вот так и учусь - с миру по нитке. Вам ещё раз огромное спасибо!