Авткопирование содержимого ячейки на другой лист по условию - Мир MS Excel

Регистрация

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

= Мир MS Excel/Авткопирование содержимого ячейки на другой лист по условию - Мир MS Excel

Старая форма входа

Страница 1 из 1 1
Модератор форума: китин, _Boroda_

Авткопирование содержимого ячейки на другой лист по условию (Формулы/Formulas)

Авткопирование содержимого ячейки на другой лист по условию

Дата: Четверг, 11.02.2016, 11:05 | Сообщение № 1

Группа: Пользователи

Ранг: Новичок

Сообщений: 12

Репутация: 0 ±

Замечаний: 0% ±

Excel 2003

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

К сообщению приложен файл: LABOR.xls (18.0 Kb)

Ответить

Дата: Четверг, 11.02.2016, 11:12 | Сообщение № 2

Группа: Заблокированные

Ранг: Участник клуба

Сообщений: 3442

Репутация: 929 ±

Замечаний: 20% ±

2010, 2013, 2016 RUS / ENG

например, так:

Код

=ЕСЛИОШИБКА(ИНДЕКС(Регистрация!$C$3:$C$10;НАИМЕНЬШИЙ(ЕСЛИОШИБКА((СТРОКА(Регистрация!$D$3:$D$10)-2)/(Регистрация!$D$3:$D$10="да");"");СТРОКА(A1)));"")

UPD на версию Excel не посмотрел - тогда так:

Код

=ЕСЛИ(ЕОШ(НАИМЕНЬШИЙ(ЕСЛИ(Регистрация!$D$3:$D$50="да";СТРОКА(Регистрация!$C$3:$C$50)-2;"");СТРОКА(B1)));"";ИНДЕКС(Регистрация!$C$3:$C$50;НАИМЕНЬШИЙ(ЕСЛИ(Регистрация!$D$3:$D$50="да";СТРОКА(Регистрация!$C$3:$C$50)-2;"");СТРОКА(B1))))

К сообщению приложен файл: 1144995.xls (34.0 Kb)

Сообщение отредактировал buchlotnik - Четверг, 11.02.2016, 11:17

Ответить

Дата: Четверг, 11.02.2016, 11:14 | Сообщение № 3

Группа: Модераторы

Ранг: Экселист

Сообщений: 7029

Репутация: 1078 ±

Замечаний: 0% ±

Excel 2007;2010;2016

так надо? формула массива для 2003

Код

=ЕСЛИ(ЕОШ(ИНДЕКС(Регистрация!$C$3:$C$50;НАИМЕНЬШИЙ(ЕСЛИ(Регистрация!$D$3:$D$50="Да";СТРОКА(Регистрация!$C$3:$C$50)-2);СТРОКА(A1))));"";ИНДЕКС(Регистрация!$C$3:$C$50;НАИМЕНЬШИЙ(ЕСЛИ(Регистрация!$D$3:$D$50="Да";СТРОКА(Регистрация!$C$3:$C$50)-2);СТРОКА(A1))))

К сообщению приложен файл: LABOR_2.xls (37.5 Kb)

Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852

Ответить

Дата: Четверг, 11.02.2016, 11:16 | Сообщение № 4

Группа: Модераторы

Ранг: Старожил

Сообщений: 2290

Репутация: 766 ±

Замечаний: 0% ±

2019

Какие все шустрые

НУ и мой вариант. Формула массива:

Код

=ЕСЛИОШИБКА(ИНДЕКС(Регистрация!$C$1:$C$41;НАИМЕНЬШИЙ(ЕСЛИ(Регистрация!$D$3:$D$41="Да";СТРОКА(Регистрация!$D$3:$D$41);9^9);СТРОКА(E1));1);"")

К сообщению приложен файл: 4686306.xls (34.0 Kb)

Иногда все проще чем кажется с первого взгляда.

Ответить

Дата: Четверг, 11.02.2016, 11:20 | Сообщение № 5

Группа: Пользователи

Ранг: Новичок

Сообщений: 12

Репутация: 0 ±

Замечаний: 0% ±

Excel 2003

Как оперативно ребята!!! Спасибо) сейчас буду смотреть,изучать, втыкать в формулы

Ответить

Дата: Четверг, 11.02.2016, 11:27 | Сообщение № 6

Группа: Модераторы

Ранг: Экселист

Сообщений: 7029

Репутация: 1078 ±

Замечаний: 0% ±

Excel 2007;2010;2016

Цитата SLAVICK, 11.02.2016 в 11:16, в сообщении № 4

Какие все шустрые

гы да исчо и глазастые, на версию Excel смотрим.

Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852

Ответить

Дата: Четверг, 11.02.2016, 11:28 | Сообщение № 7

Группа: Заблокированные

Ранг: Участник клуба

Сообщений: 3442

Репутация: 929 ±

Замечаний: 20% ±

2010, 2013, 2016 RUS / ENG

[offtop]

Цитата

на версию Excel смотрим

смотрим-смотрим

Ответить

Дата: Четверг, 11.02.2016, 11:37 | Сообщение № 8

Группа: Модераторы

Ранг: Старожил

Сообщений: 2290

Репутация: 766 ±

Замечаний: 0% ±

2019

Цитата китин, 11.02.2016 в 11:27, в сообщении № 6

гы да исчо и глазастые,

Цитата buchlotnik, 11.02.2016 в 11:28, в сообщении № 7

смотрим-смотрим

Ну да

- это я еще не проснулся shock

shock

.
Ну тогда так:

Код

=ЕСЛИ(ЕОШ(
ИНДЕКС(Регистрация!$C$1:$C$41;НАИМЕНЬШИЙ(ЕСЛИ(Регистрация!$D$3:$D$41="Да";СТРОКА(Регистрация!$D$3:$D$41);9^9);СТРОКА(E1))));"";
ИНДЕКС(Регистрация!$C$1:$C$41;НАИМЕНЬШИЙ(ЕСЛИ(Регистрация!$D$3:$D$41="Да";СТРОКА(Регистрация!$D$3:$D$41);9^9);СТРОКА(E1))))

Иногда все проще чем кажется с первого взгляда.

Ответить

Дата: Четверг, 11.02.2016, 12:27 | Сообщение № 9

Группа: Пользователи

Ранг: Новичок

Сообщений: 12

Репутация: 0 ±

Замечаний: 0% ±

Excel 2003

Ребят, а если мне не 40 значение надо будет, а 540? где что поправить надо будет? сделал так(на 60 значений)

Код

=ЕСЛИ(ЕОШ(ИНДЕКС(Регистрация!$C$3:$C$60;НАИМЕНЬШИЙ(ЕСЛИ(Регистрация!$D$3:$D$60="Да";СТРОКА(Регистрация!$C$3:$C$60)-2);СТРОКА(A1))));"";ИНДЕКС(Регистрация!$C$3:$C$60;НАИМЕНЬШИЙ(ЕСЛИ(Регистрация!$D$3:$D$60="Да";СТРОКА(Регистрация!$C$3:$C$60)-2);СТРОКА(A1))))

и протянул ее. в итоге только первая строка заполнилась, а остальные пустые. Что я сделал не так7
[moder]Используйте для формул кнопочку fx. Поправила.[/moder]

Сообщение отредактировал Manyasha - Четверг, 11.02.2016, 12:40

Ответить

Дата: Четверг, 11.02.2016, 12:55 | Сообщение № 10

Группа: Друзья

Ранг: Старожил

Сообщений: 1232

Репутация: 326 ±

Замечаний: 0% ±

2003

Цитата AR84, 11.02.2016 в 11:05, в сообщении № 1

Если нет, то через макросы.

В модуль листа.
[vba]

Код

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Application.Intersect(Range("D1:D2000"), Target) Is Nothing Then
    Dim s&
    If Target = "Да" Then
       s = Sheets("1").Range("C" & Rows.Count).End(xlUp).Row + 1
       Target.Offset(0, -1).Copy Sheets("1").Cells(s, 3)
       Target.Offset(0, -2).Copy Sheets("1").Cells(s, 2)
    End If
  End If
End Sub

[/vba]

Ответить

Дата: Четверг, 11.02.2016, 14:22 | Сообщение № 11

Группа: Модераторы

Ранг: Старожил

Сообщений: 2290

Репутация: 766 ±

Замечаний: 0% ±

2019

Цитата AR84, 11.02.2016 в 12:27, в сообщении № 9

итоге только первая строка заполнилась, а остальные пустые. Что я сделал не так7

Формула массива вводится сочетанием клавиш:
CTRL SHIFT Enter

Иногда все проще чем кажется с первого взгляда.

Ответить

Дата: Четверг, 11.02.2016, 20:24 | Сообщение № 12

Группа: Админы

Ранг: Местный житель

Сообщений: 16709

Репутация: 6501 ±

Замечаний: ±

2003; 2007; 2010; 2013 RUS

Цитата AR84, 11.02.2016 в 12:27, в сообщении № 9

а если мне не 40 значение надо будет, а 540?

Вот тогда уж точно не стОит 2 раза считать тяжелый НАИМЕНЬШИЙ.
Предлагаю так

Код

=ЕСЛИ(A3>СЧЁТЕСЛИ(Регистрация!D$3:D$50;"Да");"";ИНДЕКС(Регистрация!C$3:C$50;НАИМЕНЬШИЙ(ЕСЛИ(Регистрация!D$3:D$50="Да";Регистрация!A$3:A$50);СТРОКА(A1))))

Тоже формула массива, вводится одновременным нажатием Контрл Шифт Ентер

К сообщению приложен файл: LABOR_3.xls (37.5 Kb)

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

Ответить

Дата: Пятница, 12.02.2016, 09:33 | Сообщение № 13

Группа: Пользователи

Ранг: Новичок

Сообщений: 12

Репутация: 0 ±

Замечаний: 0% ±

Excel 2003

Ребят, а если мне надо скопировать с первого листа не только фио, но и дату на второй лист в соответствующую ячейку, формула как измениться? Что то я в нее пытаюсь вникнуть и никак. Как поправить диапазон - я разобрался. Спасибо вам большое.

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

Сообщение отредактировал AR84 - Пятница, 12.02.2016, 09:37

Ответить

Дата: Пятница, 12.02.2016, 09:39 | Сообщение № 14

Группа: Друзья

Ранг: Старожил

Сообщений: 1232

Репутация: 326 ±

Замечаний: 0% ±

2003

А макрос чё, не подходит, или не смотрел?

PS: Понял, Выбор Да-Нет не разовый. Значит надо каждый раз переписывать данные.
Ну в общим исправил. Мож пригодится.

[vba]

Код

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("D1:D2000"), Target) Is Nothing Then
   Sheets("1").Range("B3:D45").ClearContents
   Dim s&, i&
   s = 3
   For i = 3 To Range("D" & Rows.Count).End(xlUp).Row
     If Cells(i, 4) = "Да" Then
       Sheets("1").Cells(s, 2) = Cells(i, 2)
       Sheets("1").Cells(s, 3) = Cells(i, 3)
       s = s + 1
     End If
   Next
End If
End Sub

[/vba]

К сообщению приложен файл: LABOR_VBA.xls (38.5 Kb)

Сообщение отредактировал Wasilic - Пятница, 12.02.2016, 10:21

Ответить

Дата: Пятница, 12.02.2016, 09:43 | Сообщение № 15

Группа: Модераторы

Ранг: Экселист

Сообщений: 7029

Репутация: 1078 ±

Замечаний: 0% ±

Excel 2007;2010;2016

ну это уже проще обычная

Код

=ЕСЛИ(ЕНД(ИНДЕКС(Регистрация!$B$3:$B$50;ПОИСКПОЗ('1'!$C3;Регистрация!$C$3:$C$50;0)));"";ИНДЕКС(Регистрация!$B$3:$B$50;ПОИСКПОЗ('1'!$C3;Регистрация!$C$3:$C$50;0)))

К сообщению приложен файл: _LABOR_3.xls (42.5 Kb)

Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852

Ответить

Дата: Пятница, 12.02.2016, 10:29 | Сообщение № 16

Группа: Админы

Ранг: Местный житель

Сообщений: 16709

Репутация: 6501 ±

Замечаний: ±

2003; 2007; 2010; 2013 RUS

В куске ИНДЕКС(Регистрация!C$3:C$50 измените буквы столбца ("С") на нужные.

Цитата AR84, 12.02.2016 в 09:33, в сообщении № 13 ()

Что то я в нее пытаюсь вникнуть и никак

Поясню немного.
1. ЕСЛИ(Регистрация!D$3:D$50="Да";Регистрация!A$3:A$50) - когда в Д написано "Да", то берем значения из А, иначе - значение ЛОЖЬ. Получаем массив из номеров из столбца А и текста ЛОЖЬ (слово ЛОЖЬ - в данном случае рассматривается именно как текст - с ним не производилось никаких арифметических операций).
2. СТРОКА(A1) - дает номер строки ячейки А1 - то есть единицу. При протягивании формулы вниз ссылка изменится и в следующей ячейке будет уже А2 и, следовательно, СТРОКА(А2) даст нам 2. И так далее вниз 3, 4, 5, ...
3. НАИМЕНЬШИЙ(ЕСЛИ(Регистрация!D$3:D$50="Да";Регистрация!A$3:A$50);СТРОКА(A1)) переписываем как НАИМЕНЬШИЙ(п.1;п.2), получаем для первой записи первый наименьший из массива, полученного в п.1, для второй записи - второй наименьший, ... Функция НАИМЕНЬШИЙ игнорирует текст, поэтому мы получаем только цифры. Например, если массив из п.1 у нас получился 1:ЛОЖЬ:ЛОЖЬ:4:5, то первый наименьший = 1, второй = 4, третий = 5. То есть для каждой строки в итоговой таблице мы получаем номер по порядку той соответствующей строки в исходной таблице, где стоит "Да".
4. ИНДЕКС(Регистрация!C$3:C$50;п.3) - из массива столбца С выковыриваем то, что получили в п.3.
6. A3>СЧЁТЕСЛИ(Регистрация!D$3:D$50;"Да");"";... - если "Да" в исходнике закончились, то выводим пусто

В куске ИНДЕКС(Регистрация!C$3:C$50 измените буквы столбца ("С") на нужные.

Цитата AR84, 12.02.2016 в 09:33, в сообщении № 13 ()

Что то я в нее пытаюсь вникнуть и никак

Поясню немного.
1. ЕСЛИ(Регистрация!D$3:D$50="Да";Регистрация!A$3:A$50) - когда в Д написано "Да", то берем значения из А, иначе - значение ЛОЖЬ. Получаем массив из номеров из столбца А и текста ЛОЖЬ (слово ЛОЖЬ - в данном случае рассматривается именно как текст - с ним не производилось никаких арифметических операций).
2. СТРОКА(A1) - дает номер строки ячейки А1 - то есть единицу. При протягивании формулы вниз ссылка изменится и в следующей ячейке будет уже А2 и, следовательно, СТРОКА(А2) даст нам 2. И так далее вниз 3, 4, 5, ...
3. НАИМЕНЬШИЙ(ЕСЛИ(Регистрация!D$3:D$50="Да";Регистрация!A$3:A$50);СТРОКА(A1)) переписываем как НАИМЕНЬШИЙ(п.1;п.2), получаем для первой записи первый наименьший из массива, полученного в п.1, для второй записи - второй наименьший, ... Функция НАИМЕНЬШИЙ игнорирует текст, поэтому мы получаем только цифры. Например, если массив из п.1 у нас получился 1:ЛОЖЬ:ЛОЖЬ:4:5, то первый наименьший = 1, второй = 4, третий = 5. То есть для каждой строки в итоговой таблице мы получаем номер по порядку той соответствующей строки в исходной таблице, где стоит "Да".
4. ИНДЕКС(Регистрация!C$3:C$50;п.3) - из массива столбца С выковыриваем то, что получили в п.3.
6. A3>СЧЁТЕСЛИ(Регистрация!D$3:D$50;"Да");"";... - если "Да" в исходнике закончились, то выводим пусто_Boroda_

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

Ответить

СообщениеВ куске ИНДЕКС(Регистрация!C$3:C$50 измените буквы столбца ("С") на нужные.

Цитата AR84, 12.02.2016 в 09:33, в сообщении № 13 ()

Что то я в нее пытаюсь вникнуть и никак

Поясню немного.
1. ЕСЛИ(Регистрация!D$3:D$50="Да";Регистрация!A$3:A$50) - когда в Д написано "Да", то берем значения из А, иначе - значение ЛОЖЬ. Получаем массив из номеров из столбца А и текста ЛОЖЬ (слово ЛОЖЬ - в данном случае рассматривается именно как текст - с ним не производилось никаких арифметических операций).
2. СТРОКА(A1) - дает номер строки ячейки А1 - то есть единицу. При протягивании формулы вниз ссылка изменится и в следующей ячейке будет уже А2 и, следовательно, СТРОКА(А2) даст нам 2. И так далее вниз 3, 4, 5, ...
3. НАИМЕНЬШИЙ(ЕСЛИ(Регистрация!D$3:D$50="Да";Регистрация!A$3:A$50);СТРОКА(A1)) переписываем как НАИМЕНЬШИЙ(п.1;п.2), получаем для первой записи первый наименьший из массива, полученного в п.1, для второй записи - второй наименьший, ... Функция НАИМЕНЬШИЙ игнорирует текст, поэтому мы получаем только цифры. Например, если массив из п.1 у нас получился 1:ЛОЖЬ:ЛОЖЬ:4:5, то первый наименьший = 1, второй = 4, третий = 5. То есть для каждой строки в итоговой таблице мы получаем номер по порядку той соответствующей строки в исходной таблице, где стоит "Да".
4. ИНДЕКС(Регистрация!C$3:C$50;п.3) - из массива столбца С выковыриваем то, что получили в п.3.
6. A3>СЧЁТЕСЛИ(Регистрация!D$3:D$50;"Да");"";... - если "Да" в исходнике закончились, то выводим пусто

Автор - _Boroda_
Дата добавления - 12.02.2016 в 10:29

Дата: Пятница, 12.02.2016, 11:14 | Сообщение № 17

Группа: Пользователи

Ранг: Новичок

Сообщений: 12

Репутация: 0 ±

Замечаний: 0% ±

Excel 2003

Эх.....вижу что макросом проще написано, но я в них совсем ноль....пытаюсь изучить что делает формула и как ее потом можно усовершенствовать и применить (файлик будет расти, дополняться поля и другие формулы)...Спасибо Boroda за подробное объяснение формулы, хотелось бы такую же информацию от Wasilic по поводу его макроса =)

ПС. осилю формулы, перейду к макросам....

Сообщение отредактировал AR84 - Пятница, 12.02.2016, 11:18

Ответить

Дата: Пятница, 12.02.2016, 11:47 | Сообщение № 18

Группа: Друзья

Ранг: Старожил

Сообщений: 1232

Репутация: 326 ±

Замечаний: 0% ±

2003

Цитата AR84, 12.02.2016 в 11:14, в сообщении № 17

хотелось бы такую же информацию от Wasilic

Даже не знаю как пояснять.
Если в колонке D1-D2000 (диапазон можно поменять на любой) произошли какие то изменения, выполняется процедура.
Очищается таблица на листе «1» в диапазоне В3: D45 (ну как в примере, можно заменить на нужный). Задаются числовые переменные s, i. s=3 – начальная строка записи на листе «1». Цикл перебора “For i” начиная с 3-й строки до последней заполненной, проверяет колонку 4 то есть «D». И если в ней встречается «Да», переписывает данные в лист «1».
Как то так.

Сообщение отредактировал Wasilic - Пятница, 12.02.2016, 11:50

Ответить

Дата: Понедельник, 15.02.2016, 11:51 | Сообщение № 19

Группа: Пользователи

Ранг: Новичок

Сообщений: 12

Репутация: 0 ±

Замечаний: 0% ±

Excel 2003

Спасибо всем кто откликнулся за помощь. Решил свою задачу с помощью макроса Wasilic, подредактировал под свои нужды. Осталось сообразить как сделать печатную форму(открою новую тему).

Ответить

Авткопирование содержимого ячейки на другой лист по условию (Формулы/Formulas)

Страница 1 из 1
1

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