Выбрать дату в календареВыбрать дату в календаре

Страницы: 1 2 След.
Разделение текста по разделителю на строки
 

Здравствуйте, предыстория: по работе приходится копировать текст из pdf файла и вставлять текст в excel, чтобы в дальнейшем формулами выбирать/объединять/подставлять в отчеты. До не давнего момента этот текст при вставке в столбец А:A не содержал символа переноса строки (символ 10) и я корректно определял формулами начало одного блока текста и конец другого, поскольку при копировании всё вставлялось в отдельные строки. Но что то пошло не так и блоки начали объединяться в одной ячейке, что сильно затруднило обнаружение начала и конец каждого из блоков.

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

Во вложении для примера текст русской сказки  :) .

В ячейке A6 и A11 текст содержит символ переноса строки. В столбце B подсчитывается количество таких символов. В столбцах C:AA текст содержащий символ переноса строки разделяется по разделителю.

Задача формулами получить в столбце AB номер строки, той ячейки, которая содержит символ переноса строки, и повторить такой номер, столько раз, сколько символов переноса строки в ячейки (в примере номер строки ячейки A6 повторился 4 раза, поскольку содержит 3 разделителя + 1 повторение для конца ячейки). А затем соответственно в столбце AC проставить номер столбца таблицы, чтобы формулой получить разделенный текст (в примере для ячейки A6 проставлено 1,2,3,4 для того чтобы получить конкретную часть текста).

Ну или если возможно это все сделать без таких заморочек с доп. столбцами и без макросов, то буду только рад.

Изменено: Максим - 23.03.2024 17:28:07
Поиск значения по условию с динамическим номером столбца
 
Здравствуйте, пытаюсь получить все значение кодов товара из столбца B по условию. Поскольку одним из условий является поиск номера столбца, у меня возникли проблемы, так как аргумент – номер столбца – является статическим значением, что я и попытался решить через СМЕЩ.
Поскольку использование функции СМЕЩ не позволяет обновлять данные из закрытой книги (в случае если я получаю данные в отдельном файле), пришлось от неё отказаться.
Возможно ли как то иначе решить проблему? Для заполнения, подготовил поля в столбце Q
Пример прилагаю)
Изменено: Максим - 06.07.2023 14:20:29
Скорректировать функции ОСТАТ, СТРОКА в Excel
 
Здравствуйте, в ячейках M3:S32, вписана формула, вычленяющая из таблицы A:J, процедуры по выпадающим на дни недели даты. Из за того что делитель не правильно определен, ячейка 1 среды и 4 четверга не содержит имени заголовка и номера процедуры.
Прошу подсказать, какой делитель должен быть, и по каким принципам он определяется.  :oops:

=ЕСЛИОШИБКА(СЦЕПИТЬ(ИНДЕКС(Процедуры[#Заголовки];ОСТАТ(НАИМЕНЬШИЙ(ЕСЛИ(Процедуры[[Подача запросов]:[Расм. заявок]]=M$1;СТРОКА(Процедуры[№ изв.])-СТРОКА(E$1)+СТОЛБЕЦ(Процедуры[[#Заголовки];[Подача запросов]:[Расм. заявок]])/10);СТРОКА()-СТРОКА($M$2));1)*10);" по изв. № ";ИНДЕКС(Процедуры[№ изв.];ЦЕЛОЕ(НАИМЕНЬШИЙ(ЕСЛИ(Процедуры[[Подача запросов]:[Расм. заявок]]=M$1;СТРОКА(Процедуры[№ изв.])-СТРОКА(E$1)+СТОЛБЕЦ(Процедуры[[#Заголовки];[Подача запросов]:[Расм. заявок]])/10));СТРОКА()-СТРОКА($M$2)))));" ")

Автоподбор высоты (диапазон по условию)
 
Добрый день, подскажите, как заменить диапазоны 2:501 и $AP$1:$AP$501 на диапазон равный заполненным значениям в столбце AP2:AP501, чтобы ему обрабатывать меньше строк, и к примеру, если заполнено только 20 строк, сделать авто подбор и фильтр на диапазон AP2:AP20    :)

Код
Sub AutoFit501()
    ActiveSheet.Range("$AP$1:$AP$501").AutoFilter Field:=16, Criteria1:="<>"
    Rows("2:501").EntireRow.AutoFit
End Sub
Изменено: Максим - 18.01.2022 13:47:18
Получить данные из html table VBA
 
Добрый день, макрос вызывается по гиперссылке в ячейке A1, в ячейке С1 вписан адрес сайта с которого я планирую выгружать таблицу.
Подскажите:
1) Как после выполнения загрузки с page=1, реализовать выгрузку с page=2 и так далее до 394
2) Выполнять выгрузку со следующей страницы выгружая данные под последней заполненной строкой, чтобы не пришлось это делать в ручную.

Пример прикрепляю
Получить данные из html table VBA
 
Добрый день, помогите в получении данных не со всех таблиц которые есть, а с конкретной, вот её класс - class="valueTable font6Size alignCenterCell"

Код
Sub Plangrafik()
    Dim htm As Object
    Dim Tr As Object
    Dim Td As Object
    Dim Tab1 As Object
    Web_URL = VBA.Trim(Sheets(1).Cells(1, 1))
    Set HTML_Content = CreateObject("htmlfile")
    With CreateObject("msxml2.xmlhttp")
        .Open "GET", Web_URL, False
        .send
        HTML_Content.Body.Innerhtml = .responseText
    End With
    Column_Num_To_Start = 1
    iRow = 2
    iCol = Column_Num_To_Start
    iTable = 0
    For Each Tab1 In HTML_Content.getElementsByTagName("table")
        With HTML_Content.getElementsByTagName("table")(iTable)
            For Each Tr In .Rows
                For Each Td In Tr.Cells
                    Sheets(1).Cells(iRow, iCol).Select
                    Sheets(1).Cells(iRow, iCol) = Td.innerText
                    iCol = iCol + 1
                Next Td
                iCol = Column_Num_To_Start
                iRow = iRow + 1
            Next Tr
        End With
        iTable = iTable + 1
        iCol = Column_Num_To_Start
        iRow = iRow + 1
    Next Tab1
End Sub
Изменено: Максим - 22.07.2021 21:17:25
Количество значений, прошедших по учету и имеющих дату операции
 
Добрый день

в столбце А отражены планируемые к трате значения, а в столбце С - значения которые прошли по учету. Помогите подсчитать количество значений прошедших по учету и имеющих дату операции и сопоставить эти значения в столбце I по критерию ориентировочных дат покупки, а в столбце K c дополнительным критерием по столбцу E c суммой таких ячеек

Результат в столбец J:K
Код
=СУММПРОИЗВ((условие1)*(условие2)*(условие3)) 
не подходит, так как значения условий не пересекаются по горизонтали
Изменено: vikttur - 02.07.2021 23:15:53
Получить данные со второго листа по двум условиям на первом
 
Добрый день, в столбец Осн.1!А:D попадают значения по условию Рапорт!$D$3:$D$202>0

Помогите на лист Осн.1 Подтянуть все строки с листа ТЗ:
В столбец Осн.1!F:J попадают значения по условию:
1) Рапорт!$D$3:$D$202>0
2) Соответствия № п/п в столбце Рапорт!$A$3:$A$202 со значениями со столбце ТЗ!$A$3:$A$1000

Попытался сделать формулой, где вначале в качестве результата я получаю номер пункта с листа Рапорт! (по аналогии со столбцом Осн.1!А), а потом по такой же формуле она обращается на лист ТЗ, чтобы вытянуть схожие номера пункта, но ничего кроме как ТЗ с первым удовлетворяющим значением не нашлось.  :) HELP
Подстановка значений по нескольким условиям
 
:oops: Прошу прощения за то, что спрашиваю как реализовать банальные вещи, но не смог.
В столбец Т2:Т51 записать формулу чтобы напротив ячеек где есть числа в столбце D совмещались параметры из столбца F и характеристики из столбца G или H при любом из условии: 1) Если в столбце I была характеристика "Соответствует КТРУ"; 2) Если в столбце I была характеристика которая начинается на "Параметр изменен", к ней добавляется " (Параметр изменен)" . Область поиска для каждой из строк, ограничена одними и теми же цифрами в столбце С

Файл прилагаю.
Изменено: Максим - 05.02.2021 19:43:45
Формировать диапазон без пропусков для выпадающего списка
 
Создаю именной диапазон для выпадающего списка, сами имена находятся горизонтально. Что поменять в формуле?
Пробовал поменять с высотой, не получилось

СМЕЩ(ссылка;смещ_по_строкам;смещ_по_столбцам;[высота];[ширина])
Код
=СМЕЩ(Общее!$B$1;0;0;СЧЁТЗ(Общее!$B$1:$AA$1)-СЧИТАТЬПУСТОТЫ(Общее!$B$1:$AA$1))
Все рабочие дни в заданном интервале дат
 
Здравствуйте, в столбце $D$2:$D$370 отображаются все рабочие дни, начальная дата А2, конечная дата B2
Не могу додумать, как заставить работать формулу не учитывая выходные, использовать следующую рабочую дату, если она в конечную не уперается
Код
=ЕСЛИ(РАБДЕНЬ.МЕЖД($A$2-2+СТРОКА(D1);1;1;$C$2:$C$370)<=$B$2;РАБДЕНЬ.МЕЖД($A$2-2+СТРОКА(D1);1;1;$C$2:$C$370);"")
Изменено: Максим - 08.12.2020 16:35:13
Поиск последнего значения по условию
 
Здравствуйте, помогите с формулой, реализация массивом с впр приветствуется, ветки читал, но что то голова уже отказывается думать.

В столбце G вписываю номер извещения, записываю в столбец I и H новую цену контракта и тип соглашения. Помогите с формулой для стобцов D и E: если извещение повторилось в столбце G: взять значение нижнее по списку из столбца I и H соответственно, но при условии если вписана дата соглашения в столбце J для крайней выбирающейся строчки, если нет, оставить последнее найденное значение, если и его нет, тогда пусто
Подсчет итогового количества с одинаковой единицей измерения
 
Добрый день, помогите реализовать формулой подсчет количества, в столбце F1 пример подсчета, а то устаю каждый раз в ручную подсчитывать  :D
Изменено: Максим - 28.08.2020 16:00:20
Сумма прописью WORD, Помогите допилить макрос WORD
 
Добрый день, макрос в normal.dot пишет сумму 100000,00 видом (сто тысяч) рублей 00 копеек
Помогите реализовать запись с дубрилованием суммы с разделением пробелом разрядов

Образец:
Сумма: 100000,00 Пропись: 100 000 (сто тысяч) рублей 00 копеек

Код
Sub Прописью()
    If Selection.Type <> wdSelectionNormal Then Exit Sub
    Dim NNN, NNNOst, NNNLen
    With CreateObject("VBScript.RegExp"): .Global = True: .Pattern = "\D": NNN = .Replace(Selection.Range.Text, ""): End With
    NNNLen = Len(NNN)
    If NNNLen > 17 Then Exit Sub
    NNNOst = Format(Right(NNN, 2), "00", 2)
    If NNNLen > 2 Then NNN = Left(NNN, NNNLen - 2) Else NNN = 0
    ' NNN = NNN & "," & NNNOst
    If Len(NNN) Then Selection.Start = Selection.End: Selection.Range.Text = " (" & СУМ_ПРОП(NNN, NNNOst)
End Sub
Function СУМ_ПРОП$(ByVal ЧИСЛО#, ByVal ЧИСЛОКОП#)
    Dim rub$, kop$, ed, des, sot, nadc, RAZR, i&, m$
    If ЧИСЛО >= 1E+15 Or ЧИСЛО < 0 Then Exit Function
    sot = Array("", " сто", " двести", " триста", " четыреста", " пятьсот", " шестьсот", " семьсот", " восемьсот", " девятьсот")
    des = Array("", "", " двадцать", " тридцать", " сорок", " пятьдесят", " шестьдесят", " семьдесят", " восемьдесят", " девяносто")
    nadc = Array(" десять", " одиннадцать", " двенадцать", " тринадцать", " четырнадцать", " пятнадцать", " шестнадцать", " семнадцать", " восемнадцать", " девятнадцать")
    ed = Array("", " один", " два", " три", " четыре", " пять", " шесть", " семь", " восемь", " девять", "", " одна", " две")
    RAZR = Array(" триллион", " триллиона", " триллионов", " миллиард", " миллиарда", " миллиардов", " миллион", " миллиона", " миллионов", " тысяча", " тысячи", " тысяч", ") рубль ", ") рубля ", ") рублей ")
    rub = Left(Format(ЧИСЛО, "000000000000000"), 15)
    kop = Left(Format(ЧИСЛОКОП, "00"), 2)
    If CDbl(rub) = 0 Then m = " ноль"
    For i = 1 To Len(rub) Step 3
        If Mid(rub, i, 3) <> "000" Or i = Len(rub) - 2 Then
            m = m & sot(CInt(Mid(rub, i, 1))) & IIf(Mid(rub, i + 1, 1) = "1", nadc(CInt(Mid(rub, i + 2, 1))), _
                    des(CInt(Mid(rub, i + 1, 1))) & ed(CInt(Mid(rub, i + 2, 1)) + IIf(i = Len(rub) - 5 And CInt(Mid(rub, i + 2, 1)) < 3, 10, 0))) & _
                    IIf(Mid(rub, i + 1, 1) = "1" Or (Mid(rub, i + 2, 1) + 9) Mod 10 >= 4, RAZR(i + 1), IIf(Mid(rub, i + 2, 1) = "1", RAZR(i - 1), RAZR(i)))
        End If
    Next i
    m = LTrim(m)
    СУМ_ПРОП = RTrim(Left(m, 1)) & Mid(m, 2) & kop & " копе" & IIf(kop \ 10 = 1 Or ((kop + 9) Mod 10) >= 4, "ек", IIf(kop Mod 10 = 1, "йка", "йки"))
End Function
Подбор слагаемых для нужной суммы формулой
 
Добрый день)
В ячейку D2 вписывается необходимая итоговая цена, которая должна быть (в идеале) равна той, которая получится в I2. Необходимо автоматическим подбором значений H2:H35 добиться значения D2, равномерным увеличением/уменьшением значений H2:H35 на +-0,01 в зависимости от их изначальной стоймости и количества.
Материал и аналогичные топики смотрел (не смог в свой пример  :sceptic: ), возможно ли реализовать не надстройкой, а формулой?
Изменено: Максим - 19.05.2020 11:55:22
Макрос обновления всех сводных таблиц на листе
 
Здравствуйте, макрос обновляет все сводные таблицы на листе "Сводная подразделения", при изменении в умной таблице "Таблица1" на другом листе.
Как избавиться от привязки к конкретной умной таблице, и сделать обновление сводных таблиц:

1 вариант: при любом изменении данных на листе.

2 вариант: при изменении только в "Таблица1", "Таблица2"

:) Если не сложно, пришлите оба варианта, спасибо
Код
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Target.Parent.ListObjects("Таблица1").Range) Is Nothing Then
        Dim pt As PivotTable
        For Each pt In Sheets("Сводная подразделения").PivotTables
            pt.RefreshTable
        Next
    End If
End Sub
Поиск первого подходящего значения по условию, установленного внутри формулы
 
Добрый день, помогите решить следующую проблему: функция ИНДЕКС(ПОИСКПОЗ возвращает первое найденное значение, но необходимо в фомулу добавить условие: возвращать значение, с порядковым номером большим среди всех удовлетворяющих наименование, и при отсутсвии в соседнем столбце "Отсутсвует"
Изменено: Максим - 30.04.2020 15:44:04
Список праздников РАБДЕНЬ.МЕЖД, помогите избавиться вписать праздники в формулу
 
:) Добрый день, хочу избавиться от столбца с датами праздников и вместо ссылки в формуле D2:D14, вписать их вручную туда, но не могу понять, как вписать больше одной даты:
Код
=РАБДЕНЬ.МЕЖД(нач_дата;количество_дней;[выходные];[праздники])

=РАБДЕНЬ.МЕЖД($C$1+10;1;"1100111";$D$2:$D$14)
Вот собственно производственный каллендарь:
01.01.2020
02.01.2020
03.01.2020

Пришлите для примера как вписать их, а дальше сам, по всему году пробегусь
Изменено: Максим - 05.03.2020 11:42:50
Минимальное значение исключая ноль НЕ ДЛЯ ДИАПАЗОНА
 
    Добрый вечер! Помогите реализовать следующее:
   В столбце D вписана формула по определению минимального, а в столбеце Е максимального значения среди ячеек Q:S, хочу от них избавиться, объединив формулы из каждой в одну в стобцы D и Е соответсвтенно для своих задач, но это не получается сделать, вот почему: предполагается, что заполнение столбцов всех 3 производителей не потребуется, формулы Q:S видимо дописаны не корректно, т.к. минимальным значением не может быть ноль
  Читал темы с ответами vikttur и решением через
Код
=НАИМЕНЬШИЙ(A1:A9;(СЧЁТЕСЛИ(A1:A9;0))*(СЧЁТЕСЛИ(A1:A9;"<0")=0)+1)
но там для диапазона, а тут из за того что я хочу убрать столбцы, для конкретных ячеек с формулами - не получается  :oops:  
Изменено: Максим - 14.02.2020 10:31:52
Сумма диапазона чисел с предварительной операцией над ними без дополнительного столбца
 
Добрый день, ячейка D2 cодержит сумму ячеек диапазона С2:С73, которые полученны в результате вычитания из разности ячейки B2-100.
Помогите с формулой для D2 (нехочу каждую ячейку вроде Cумм(B2-100;B3-100,,,,) для суммы указывать, необходимо диапазон) и D3:D73, чтобы избавится от столбца С2:С73
Макрос многоуровневой нумерации
 
Добрый день, помогите подредактировать макрос, задача добавить функию которая перед выполнением будет очищать ВЕСЬ столбец листа, а не нумеруемый интервал от введенных ранее чисел, а потом нумеровать сам интервал :) такое возможно?
Код
Sub Нумерация()
    Application.EnableEvents = False
    Dim y As Long
    y = Cells(Rows.Count, 1).End(xlUp).Row
    Dim a As Variant
    a = Range(Cells(1, 4), Cells(y, 4))
    Dim i As Long
    Dim j As Long
     
    Dim v As Range
    Set v = ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible)
     
    For y = 2 To UBound(a)
    If Not Intersect(Cells(y, 5), v) Is Nothing Then
         
        If a(y, 1) <> "" Then
            i = i + 1
            j = 1
            Cells(y, 5).Value = i
        Else
            Cells(y, 5).Value = i & "." & j
            j = j + 1
        End If
    End If
    Next
    Application.EnableEvents = True
End Sub
Изменено: Максим - 09.01.2020 12:50:43
Определение порядка получения данных с листа на другой лист формулой
 
Название темы: Определение порядка получения данных с листа на другой лист формулой

Необходимо получить данные с листа !Рапорт по столбцу Е в лист !Графики
Где: очередность определяется формулой: вначале взять нужны строки со значением столбца Е=1, потом по значению 2, потом "п.4", потом 3
Изменено: Максим - 30.12.2019 14:45:05
Автоопределение диапазона СУММПРОИЗВ по условию
 
Добрый день, в ячейке I1 вписана формула
Код
=СУММПРОИЗВ($F$3:$F$52;I$3:I$52)
В каждой строчке указанного диапазона 3:52 в столбце B вписано число.
Как реализовать зависимость высоты диапазона начиная с $B$3, чтобы самому не протягивать в ручную а "конец" формула сама определяла по вписанным значениям в столбец B
Автоматическая перенумерация строк при применении фильтра
 
Добрый день, помогите в создании формулы.  :)
Фильтр в столбце C. При включении фильтра в серых строках стобца Е будет порядковый номер первого уровня, в белых порядковые второго уровня. В серых всегда стобцец D c номером.
ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3 не подойдет, так фильтр может затронуть и первое наименование и туда вписывать 1 будет не корректно
Код
=ЕСЛИОШИБКА(ЕСЛИ(ЕЧИСЛО(D2)=ИСТИНА;ПРОМЕЖУТОЧНЫЕ.ИТОГИ(103;$D$2:D2);E1+0,1);"")

Столкнулся с проблемой при прибавлении 0,1, нужно 0.1. Может какой то иначе формулой можно?

Изменено: Максим - 23.12.2019 15:10:52 (Додумался до чего то похожее на формулу)
Нумерация заглавных (ячейки 1 уровня) и подзаголовных ячеек (ячейки 2 уровня) по условию
 
Нумерация заглавных (ячейки 1 уровеня) и подзаголовных ячеек (ячейки 2 уровня) по условию

Столбец А: нумерация по строчкам, исключая первую
Столбец D: нумерация по порядку строк с наименованием (у них всегда значения Q не пустые)
Столбец С: такая же нумерация как и в D, но и белых
Столбец B: если значение столбца Q равно 0, в соответсвующих значениям столбца C строк появляется 0, для того чтобы пустые значения я потом отфильтрововал
:oops: Функцию ВПР понял, а вот реализовать нумерацию через индексы, поискпоз, счёт и прочее не получается.
А автоматизировать такой процесс только умной таблицей можно?
Изменено: Максим - 18.12.2019 11:13:16
Убрать ошибку #Н/Д
 
Как убрать ошибку, пытаюсь формулой:
Если при функции ВПР выдает ошибку, то пусто, если нет, то выполняем ВПР, можно как то решить? :)
Код
=ЕСЛИ(ВПР(D2;'Расчет 21.20'!A:G;6;1)="#Н/Д";"";ВПР(D2;'Расчет 21.20'!A:G;6;1))
Создание многоуровнего списка по условию
 
Доброго вечера, помогите пожалуйста решить проблему:
1) с автоматической нумерацией столбцов без перетаскивания формулы при удалении или добавлении строк (без использования умных таблиц)
2) с определением уровня списка предпочтительно по цвету ячейки (1-серый, 1.1-без заливки (если не возможно, то белый)) или введенным формулам

Посмотрел форум, функция промежуточные итоги и счёт для 1 столбца не подходит, потому что необходимо сохранять значения 1 столбца при фильтрах  :oops:  
Суммирование значений при условии, что в соседних ячейках находятся числа.
 
Здравствуйте, нужно подсчитать сумму денег в диапазоне AC10:AC4160 по условию если напротив этих сумм в диапазоне F10:F4160 вписанно число. Пробую так
Код
=СЧЁТЕСЛИ(AC10:AC4160;(ЕЧИСЛО(F10)=ИСТИНА))

и не работает(

Помогите с решением  :)

Поиск по 2 значениям в горизонтали (Тоже самое что у paff но тяжелей)
 
Добрый день, таже самая задачка по подсчету количества одинаковой номенклатуры со значениями. и по возможности делать это не в отдельном столбце, а в том же самом, сам к сожалению не смог в ответах на тему у puff разобраться
:) Объясню мой бред: есть 3 уровня
1. итого номенклатуры за каждый отдел (желтый цвет) <- подсчитать сумму вторых уровней
1.1.  номенклатура в 1 отделе (серый) <- подсчитать сумму третьих уровней
1.1.1. номенклатура в 1 отделе по годам
1.2. номенклатура во 2 отделе (серый) <- подсчитать сумму третьих уровней
1.2.1 номенклатура во 2 отделе по годам
Делаю формулу через если(индекс поискпоз);сумм, но без результатно
Файл с примером прилагаю
Изменено: liptonenochek - 12.11.2019 12:51:13
Первые совпадающие значения в столбце
 
Добрый день, есть столбец, в который вводят значения к примеру:

21.20.10.149-000001-1-00001-0000000000000
21.20.10.134-000068-1-00001-0000000000000
21.20.10.251-000011-1-00005-0000000000000

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