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

Пожалуйста, войдите или зарегистрируйтесь.


Расширенный поиск  

Новости:

К первому сообщению темы должен быть прикреплен файл примера в формате xls*.
Приложив пример, Вы избавите себя и других от вопросов типа "А какой критерий?", "А куда выводить результат?", "А сколько строк?" и все тех же просьб выложить файл. Рисовать за Вас Ваши же таблички с заданиями, а затем и решение к ним, никто желанием не горит. Да и, как показывает практика, в большинстве случаев без файла решения не найти.

Автор Тема: Списки в Excel  (Прочитано 160035 раз)

0 Пользователей и 1 Гость просматривают эту тему.

oleg

  • Пользователь
  • **
  • Уважение: +0/-0
  • Оффлайн Оффлайн
  • Сообщений: 21
Re: Списки в Excel
« Ответ #25 : 30.07.2008, 13:19:20 »

Так, разобрался с одним, столкнулся с новой проблемой. Надо сформировать новый список, в зависимости того, что выбрано в списке №1 :-(,   "ЕСЛИ" тоже не подходит...
Записан

Шпец Докапыч

  • Ветеран
  • *****
  • Уважение: +189/-4
  • Оффлайн Оффлайн
  • Сообщений: 1 919
  • ОБЛАДАТЕЛЬ УНИКАЛЬНЫХ НАВЫКОВ!!! :)
Re: Списки в Excel
« Ответ #26 : 30.07.2008, 14:52:14 »

Cмотрел эту тему,  в ней полностью разобрался, но это не то что нужно.
Надо сформировать новый список, в зависимости того, что выбрано в списке №1 :-(

Может, то, что не подходит lusui, подойдёт Вам, oleg?
Записан
Знания недостаточно, необходимо применение. Желания недостаточно, необходимо действие. (с) Брюс Ли

Soldier

  • Пользователь
  • **
  • Уважение: +1/-0
  • Оффлайн Оффлайн
  • Сообщений: 63
  • Что не убивает, делает сильнее
Re: Списки в Excel
« Ответ #27 : 11.08.2008, 14:57:55 »

Доброго времени всем!

1. Как сделать раскрывающийся список с данными из нескольких листов?
2. Как увеличить количество строк в листе, а то на 65536 непомещается   (
Записан

MickMick

  • Пользователь
  • **
  • Уважение: +0/-0
  • Оффлайн Оффлайн
  • Сообщений: 21
Re: Списки в Excel
« Ответ #28 : 16.09.2008, 19:28:03 »

Добрый вечер. Падскажите, пожалуйста, как можно решить следующую задачу:
1. Есть два листа. На первом создается таблица (1), на втором находится таблица (2) с данными, которая может дополняться (смотреть вложение). Нужно, чтобы при выборе ячейки "Предмет" в (1) открывалась таблица (2) (как при создании гиперссылки), и при выборе в ней Предмета - в первую таблицу переносились бы все данные, которые связаны с этим предметом.
2. Ширина ячейки "Предмет" в создаваемой таблице должна быть фиксированной, а если данные не помещатся - высота ячейки (и всей строки) автоматически увеличивается.
3. Нужно, чтобы при вводе данных в стоку таблицы (1) автоматически добавлялась следующая чистая строка, и при заполнении ячейки "Предмет" у этой строки появлялся номе по порядку.
4. Под таблицей в правой ячейке должны суммироваться учебные часы, выбранных предметов.
Извините за громоздкое объяснение, но очень нужна ваша помощь. Зараннее спасибо.
Записан

Шпец Докапыч

  • Ветеран
  • *****
  • Уважение: +189/-4
  • Оффлайн Оффлайн
  • Сообщений: 1 919
  • ОБЛАДАТЕЛЬ УНИКАЛЬНЫХ НАВЫКОВ!!! :)
Re: Списки в Excel
« Ответ #29 : 16.09.2008, 20:34:56 »

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

Добрый вечер. Инструкция:
  • 1. Составляем перечень предметов: см. Данные!I7:I24
  • 2. Задаём ему (перечню) имя "Предметы": см. формулу имени
  • 3. Таблицу (1) задаём списком через Ctrl+L, добавляем строку итогов
  • 4. Делаем выпадающий список для Предметов, в остальные столбцы вводим формулу со ссылкой на лист "Данные"
  • 5. При выборе след. предмета (B11) формулы автоматически протянутся (ибо список)
  • 6. На событие "Изменение" вешаем автоподбор ширины: см. код в модуле листа (ПКМ по ярлычку -> Исходный текст)

Надеюсь, что объяснил доступно ;)
Записан
Знания недостаточно, необходимо применение. Желания недостаточно, необходимо действие. (с) Брюс Ли

MickMick

  • Пользователь
  • **
  • Уважение: +0/-0
  • Оффлайн Оффлайн
  • Сообщений: 21
Re: Списки в Excel
« Ответ #30 : 08.10.2008, 15:47:55 »

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

vadkol

  • Новичок
  • *
  • Уважение: +0/-0
  • Оффлайн Оффлайн
  • Сообщений: 1
Автоввод в выпадающем списке
« Ответ #31 : 09.10.2008, 19:32:53 »

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

K.E.N.

  • Новичок
  • *
  • Уважение: +0/-0
  • Оффлайн Оффлайн
  • Сообщений: 2
Re: Списки в Excel
« Ответ #32 : 24.12.2008, 09:54:47 »

как сделать так чтобы при выборе из списка Ф.И.О. выдергивались данные из Листа3 соттветствующие данной фамилии ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ???
Записан

IKor

  • Старожил
  • ****
  • Уважение: +49/-0
  • Оффлайн Оффлайн
  • Сообщений: 902
Re: Списки в Excel
« Ответ #33 : 24.12.2008, 13:16:46 »

Проще всего это сделать при помощи _автофильтр_а прямо на листе 3.
Но если потребуется, то можно сделать так как показано в прилагаемом файле - описание формул в самос файле.
Важное ограничение: данные в таблице на листе 3 должны быть отсортированы по сотрудникам - иначе придется сильно усложнять формулы листов.
Кроме того, все то же самое можно сделать при помощи макросов, но я не возьмусь ;)
P.S. в качестве бонуса я убрал из списка сотрудников повторяющиеся фамилии.
Записан

K.E.N.

  • Новичок
  • *
  • Уважение: +0/-0
  • Оффлайн Оффлайн
  • Сообщений: 2
Re: Списки в Excel
« Ответ #34 : 25.12.2008, 11:37:10 »

спасибки юольшущие!  :D
с наступающим всех :)  :D

осталось только все разобрать по полочкам :). но с этим я справлюсь сам  ;)
Записан

glokkk

  • Новичок
  • *
  • Уважение: +0/-0
  • Оффлайн Оффлайн
  • Сообщений: 6
Re: Списки в Excel
« Ответ #35 : 08.01.2009, 07:25:46 »

И еще вопрос: как был сделан раскрывающийся список (я сделал через проверку, но там нельзя ссылаться на другие листы)?

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

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

Заранее спасибо
Записан

Шпец Докапыч

  • Ветеран
  • *****
  • Уважение: +189/-4
  • Оффлайн Оффлайн
  • Сообщений: 1 919
  • ОБЛАДАТЕЛЬ УНИКАЛЬНЫХ НАВЫКОВ!!! :)
Re: Списки в Excel
« Ответ #36 : 08.01.2009, 10:51:14 »

А если усложнить задачу (см. аттачмент)?

Во-первых, это можно сделать _автофильтр_ом по условию "содержит".
А, во-вторых, вам нужно одно решение или СРАЗУ два? Если два, то составляйте аналогичную формулу с формулой во вложении (там пример выборки).
Записан
Знания недостаточно, необходимо применение. Желания недостаточно, необходимо действие. (с) Брюс Ли

IKor

  • Старожил
  • ****
  • Уважение: +49/-0
  • Оффлайн Оффлайн
  • Сообщений: 902
Re: Списки в Excel
« Ответ #37 : 08.01.2009, 11:06:08 »

;) "Штандартенфюрер тоже, но Хунта успел раньше..." (c) А. и Б. Стругацкие
Еще один вариант решения. Комменатрии внутри файла.
Записан

glokkk

  • Новичок
  • *
  • Уважение: +0/-0
  • Оффлайн Оффлайн
  • Сообщений: 6
Re: Списки в Excel
« Ответ #38 : 08.01.2009, 12:50:32 »

Шпец Докапыч, IKor спасибо. Буду смотреть

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

« Последнее редактирование: 08.01.2009, 14:48:04 от glokkk »
Записан

Шпец Докапыч

  • Ветеран
  • *****
  • Уважение: +189/-4
  • Оффлайн Оффлайн
  • Сообщений: 1 919
  • ОБЛАДАТЕЛЬ УНИКАЛЬНЫХ НАВЫКОВ!!! :)
Re: Списки в Excel
« Ответ #39 : 08.01.2009, 15:40:45 »

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

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

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

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

Записан
Знания недостаточно, необходимо применение. Желания недостаточно, необходимо действие. (с) Брюс Ли

glokkk

  • Новичок
  • *
  • Уважение: +0/-0
  • Оффлайн Оффлайн
  • Сообщений: 6
Re: Списки в Excel
« Ответ #40 : 09.01.2009, 12:35:37 »

Благодарю. Разобрался.  ???
« Последнее редактирование: 12.01.2009, 09:17:59 от glokkk »
Записан

IKor

  • Старожил
  • ****
  • Уважение: +49/-0
  • Оффлайн Оффлайн
  • Сообщений: 902
Re: Списки в Excel
« Ответ #41 : 09.01.2009, 14:57:51 »

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

Похоже, что история повторяется. Waalan тоже не учёл именованный диапазон. Ссылка на неё ("B1") в имени "VPR".
И еще вопрос: как был сделан раскрывающийся список (я сделал через проверку, но там нельзя ссылаться на другие листы)?

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

меню ДАННЫЕ / ПРОВЕРКА => вкладка ПАРАМЕТРЫ => тип_данных=Список
Записан

IKor

  • Старожил
  • ****
  • Уважение: +49/-0
  • Оффлайн Оффлайн
  • Сообщений: 902
Re: Списки в Excel
« Ответ #42 : 15.01.2009, 22:31:56 »

Вот в этой теме обсуждалась возможность генерации
списка уникальных значений без вспомогательных столбцов.
К сожалению, полностью избежать их использования не удалось,
но даже в таком виде решение кажется мне интересным.
Динамически обновляемый раскрывающийся список для ввода данных
Суть вопроса:
Довольно часто бывает удобно выбирать значение для ввода из наперед заданных (ранее введенных) значений. Для этой цели в Excel есть несколько возможностей, например, проверка вводимых значений (меню ДАННЫЕ / ПРОВЕРКА / список). К сожалению, список необходимо сформировать заранее и для ввода нового уникального значения его предварительно требуется добавить в этот список. Предлагаемое решение позволяет вводить данные как из выпадающего списка ранее введенных значений, так и указывать новые (последние автоматически добавятся к списку уникальных).
Недостатком этого решения является то, что для его работы требуется-таки сформировать список уникальных значений в отдельном месте документа. Однако наполнение этого списка производится автоматически - от человека требуется только следить за тем, чтобы количество строк в нем превышало количество уникальных значений.

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

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

Но мы ведь не ищем легких путей  ::)  ;D
Записан

Любовь

  • Новичок
  • *
  • Уважение: +0/-0
  • Оффлайн Оффлайн
  • Сообщений: 1
Списки в Excel
« Ответ #43 : 17.01.2009, 20:49:35 »

Как сделать выпадающий список, чтобы значения списка были "залиты" разными цветами? Т. е. при нажатии на стрелку, нвыпадает список, одно значение которого написано на синем фоне, другое - на красном, третье - на зелёном, и т. д.
Записан

SNAS

  • Новичок
  • *
  • Уважение: +0/-0
  • Оффлайн Оффлайн
  • Сообщений: 4
Re: Списки в Excel
« Ответ #44 : 06.03.2009, 15:43:45 »

Добрый день!
Какое событие генерится при выборе из списка ("проверка данных")?
Надо написать макрос, чтобы при выборе из списка ("проверка данных") вместо значения подставить ссылку на ячейку.
Зарание спасибо!
Записан

Шпец Докапыч

  • Ветеран
  • *****
  • Уважение: +189/-4
  • Оффлайн Оффлайн
  • Сообщений: 1 919
  • ОБЛАДАТЕЛЬ УНИКАЛЬНЫХ НАВЫКОВ!!! :)
Re: Списки в Excel
« Ответ #45 : 06.03.2009, 16:19:43 »

Какое событие генерится при выборе из списка ("проверка данных")?

Worksheet_Change конечно же.
Записан
Знания недостаточно, необходимо применение. Желания недостаточно, необходимо действие. (с) Брюс Ли

SNAS

  • Новичок
  • *
  • Уважение: +0/-0
  • Оффлайн Оффлайн
  • Сообщений: 4
Re: Списки в Excel
« Ответ #46 : 11.03.2009, 13:17:59 »

Цитировать
Worksheet_Change конечно же.
Но, в случае маскирования Worksheet_Change придется как-то проверять установлено ли в измененной ячейке "проверка данных"?
Я думал, что есть отдельное событие именно по ячейке с "проверка данных".
И еще - не нашел, где можно взять по ячейке с "проверка данных" ссылку на установленный список.
Записан

Шпец Докапыч

  • Ветеран
  • *****
  • Уважение: +189/-4
  • Оффлайн Оффлайн
  • Сообщений: 1 919
  • ОБЛАДАТЕЛЬ УНИКАЛЬНЫХ НАВЫКОВ!!! :)
Re: Списки в Excel
« Ответ #47 : 11.03.2009, 14:20:58 »

Если у Вас эта ячейка плавающая по листу, то проверить проверку (сорри за тавтологию) можно так:
  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.
Записан
Знания недостаточно, необходимо применение. Желания недостаточно, необходимо действие. (с) Брюс Ли

SNAS

  • Новичок
  • *
  • Уважение: +0/-0
  • Оффлайн Оффлайн
  • Сообщений: 4
Re: Списки в Excel
« Ответ #48 : 11.03.2009, 19:32:17 »

Спасибо!
Ячейка не плавающая, просто их много и в разных местах листа.
С ходу получился такой код. Работает, но может можно оптимальнее?
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 выделяет указанный диапазон, что не очень удобно (корректно). Иной способ поиска строки и взятие ее адреса, насколько я понимаю - перебор строк диапазона списка?
« Последнее редактирование: 13.03.2009, 17:09:16 от SNAS »
Записан

твой друг

  • Новичок
  • *
  • Уважение: +0/-0
  • Оффлайн Оффлайн
  • Сообщений: 2
Re: Списки в Excel
« Ответ #49 : 26.03.2009, 18:29:45 »

Здравствуйте! Проблема: есть два списка регионов России с рядами данных по годам, один с 90 до 99 года, другой после 2000 и до 2007. Регионы в списках расположены в совершенно разном порядке, в одном списке на 2 региона больше, чем в другом. Как с помощью excel сделать из этих двух списков один с рядами данных с 90 по 07 годы? очень уж не хочется делать это вручную...
Записан
 



Темы без ответов

22.05.2018 11:38 Скрипт написать который допишет данные в файл 569
03.03.2018 00:00 Подсчет отработанного времени, за исключением заранее определенных перерывов 848
14.02.2018 10:11 Подготовить читабельную отчетность по платежам 821
23.01.2018 13:46 Найти вероятность повторной покупки 778
12.01.2018 23:56 Сделать отчет на Power BI (Dashboard) 1057
06.09.2017 10:43 Solver VBA не решает гиперболическое уравнение, но при этом решает гармоническое 1032
17.08.2017 12:15 Гиперссылка и фильтр одновременно макрос 1347
23.05.2017 11:20 Копирование данных из одной таблицы в умную таблицу по условию 2952
15.03.2017 15:45 автозамена картинок PowerPoint 1782
11.03.2017 13:43 Изменить нумерацию страниц 2013





Яндекс цитирования msexcel.ru Яндекс.Метрика

Страница сгенерирована за 0.246 секунд. Запросов: 136.