Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Выборка значений и перенос данных на другой лист - Мир MS Excel

Старая форма входа
  • Страница 1 из 2
  • 1
  • 2
  • »
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Выборка значений и перенос данных на другой лист (Формулы/Formulas)
Выборка значений и перенос данных на другой лист
JaguarKo Дата: Среда, 04.03.2015, 05:44 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 43
Репутация: 0 ±
Замечаний: 20% ±

Excel 2013
Здравствуйте,

Необходимо сделать выборку по значению ячеек в столбце A с переносом данных на другие листы. В приложении файл как должно получиться.
Можно ли это сделать без макросов а просто формулами?
Я понимаю что вопрос не простой, но подскажите с чего хотя бы начать. Или подскажите функцию для формулы.
На самом деле это похоже на Сортировку и фильтр, но как сделать что бы значения переносились на другой лист.

Заранее благодарю за помощь.
К сообщению приложен файл: 123.xlsx (11.4 Kb)
 
Ответить
СообщениеЗдравствуйте,

Необходимо сделать выборку по значению ячеек в столбце A с переносом данных на другие листы. В приложении файл как должно получиться.
Можно ли это сделать без макросов а просто формулами?
Я понимаю что вопрос не простой, но подскажите с чего хотя бы начать. Или подскажите функцию для формулы.
На самом деле это похоже на Сортировку и фильтр, но как сделать что бы значения переносились на другой лист.

Заранее благодарю за помощь.
Автор - JaguarKo
Дата добавления - 04.03.2015 в 05:44
AleksSid Дата: Среда, 04.03.2015, 06:23 | Сообщение № 2
Группа: Пользователи
Ранг: Прохожий
Сообщений: 6
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
Примерно так.
Код
=СУММПРОИЗВ((Список!$A$2:$A$16=$A2)*(Список!$B$2:$B$16=A!$B2)*(Список!$C$1:$D$1=A!C$1)*Список!$C$2:$D$16)
Или так, формула массива.
Код
=ЕСЛИОШИБКА(ИНДЕКС(Список!$C$2:$D$16;ПОИСКПОЗ($A2&$B2;Список!$A$2:$A$16&Список!$B$2:$B$16;0);ПОИСКПОЗ(C$1;Список!$C$1:$D$1;0));"")
К сообщению приложен файл: 123_1.xlsx (13.3 Kb)
 
Ответить
СообщениеПримерно так.
Код
=СУММПРОИЗВ((Список!$A$2:$A$16=$A2)*(Список!$B$2:$B$16=A!$B2)*(Список!$C$1:$D$1=A!C$1)*Список!$C$2:$D$16)
Или так, формула массива.
Код
=ЕСЛИОШИБКА(ИНДЕКС(Список!$C$2:$D$16;ПОИСКПОЗ($A2&$B2;Список!$A$2:$A$16&Список!$B$2:$B$16;0);ПОИСКПОЗ(C$1;Список!$C$1:$D$1;0));"")
Автор - AleksSid
Дата добавления - 04.03.2015 в 06:23
VEKTORVSFREEMAN Дата: Среда, 04.03.2015, 06:48 | Сообщение № 3
Группа: Друзья
Ранг: Ветеран
Сообщений: 772
Репутация: 130 ±
Замечаний: 0% ±

MS Office Excel 2010
JaguarKo, здравствуйте!
Не макросами, но и не формулами, а сводной таблицей.
Может удовлетворит такой вариант решения.
К сообщению приложен файл: 7993704.xlsx (18.8 Kb)

"Опыт - это то, что получаешь, не получив того, что хотел"
 
Ответить
СообщениеJaguarKo, здравствуйте!
Не макросами, но и не формулами, а сводной таблицей.
Может удовлетворит такой вариант решения.
Автор - VEKTORVSFREEMAN
Дата добавления - 04.03.2015 в 06:48
JaguarKo Дата: Среда, 04.03.2015, 08:18 | Сообщение № 4
Группа: Пользователи
Ранг: Новичок
Сообщений: 43
Репутация: 0 ±
Замечаний: 20% ±

Excel 2013
AleksSid,
Здравствуйте,
Нужно что бы при заполнении листа Список данные по критериям названия ячеек в столбце A переносились и заполняли таблички на соответствующих листах.
 
Ответить
СообщениеAleksSid,
Здравствуйте,
Нужно что бы при заполнении листа Список данные по критериям названия ячеек в столбце A переносились и заполняли таблички на соответствующих листах.
Автор - JaguarKo
Дата добавления - 04.03.2015 в 08:18
JaguarKo Дата: Среда, 04.03.2015, 08:41 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 43
Репутация: 0 ±
Замечаний: 20% ±

Excel 2013
VEKTORVSFREEMAN,
Спасибо большое за подсказку о сводной таблице, попробую в ней разобраться.
 
Ответить
СообщениеVEKTORVSFREEMAN,
Спасибо большое за подсказку о сводной таблице, попробую в ней разобраться.
Автор - JaguarKo
Дата добавления - 04.03.2015 в 08:41
VEKTORVSFREEMAN Дата: Среда, 04.03.2015, 08:43 | Сообщение № 6
Группа: Друзья
Ранг: Ветеран
Сообщений: 772
Репутация: 130 ±
Замечаний: 0% ±

MS Office Excel 2010
JaguarKo, не за что!
Единственный нюанс вижу, что нужно будет после внесения данных в лист "Список" нажимать на кнопку "Обновить данные" на вкладке "Данные"
Удачи!

"Опыт - это то, что получаешь, не получив того, что хотел"
 
Ответить
СообщениеJaguarKo, не за что!
Единственный нюанс вижу, что нужно будет после внесения данных в лист "Список" нажимать на кнопку "Обновить данные" на вкладке "Данные"
Удачи!
Автор - VEKTORVSFREEMAN
Дата добавления - 04.03.2015 в 08:43
Pelena Дата: Среда, 04.03.2015, 09:17 | Сообщение № 7
Группа: Админы
Ранг: Местный житель
Сообщений: 19195
Репутация: 4423 ±
Замечаний: ±

Excel 365 & Mac Excel
Если строк не очень много (в примере сделано до 1000), то можно и формулами массива
Код
=ЕСЛИОШИБКА(ИНДЕКС(Список!B$2:B$1000;НАИМЕНЬШИЙ(ЕСЛИ(Список!$A$2:$A$1000="C";СТРОКА($A$2:$A$1000)-1);СТРОКА(A1)));"")

Файл перевложила, исправила ошибку
К сообщению приложен файл: 123-15-.xlsx (14.6 Kb)

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816

Сообщение отредактировал Pelena - Среда, 04.03.2015, 12:12
 
Ответить
СообщениеЕсли строк не очень много (в примере сделано до 1000), то можно и формулами массива
Код
=ЕСЛИОШИБКА(ИНДЕКС(Список!B$2:B$1000;НАИМЕНЬШИЙ(ЕСЛИ(Список!$A$2:$A$1000="C";СТРОКА($A$2:$A$1000)-1);СТРОКА(A1)));"")

Файл перевложила, исправила ошибку
Автор - Pelena
Дата добавления - 04.03.2015 в 09:17
JaguarKo Дата: Среда, 04.03.2015, 09:50 | Сообщение № 8
Группа: Пользователи
Ранг: Новичок
Сообщений: 43
Репутация: 0 ±
Замечаний: 20% ±

Excel 2013
Pelena,
Здравствуйте,

Что то не работает. "C" это критерий для поиска?
 
Ответить
СообщениеPelena,
Здравствуйте,

Что то не работает. "C" это критерий для поиска?
Автор - JaguarKo
Дата добавления - 04.03.2015 в 09:50
Pelena Дата: Среда, 04.03.2015, 09:58 | Сообщение № 9
Группа: Админы
Ранг: Местный житель
Сообщений: 19195
Репутация: 4423 ±
Замечаний: ±

Excel 365 & Mac Excel
Эта формула для листа С, на других листах соответственно А и В (все латинские)
А что не работает?

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеЭта формула для листа С, на других листах соответственно А и В (все латинские)
А что не работает?
Автор - Pelena
Дата добавления - 04.03.2015 в 09:58
JaguarKo Дата: Среда, 04.03.2015, 10:44 | Сообщение № 10
Группа: Пользователи
Ранг: Новичок
Сообщений: 43
Репутация: 0 ±
Замечаний: 20% ±

Excel 2013
Pelena,
Извините что сразу не сказал. Сейчас немного разобрался, на листе А в формуле стоял "C".
Почему когда нажимаю на формулу пропадает { скобка и тогда формула не работает.
 
Ответить
СообщениеPelena,
Извините что сразу не сказал. Сейчас немного разобрался, на листе А в формуле стоял "C".
Почему когда нажимаю на формулу пропадает { скобка и тогда формула не работает.
Автор - JaguarKo
Дата добавления - 04.03.2015 в 10:44
Pelena Дата: Среда, 04.03.2015, 10:47 | Сообщение № 11
Группа: Админы
Ранг: Местный житель
Сообщений: 19195
Репутация: 4423 ±
Замечаний: ±

Excel 365 & Mac Excel
Это формула массива, вводится сочетанием клавиш Ctrl+Shift+Enter

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеЭто формула массива, вводится сочетанием клавиш Ctrl+Shift+Enter
Автор - Pelena
Дата добавления - 04.03.2015 в 10:47
JaguarKo Дата: Среда, 04.03.2015, 10:56 | Сообщение № 12
Группа: Пользователи
Ранг: Новичок
Сообщений: 43
Репутация: 0 ±
Замечаний: 20% ±

Excel 2013
Pelena,
Извиняюсь за незнание. Видимо тут можно только через формулу массива сделать. Вроде все работает отлично. Спасибо большое.
 
Ответить
СообщениеPelena,
Извиняюсь за незнание. Видимо тут можно только через формулу массива сделать. Вроде все работает отлично. Спасибо большое.
Автор - JaguarKo
Дата добавления - 04.03.2015 в 10:56
_Boroda_ Дата: Среда, 04.03.2015, 11:36 | Сообщение № 13
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16675
Репутация: 6481 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Можно еще сводными таблицами. Обновлять их все сразу можно Данные - Обновить все
К сообщению приложен файл: 123-15-1.xlsx (18.3 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеМожно еще сводными таблицами. Обновлять их все сразу можно Данные - Обновить все
Автор - _Boroda_
Дата добавления - 04.03.2015 в 11:36
VEKTORVSFREEMAN Дата: Среда, 04.03.2015, 11:40 | Сообщение № 14
Группа: Друзья
Ранг: Ветеран
Сообщений: 772
Репутация: 130 ±
Замечаний: 0% ±

MS Office Excel 2010
_Boroda_, здравствуй!
Так вроде то же и предлагал, только оформление немного иное (без общего фильтра) :)

"Опыт - это то, что получаешь, не получив того, что хотел"
 
Ответить
Сообщение_Boroda_, здравствуй!
Так вроде то же и предлагал, только оформление немного иное (без общего фильтра) :)
Автор - VEKTORVSFREEMAN
Дата добавления - 04.03.2015 в 11:40
_Boroda_ Дата: Среда, 04.03.2015, 11:52 | Сообщение № 15
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16675
Репутация: 6481 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Действительно. Не заметил.

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

Добавлено
В смысле - в формуле не нужно заводить имя листа. См. столбец А - там название листа, в котором формула, пишется автоматически.
Код
=ПСТР(ЯЧЕЙКА("имяфайла";A2);ПОИСК("]";ЯЧЕЙКА("имяфайла";A2))+1;99)
К сообщению приложен файл: 123-15-3.xlsx (15.3 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеДействительно. Не заметил.

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

Добавлено
В смысле - в формуле не нужно заводить имя листа. См. столбец А - там название листа, в котором формула, пишется автоматически.
Код
=ПСТР(ЯЧЕЙКА("имяфайла";A2);ПОИСК("]";ЯЧЕЙКА("имяфайла";A2))+1;99)
Автор - _Boroda_
Дата добавления - 04.03.2015 в 11:52
Pelena Дата: Среда, 04.03.2015, 11:56 | Сообщение № 16
Группа: Админы
Ранг: Местный житель
Сообщений: 19195
Репутация: 4423 ±
Замечаний: ±

Excel 365 & Mac Excel
[offtop]Перфекционист :D [/offtop]

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
Сообщение[offtop]Перфекционист :D [/offtop]
Автор - Pelena
Дата добавления - 04.03.2015 в 11:56
VEKTORVSFREEMAN Дата: Среда, 04.03.2015, 11:58 | Сообщение № 17
Группа: Друзья
Ранг: Ветеран
Сообщений: 772
Репутация: 130 ±
Замечаний: 0% ±

MS Office Excel 2010
[offtop]А я не понял в чем Перфект :( дополнения
прошу прощения, нашёл доработку в формуле (просто подумал, что лист сам будет появляться и обзываться так как надо "чтобы не нужно было вручную заводить имя листа")[/offtop]

"Опыт - это то, что получаешь, не получив того, что хотел"


Сообщение отредактировал VEKTORVSFREEMAN - Среда, 04.03.2015, 12:02
 
Ответить
Сообщение[offtop]А я не понял в чем Перфект :( дополнения
прошу прощения, нашёл доработку в формуле (просто подумал, что лист сам будет появляться и обзываться так как надо "чтобы не нужно было вручную заводить имя листа")[/offtop]
Автор - VEKTORVSFREEMAN
Дата добавления - 04.03.2015 в 11:58
_Boroda_ Дата: Среда, 04.03.2015, 12:09 | Сообщение № 18
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16675
Репутация: 6481 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Цитата Pelena, 04.03.2015 в 11:56, в сообщении № 16
Перфекционист

Да просто посмотрел твой файл из сообщения №7 - в листе А стоит формула для листа С и значения неверные.
Цитата VEKTORVSFREEMAN, 04.03.2015 в 11:58, в сообщении № 17
что лист сам будет появляться и обзываться так как надо "чтобы не нужно было вручную заводить имя листа"

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

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение
Цитата Pelena, 04.03.2015 в 11:56, в сообщении № 16
Перфекционист

Да просто посмотрел твой файл из сообщения №7 - в листе А стоит формула для листа С и значения неверные.
Цитата VEKTORVSFREEMAN, 04.03.2015 в 11:58, в сообщении № 17
что лист сам будет появляться и обзываться так как надо "чтобы не нужно было вручную заводить имя листа"

Нет, лист нужно будет обзывать вручную, а вот формулу менять не нужно будет. Нужно просто скопировать уже имеющийся лист и переназвать его. Я там в свой пред. пост дописал немного, чтобы попонятнее стало.
Автор - _Boroda_
Дата добавления - 04.03.2015 в 12:09
JaguarKo Дата: Вторник, 17.03.2015, 05:32 | Сообщение № 19
Группа: Пользователи
Ранг: Новичок
Сообщений: 43
Репутация: 0 ±
Замечаний: 20% ±

Excel 2013
_Boroda_,
Здравствуйте,
Все отлично, но есть единственная проблема, если ячейки в Списке пустые, то в при поиске и подставке значение получается 0, а хотелось бы что бы она тоже была пустой.
 
Ответить
Сообщение_Boroda_,
Здравствуйте,
Все отлично, но есть единственная проблема, если ячейки в Списке пустые, то в при поиске и подставке значение получается 0, а хотелось бы что бы она тоже была пустой.
Автор - JaguarKo
Дата добавления - 17.03.2015 в 05:32
ShAM Дата: Вторник, 17.03.2015, 06:12 | Сообщение № 20
Группа: Друзья
Ранг: Старожил
Сообщений: 1347
Репутация: 249 ±
Замечаний: 0% ±

Excel 2010
Предлагаю 2 способа. На листе А чуть изменил формулу. На листе В через Файл - Параметры - Дополнительно - убрать "птицу" с "Показывать нули в ячейках, которые содержат нулевые значения". ЗЫ: Это для эксель-2010, но, думаю, в 2013-м тоже где-то рядом.
К сообщению приложен файл: 123-15-3-1.xlsx (15.8 Kb)
 
Ответить
СообщениеПредлагаю 2 способа. На листе А чуть изменил формулу. На листе В через Файл - Параметры - Дополнительно - убрать "птицу" с "Показывать нули в ячейках, которые содержат нулевые значения". ЗЫ: Это для эксель-2010, но, думаю, в 2013-м тоже где-то рядом.
Автор - ShAM
Дата добавления - 17.03.2015 в 06:12
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Выборка значений и перенос данных на другой лист (Формулы/Formulas)
  • Страница 1 из 2
  • 1
  • 2
  • »
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!