Ответы пользователя по тегу Excel
  • Как реструктуризировать данные в Excel?

    honor8
    @honor8
    Принципы быстродействия VBA в описании
    1. Выделить данные в таблице. Вставка (меню) - Сводная таблица
    2. В появившемся окне выбрать бегунок "На существующий лист", выбрать ячейку в колонке "B" и нажать "Ок"
    3. Щёлкнуть на пустую Сводную таблицу, перетащить поле "Характеристика" в облать "Столбцы"
    4. Перетащить поле "Название" в облать "Строки"
    5. Ещё раз перетащить поле "Название" в облать "Значения"
    6. Конструктор (меню) - Общие итоги (выпадающее меню) - Отключить для строк и столбцов
    7. Конструктор (меню) - Макет отчета (выпадающее меню) - Показать в табличной форме
    8. Перетащить поле "Код" в облать "Строки"


    Это часть, котороую можно реализовать сводной таблицей. В названиях колонок будут наименования характеристик, а в таблице будет их количество. Для создания таблицы как на скриншоте нужно писать макрос.
    Ответ написан
    Комментировать
  • Как полностью отключить автозамену числа датой?

    honor8
    @honor8
    Принципы быстродействия VBA в описании
    Установите какой-нибудь современный IDE с плагином для редактирования таблиц.

    Если нужен именно Excel, то нужно искать как изменить содержание внутри шаблона xls?\xl\styles.xml
    с numFmtId="0" на numFmtId="49" (текстовый формат для книги).

    Статья по теме https://habr.com/ru/articles/593397/
    Ответ написан
    Комментировать
  • Как сделать макрос в Excel для изменения формата даты?

    honor8
    @honor8
    Принципы быстродействия VBA в описании
    А вот с макросами нужна помощь. Не затруднит прислать сам код (полный)?

    Если у вас нет времени разбираться - идите на фриланс. Студентам тоже кушать надо.
    Ответ написан
    Комментировать
  • Как получить доступ к находящемуся в памяти объекту, инстанцированному в VBA, из VBScript?

    honor8
    @honor8
    Принципы быстродействия VBA в описании
    Изменить раннее связывание на позднее.* ** И не надо за памятью гоняться.
    Ответ написан
    Комментировать
  • Как в Excel найти ВСЕ одинаковые значения в разных листах?

    honor8
    @honor8
    Принципы быстродействия VBA в описании
    Я бы использовал коллекции, в которых можно отсекать дубликаты значений. Раз вы привыкли к выделению блоков, то вот пример перебора выделенных блоков в листах справа от активного.
    Option Explicit
    '123456789012345678901234567890123456h8nor@ya567890123456789012345678toster56789
    Private IndexColors As New Collection, CurrentColor As Integer, BaseSheetIndex As Byte
    
    Sub DuplicatesColoring()
      Dim cell As Range, selected As Range
      
      Set selected = Selection
      If BaseSheetIndex = 0 Then _
        BaseSheetIndex = ActiveSheet.Index: CurrentColor = 3 ' Начальные значения
      selected.Interior.ColorIndex = -4142 ' Убрать заливку
      
      For Each cell In selected
        If Not IsEmpty(cell.Value) Then
          On Error Resume Next ' Включить обработку исключений
            If BaseSheetIndex = ActiveSheet.Index _
            And WorksheetFunction.CountIf(selected, cell.Value2) > 1 Then
              IndexColors.Add CurrentColor, CStr(cell.Value2) ' Заполнить коллекцию
              If Not Err.Number = 457 Then CurrentColor = CurrentColor + 1
            End If
            
            cell.Interior.ColorIndex = IndexColors(CStr(cell.Value2))
          On Error GoTo 0
        End If
      Next cell
      
      Do Until ActiveSheet.Index > ActiveWorkbook.Sheets.Count - 1
        ActiveWorkbook.Sheets(ActiveSheet.Index + 1).Activate
        DuplicatesColoring
        Exit Sub ' Предотвратить бесконечную рекурсию
      Loop
      
      Worksheets(BaseSheetIndex).Activate
    End Sub
    Ответ написан
    5 комментариев
  • Автоматическое создание ссылки при вводе в ячейку ключевого слова?

    honor8
    @honor8
    Принципы быстродействия VBA в описании
    Нужно составить алгоритм и написать макрос.
    Ответ написан
    Комментировать
  • Как в Calc или Excel сделать из текста ссылки?

    honor8
    @honor8
    Принципы быстродействия VBA в описании
    В Excel - формула ГИПЕРССЫЛКА
    Ответ написан
    Комментировать
  • Как удалить подстроку из другой колонки в Excel?

    honor8
    @honor8
    Принципы быстродействия VBA в описании
    Можно сделать интереснее, если нужно выделить подстроки в виде октетов из IPv4-адрес (например, можно встретить формат 090.088.112.223). Будем применять формулу =ПОДСТАВИТЬ(), которая имеет 4-ый необязательный параметр для замены вхождения по счёту (используем как счётчик).

    Для этого нужно отделить сначала правую часть, и превратить её в число. Для первого октета формула будет сложнее, чем =ЛЕВБ() (чтобы можно было отследить этапы замены), где 1 - счётчик вхождений точки:
    =ЗНАЧЕН(ЛЕВБ(ПОДСТАВИТЬ(A1;".";ПОВТОР(" ";15);1);15))

    Для выбора остальных октетов нужно будет ещё отбросить и левую часть. Теперь первый счётчик имеет значение вхождения точки 2 (сначала нужно отсечь правую часть за 2-ой точкой), а второй счётчик имеет значение точки 1 (затем нужно отсечь левую часть до 1-ой точки):
    =ЗНАЧЕН(ПРАВБ(ПОДСТАВИТЬ(
      СЖПРОБЕЛЫ(ЛЕВБ(ПОДСТАВИТЬ(A1;".";ПОВТОР(" ";15);2);15));
    ".";ПОВТОР(" ";15);1);3))

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

    Задача схожа с "Excel — как вытащить из относительной ссылки содержимое между двух последних «/»?".
    Ответ написан
    Комментировать
  • Как сделать по часовой счетчик в excel?

    honor8
    @honor8
    Принципы быстродействия VBA в описании
    Если формулами в течение дня, то можно задать начальное время: A1 = "12:00:00" (формат ячейки Время), тогда формула: =(ЧАС(ТДАТА())-ЧАС(A1))*3 (формат ячейки с формулой всегда Общий)
    Ответ написан
    3 комментария
  • Как добавить координаты населенных пунктов в таблицу и подтянуть (в следующую ячейку) описание из книги pdf?

    honor8
    @honor8
    Принципы быстродействия VBA в описании
    Данные уже вытащили из PDF, - на мой взгляд самое мерзкое занятие.
    Вы поставили тег Excel, и я полагаю вам нужен запрос на VBA к геокодеру через функцию (в примере указан геокодер Nominatim,на nginx не разворачивается, только на Apache запросы можно делать не чаще в 1 сек).
    Function GetResponse(ByVal request As String, _
      Optional ByVal lowerCase As Boolean = True) As String
      '3456789012345678901234567890123456h8nor@ya567890123456789012345678toster56789
      Const TIMEOUT As Double = 6 ' sec
      Const uRL As String = "https://nominatim.osm.org/search?county=RU&format=geocodejson&q="
      
      If lowerCase Then request = LCase(request)
      Static winhttp As Object ' WinHttpRequest
      
      On Error Resume Next
        If winhttp Is Nothing Then
          Set winhttp = CreateObject("WinHttp.WinHttpRequest.5.1")
        Else
          If Not winhttp.Status = 200 Then ' Error
            Set winhttp = Nothing
            End 'or MsgBox "to many requests", vbCritical: Exit Function
          End If
        End If
        
        ' Replace characters to "%"+HEX in UTF-8 (and SPACE to PLUS), except DIGIT (%30-%39), 
        ' except ALPHA (%41-%5A and %61-%7A), hyphen (%2D), period (%2E), underscore (%5F), tilde (%7E)
        request = Replace(Replace(request, ",", ",%20"), "%20-,", "")
        ' The True <- specifies async behaviour
        winhttp.Open "GET", uRL & request, True: DoEvents
        ' Add Headers
        winhttp.SetRequestHeader "Pragma", "private"
        winhttp.SetRequestHeader "Cache-Control", "no-store"
        winhttp.Send: DoEvents
        
        If Not winhttp.WaitForResponse(TIMEOUT) Then
          Debug.Print "timeout", uRL & request: Exit Function
        ElseIf winhttp.Status = 429 Then
          Debug.Print "to many requests", uRL: Exit Function
        End If
        
        GetResponse = winhttp.responseText
        ' Check response length
        If Not Len(GetResponse) > 189 + Len(Replace(request, "%20", " ")) Then
          Debug.Print uRL & request
        End If
    End Function

    В ответе будут объекты в формате GEOJSON, которые также нужно будет обработать. Рекомендую выбирать тип точек по следующим значениям: "city", "town", "village", "hamlet", "isolated_dwelling", "locality".

    Если карта будет интерактивная, рекомендую использовать плагин Leaflet (не лагает как Гугл. Кстати, к геокодеру Гуглу можно обращаться руками через сайт).

    +
    Как использовать отладчик:
    1. Открыть MS Excel
    2. Открыть VBA в приложении (Alt+F11)
    3. Выбирать в меню Insert -> Module
    4. Скопировать функцию, и написать её вызов в отдельной процедуре
    5. Нажать F5 для выполнения процедуры
    Ответ написан
    1 комментарий
  • Как сделать автоперемещение фигур в Excel?

    honor8
    @honor8
    Принципы быстродействия VBA в описании
    Для выделения одного или нескольких объектов щелкните на них, удерживая нажатой клавишу Shift. Затем переместите объекты. Группировкой выделяют группы объектов для ускорения изменения их атрибутов.
    Ответ написан
    Комментировать
  • Как в VBA вывести объект в Immediate Window?

    honor8
    @honor8
    Принципы быстродействия VBA в описании
    Для объектов, коллекций, массивов есть окно Locals Window (показывает дерево для отладки текущей функции по F8).
    Ответ написан
    Комментировать
  • Можете посоветовать легкий просмотрщик excel-файлов?

    honor8
    @honor8
    Принципы быстродействия VBA в описании
    Закончилась поддержка https://support.microsoft.com/en-us/help/273711/.
    Ответ написан
    Комментировать
  • Как определить второе, третье, четвертое повторяющееся значение в excel?

    honor8
    @honor8
    Принципы быстродействия VBA в описании
    Для этой задачи лучше подойдёт сводная таблица (способ #4, выбрать "Итоги по" = Количество).
    Если сложно, то можно создать промежуточную таблицу формулой СЧЁТЕСЛИ, затем отсортировать по убыванию.

    ЗЫ: Не указана версия. В Excel 2016 есть новые формулы МАКСЕСЛИ/МИНЕСЛИ.
    Ответ написан
    Комментировать
  • Как сконверитровать json в xls через powershell?

    honor8
    @honor8
    Принципы быстродействия VBA в описании
    Комментировать
  • Не активна кнопка в excel, как найти решение?

    honor8
    @honor8
    Принципы быстродействия VBA в описании
    Есть вероятность, что не в пароле дело. Попробуйте убрать галку в меню: Рецензирование -> Доступ к книге -> Правка (вкладка) -> ☐ Разрешить изменять файл нескольким пользователям одновременно.
    Ответ написан
  • Сбились связи в Excel?

    honor8
    @honor8
    Принципы быстродействия VBA в описании
    Это вопрос не про Excel. Откатить состояние можно только на сетевом диске, если администратором домена установлена эта настройка: Свойства файла -> Предыдущие версии (вкладка)
    В файлах xlsm нет связей, но есть формулы и макросы. Если они не работают как положено - их кто-то поменял.

    Ключ к решению: своевременно бэкапить нужные файлы/нанять компетентного специалиста.
    Ответ написан
    5 комментариев
  • Что делать с Overflow в VBA?

    honor8
    @honor8
    Принципы быстродействия VBA в описании
    Тип данных Long используется для хранения чисел от -2147483648 до 2147483647. Для бóльших значений используется тип Double (или Currency для денежных единиц), но свыше 15 знаков точность числа с плавающей точкой будет падать. Неопределённый тип можно преобразовать программно CDec("24,0") в Decimal (16 байт). Эквивалентное написание:
    CLng(24) = 24& ' 4 байт
    CLngLng(24) = 24^ ' 8 байт - только для x64
    CDbl(24) = 24# ' 8 байт
    CCur(24) = 24@ ' 8 байт
    Ответ написан
  • Как ускорить копирование (ВПР) картинок в Excel (VBA)?

    honor8
    @honor8
    Принципы быстродействия VBA в описании
    У объекта коллекции Sheets.Shapes.Item(1) есть свойство Left и Top, если второе задаётся значением высоты строки, то первое зависит от множества других параметров. Картинка находится не в ячейке, как это кажется на первый взгляд. Поэтому лучше сразу задавать рисунку имя Shapes.Item(1).Name.

    Уберите методы Activate и Select, - макрос будет работать быстрее. Например, вот так:
    sh2.Shapes.Item(1).Copy
    sh1.Pictures.Paste

    ЗЫ, в подобных задачах полезно пользоваться отладчиком: View -> Locals Window
    Ответ написан
    2 комментария
  • Как увеличить шрифт подписи данных в диаграмме c# Excel?

    honor8
    @honor8
    Принципы быстродействия VBA в описании
    xlApp.ActiveChart.SeriesCollection(1).DataLabels.Font.Size = 12;
    Ответ написан
    Комментировать