20

Вопрос к спецам по Excel

Имеется массив цифровых значений (порядка 10000 позиций) - столбец А. Как в столбец В вывести пропущенные (отсутствующие по порядку нумерации) значения из столбца А (данные на картинке внесены вручную).

Вопрос к спецам по Excel Excel, Формула

Дубликаты не найдены

+7

Пришлось аж зарегаться

Вот держи: в ячейку B2 вставишь

ЕСЛИ((A2-A1)=1;"";"от "&(A1+1)&" до "&(A2-1))

Создай умную таблице потом Cntrl + T

Отфильтруй потом второй столбец без пустых ячеек и все

Дальше будешь добавлять данные и она сама тебе и считать и фильтровать будет

это самый простой вариант, другой вариант с массивами, но по обстоятельствам смотря что с чем сравнивать в столбцах

раскрыть ветку 1
0
Благодарю, опробую
+9

Протягиваешь от 1 до 10к в другом столбике, и рядом с ним пишешь ДА. Потом делаешь ВПР по 1 столбцу, там где Н\Д - пропущенное число=D

раскрыть ветку 10
+7

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

раскрыть ветку 4
0
А как проверить наличие повторяющиеся значений в одном столбце? В частности есть заводские номера приборов, иногда содержащие в себе буквы.
раскрыть ветку 3
+1
Вот самый простой способ
0
ВПР

Что за зверь?

раскрыть ветку 3
+5
В столбце B прописываешь значения от 1 до 10000. Потом в строке С1 пишешь формулу =ВПР(В1;$А$1:$А$10000;1;0) и протягиваешь её до строки С10000. В итоге, в столбце С у тебя вылезут все значения из столбца А напротив столбца В. А там где получится #Н/Д - значит этих значений нет в столбце А
+5

Очень крутая штука в екселе

+1

Я бы сказал - основа экселя

+3

Держи макрос, @Wooooooofer


Sub Propuski()

Dim z As Integer

Dim i, j, k As Integer 'Счётчики

k = 1


Range("A1").Select

Selection.End(xlDown).Select

z = Selection.Row


For i = 2 To z

    If Cells(i + 1, 1).Value - Cells(i, 1).Value > 1 Then

        For j = 1 To Cells(i + 1, 1).Value - Cells(i, 1).Value - 1

            k = k + 1

            Cells(k, 2).Value = Cells(i, 1).Value + j

        Next

    Else

    End If

Next

End Sub

раскрыть ветку 3
+1

Благодарю, опробую

+1

Зашло)) Респект и уважуха))

раскрыть ветку 1
0

Здорово))))

+4

Но скорей всего это вообще не нужно. ТС придуамл этот извращенный способ для решения задачи, которая скорее всего более элегантно решается другими методами.

Поскольку эта настоящая задача неизвестна, судить о ней трудно.

раскрыть ветку 9
0

90%, что он ищет пропущенные номера фактур или каких-то подобных документов с требованием последовательной нумерации.

раскрыть ветку 5
0

Почти. Есть определенный список. Каждой позиции списка присвоен номер. После неоднократного редактирования списка некоторые позиции были удалены. После обновления списка новым позициям требуется присвоить номер. Чтобы не было дублирующихся номеров и требуется выяснить какие номера из имеющихся пропущены.

раскрыть ветку 4
-2
ТС придуамл этот извращенный способ для решения задачи, которая скорее всего более элегантно решается другими методами

Не важен метод, важен результат - вывести пропущенные значения из списка известных.

раскрыть ветку 2
-1

Ну, ответ известен. Простейший цикл на VBA. Без VBA имхо, нерешаемо.

раскрыть ветку 1
+2

Пропуски только по одному или бывают множественные?


1. Если пропуски по одному:

Пишете формулу: B2=A2-A1-1 и протягиваете донизу. Получаются нолики и единички. Пишете новую формулу: C1=IF(B2<1,"",A2-1) и снова протягиваете. Напротив пропусков появляются пропущенные значения, остальные ячейки пустые. Выделяете столбец C, копируете его как значения в новый столбец и применяете к нему удаление дубликатов (пустых ячеек).


2. Если пропуски бывают по два, по три, по четыре, то добавляете ещё столбцы:

D1=IF(B2<2,"",A2-2)

E1=IF(B2<3,"",A2-3)

F1=IF(B2<4,"",A2-4)

и так далее.

Потом надо собрать все эти столбцы: C, D, E, F в один и только потом дедублицировать и отсортировать.

Если таких столбцов надо много (не два-три, а десять-двадцать), можно и для них сделать формулу.


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


Если это не одноразовая задача, то проще написать скрипт. Если одноразовая, проще формулами.

+2

https://www.planetaexcel.ru/techniques/14/99/

Тебе подойдет последний вариант.

раскрыть ветку 1
0

Не успел отредактировать.

Вверху есть ссылка на файл с примером. Вкладка "способ 6". Чтобы включить формулу массива (в фигурных скобках) надо нажимать ctrl+shift+enter в строке формул.

0

В гугл таблицах

Иллюстрация к комментарию
раскрыть ветку 1
+1

Или в одну формулу

Иллюстрация к комментарию
0

Вы решили свой вопрос или вам помочь?

раскрыть ветку 2
0

Вопрос решен. Подошел макрос от @malrun

#comment_164421326

0

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

0
На егэ попадался такой вопрос, забавно
0

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

0

Нуууу, последствия могут быть разными

0

без программирования я бы использовал НАЙТИ() по полному списку от 1 до 10 000, и через ЕСЛИОШИБКА ловить ненайденные

0

цикл For по А, а внутри него проверять равно ли в В предыдущее текущему +1 , если нет, писать в В и двигать указатель дальше.

раскрыть ветку 2
0

А если значения не по порядку? Можно макросом сделать

раскрыть ветку 1
0
Да, скопировать на новый столбец макросом, всё отсортировать там, запустить макрос в новом столбце. Но схема, написанная выше не учитывает пропуск 2х чисел подряд
Похожие посты
884

Excellama: Выпадающие списки и логические формулы

Добрый день!

Как-то давно мне очень сильно полюбились выпадающие списки и то, как они работают в связке с логическими (и не только) формулами.


Предлагаю посмотреть как работают некоторые формулы и инструменты Excel, а для наглядности я придумала простенький пример, на основе которого мы и познакомимся с ними.

Excellama: Выпадающие списки и логические формулы Excel, Гифка, Длиннопост, Список, Формула

В этом примере мы разберем несколько инструментов Excel, а именно:

- вложенные формулы;

- выпадающие списки;

- логическая формула ЕСЛИ;

- формула блока «ссылки и массивы» ВПР.


Шаг 0 – введение.

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


Шаг 1 – работа с прайсом.

Есть перечень товаров, есть закупочная цена, необходимо определить, по какой цене продаем.

Excellama: Выпадающие списки и логические формулы Excel, Гифка, Длиннопост, Список, Формула

Для этого в ячейке C2 ставим наценку, допустим 1,47 (увеличение цены по сравнению с ценой закупки – 47%). Ее обязательно вывести в отдельную ячейку, потому что «а вдруг кризис» и придется все цены пересчитывать, не будем же мы всю таблицу заново просчитывать. Да и на будущее - в случае, если все строчки будут производить некоторые действия (умножение, сложение, вычитание, деление) с одной единственной ячейкой, то легче ее вывести отдельно и зафиксировать. Опять же, если мы захотим изменить наценку на все товары, то нам достаточно поменять значение только в одной этой ячейке, и вся наша таблица автоматически пересчитается.


А чтобы каждый раз в формуле вручную не ссылаться на одну и ту же ячейку (C2), то ее необходимо зафиксировать. Для этого необходимо поместить курсор в строку формул после знака умножить на C2 и нажать F4. Если в формуле появилось два (!) знака $, то ячейка зафиксирована.


Первоначальная формула в ячейке D5 будет выглядеть следующим образом =C5*$C$2

Excellama: Выпадающие списки и логические формулы Excel, Гифка, Длиннопост, Список, Формула

С первого взгляда все хорошо. Но это не так. В полученном результате в ячейке D5 больше двух знаков после запятой. Избавляемся от концов с помощью формулы ОКРУГЛ. Это наше первое знакомство с вложенной формулой. Формула в формуле. Самый простой способ «вложить» одно в другое – скопировать полученную формулу и следовать по инструкции ниже.


В ячейке D5 в строке формул пишем =ОКРУГЛ и открываем Аргументы функции (элемент Fx).

В поле «Число» вставляем скопированную формулу без знака «=».

В поле «Число_разрядов» ставим цифру 2, так как нам надо 2 знака после запятой.


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

Excellama: Выпадающие списки и логические формулы Excel, Гифка, Длиннопост, Список, Формула

Готово. Вы великолепны!


Шаг 2 – заполнение карточки заказа.

Начнем с выпадающего списка.

Выделяем диапазон, где хотим видеть выпадающий список (диапазон C8:C14). Переходим на вкладку Данные, группа Работа с данными, элемент Проверка данных.

Тип данных – список, источник – список товаров с листа «прайс» (диапазон B5:B16).

Excellama: Выпадающие списки и логические формулы Excel, Гифка, Длиннопост, Список, Формула

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


Есть два способа сделать нумерацию, но изначально в первую ячейку списка ставим цифру 1.


- щелкаем 2 раза за маркер автозаполнения и в появившемся окошке выбираем значение «Заполнить» - подходит при нумерации большого списка.


- тянем за маркер автозаполнения, параллельно зажав клавишу CTRL – может работать даже тогда, когда ячейка начинается не с 1, а с числа 824789, удобно продолжать нумерацию в середине списка.

Excellama: Выпадающие списки и логические формулы Excel, Гифка, Длиннопост, Список, Формула

Ура. Осталось два крупных шага (ВПР и логическая формула ЕСЛИ).


Для разгона давайте на ячейке F8 пропишем простую формулу умножения =D8*E8


Переходим к ВПР. Если по-простому, то формула «вертикальный поиск результата/вертикальный просмотр» берет ячейку со значением (товар в карточке заказа) и ищет ее в предложенном списке (в нашем случае в прайсе). После того, как формула нашла это значение в списке, она пробегает по этой строчке в прайсе и забирает оттуда нужное нам значение.


Если же говорить на языке формул, то все выглядит следующим образом:

Excellama: Выпадающие списки и логические формулы Excel, Гифка, Длиннопост, Список, Формула

Искомое значение - что ищем. Мы ищем блокнот (ячейка C8)

Таблица - где ищем. Ищем в прайсе. Указываем диапазон всей таблицы прайса (внимание: именно с ячейки B4 до ячейки D16).

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

Интервальный_просмотр - 0. Ставим 0 для получения точного результата (чтобы формула нашла конкретно "Блокнот А5", а не "блакнот а 5".

Excellama: Выпадающие списки и логические формулы Excel, Гифка, Длиннопост, Список, Формула

Далее осталось протянуть получившуюся формулу вниз, до строки итогов.


Пара простых шагов для финального штриха. Ставим автосумму в ячейке D15 и F15. Для упрощения действия можно запомнить следующее сочетание клавиш «ALT» и «=» (горячая клавиша для автосуммы).


В ячейке D16 считаем скидку с помощью логической формулы ЕСЛИ. Примем за правило, что если заказ собран на сумму более 5 000 руб., то скидка будет 10%.

Excellama: Выпадающие списки и логические формулы Excel, Гифка, Длиннопост, Список, Формула

Финальным аккордом в нашей и так уже затянувшейся песни будет простая формула

=F15-(F15*F16)

Excellama: Выпадающие списки и логические формулы Excel, Гифка, Длиннопост, Список, Формула

Все! С официальной частью закончили.


Небольшие советы:

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

2. Если Вас не устраивают и вездесущие нули, то избавиться от них тоже можно (не теряя при этом формулы). Заходим Файл – Параметры – Дополнительно – Показать параметры для следующего листа – Показывать нули в ячейках, которые содержат нулевые значения. Убираем галочку с данного пункта. Теперь нули не видны, но в ячейках все равно остались формулы, и при заполнении таблицы все будет считаться как раньше.


Обращаю Ваше внимание, что моей задачей в этом посте было просто показать, как работают разные формулы Excel. Скорее всего, в мире уже есть множество написанных программ для решения таких задач (та же 1С), возможно существуют разного рода макросы на выполнение всего, что написано в посте. Я же просто делюсь своими знаниями и надеюсь, что они Вам были полезны.


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


Когда рассказываешь про функции Excel, то возникает чувство, что все это никому не нужно и все и так все знают. Но если все же в этом посте было что-то полезное – дайте знать.

Показать полностью 9
917

EXCEL для чайников. подбор параметра

Всем приветы.

Начал писать обзорный пост про сводные таблицы и понял, что сегодня не хочется писать такой большой объем. Допишу потом.  А пока небольшой обзор функции «подбор параметра». Рассмотрим работу на небольшом примере. Допустим, что у нас есть некое количество товара, которые мы покупаем, а потом продаем с надбавкой:

EXCEL для чайников. подбор параметра Excel, Для чайников, Длиннопост, Формула, Функция

закупочная цена у нас фиксированная, количество и надбавка вбивается значениями, а остальное у нас прописано формулами. Итак, нам захотелось навариться не на 1600, на 2000, при этом захотелось нам поиграть с розничной стоимостью компасов. Ну, в примере все просто, сначала решаем в уме: нам нужно увеличить выручку на 400 р., потом эти 400 р. раскидать на 3 компаса, то есть на каждый компас нужно продать дороже на 133 р., то есть за 733 рубля, что составляет от 500 рублей изначальной цены, где то 733/500, то есть 1,47, то есть надбавка должна быть где-то 47 %. На самом деле этапов вычислений может быть больше,  главное чтобы начальное и конечное значение было связано через формулы. Подставляем 47% и видим что у нас 2005 рублей. Это из-за наших умственных округлений. Трудно ручками подобрать так чтобы было ровно 2000. Теперь идем во вкладку Данные – Анализ «что если» - Подбор параметра. Вводим вот так:

EXCEL для чайников. подбор параметра Excel, Для чайников, Длиннопост, Формула, Функция

нажимаем ОК и радуемся.

EXCEL для чайников. подбор параметра Excel, Для чайников, Длиннопост, Формула, Функция

Если значение можно найти то оно найдется. Если достигнуть нужный результат не получается то выдает инструмент выдает что то типа такого

EXCEL для чайников. подбор параметра Excel, Для чайников, Длиннопост, Формула, Функция

буду дальше пилить пост про сводные таблицы, свои пожелания и советы оставляйте в комментариях. Также передаю привет моим 460 подписчикам.

Показать полностью 2
Похожие посты закончились. Возможно, вас заинтересуют другие посты по тегам: