Ответы 12/04/2018 (Excel Everyday)

Вопрос 1

Лучший вариант - формула массива. Нужно будет использовать функцию СМЕЩ, чтобы возвращать только последние N строк, и функцию СЧЁТЗ, чтобы правильно их определять. Например, на гифке ниже прописана формула для отображения последних 5 строк. Чтобы ее правильно ввести, нужно выделить весь диапазон, в который будет выведен результат, и ввести формулу как формулу массива (нажатием Ctrl+Shift+Enter). Для изменения числа итоговых строк в формуле нужно заменить число 5 (во втором и четвертом аргументах) на необходимое число. Про функцию СМЕЩ читайте в этой статье (в ней, кстати, разбирался подобный приём).

=СМЕЩ($A$1;СЧЁТЗ(A:A)-5;0;5;2)

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

Вопрос 2

Просто поделите одну ячейку на другую (A1/B1). К ячейке с результатом примените формат "Процентный".

Вопрос 3

Посмотрели Ваш файл и проблемы с фильтрацией не увидели. Возможно, Вы применяете фильтр на несколько столбцов, а потом не все их снимаете? Полное снятие фильтра лучше всего производить командой "Очистить".

Кроме того, настоятельно рекомендуем не использовать в шапках таблиц объединенные ячейки (это может привести к неверному определению диапазона фильтрации). Горизонтальное объединение ячеек можно успешно заменить на выравнивание. Урок был тут.

Вопрос 4

Все зависит от того, как именно организована база. Если есть столбцы с годом, месяцем, количеством ТО и названием точек, то мы бы сделали так.

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

2) Добавили бы столбец с нарастающим итогом по количеству ТО для каждой точки (пусть это будет столбец E). Например, если названия точек в столбце C, а количество ТО - в D, то формула такая:

=СУММЕСЛИ($C$2:C2;C2;$D$2:D2)

3) Добавим столбец, в котором будет выводиться слово "ТОРТ" в том месяце, где точка перешла рубеж в очередные 100 млн ТО. Формула такая:

=ЕСЛИ(C2=C1;ЕСЛИ(ЦЕЛОЕ(E2/100000000)<>ЦЕЛОЕ(E1/100000000);"ТОРТ";"");"")

Эта формула проверяет, находятся ли в предыдущей строке данные по этой же точке. Если да, то формула проверяет сколько сотен миллионов ТО сделано в текущей строке и в предыдущей. Если новая сотня разменялась - выводится слово "ТОРТ".

Можете дополнить это всё условным форматированием (выделение целой строки показывали тут).

Вопрос 5

Если Вы про статистический метод Монте-Карло, то нужно воспользоваться соответствующими функциями Excel из категории "Статистические". Про каждую из них можно подробно почитать в библиотеке функций (как ею пользоваться показывали в ТГ и на YouTube).

При условии знания метода - ничего сложного в его реализации в Excel нет. В ином случае стоит обратиться на специализированный статистический форум.

Если Вы понимаете метод, но в Excel его перенести не получается - пишите. Готовы помочь в частном порядке (в рамках рубрики тема слишком объемная и скорее про статистику, чем про Excel)

Вопрос 6

В модуль листа нужно будет добавить следующий код:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1")) Is Nothing Then
        Range("B1").Select
        Application.SendKeys "%{DOWN}"
    End If
End Sub

A1 - ячейка с первым выпадающим списком. B1 - ячейка с автораскрывающимся списком.

Вопрос 7

Сначала включаете в файле гугл-таблицы доступ по ссылке. Копируете получившуюся ссылку. Переходите в Excel, выбираете "Данные" - "Из интернета". В поле URL вставляете скопированную ссылку. Часть ссылки /edit?usp=sharing меняете на /export

Жмете ОК. Откроется список листов файла. Выбираете нужный. Кликаете "Правка". В редакторе запросов фильтруете колонки как Вам нужно (точно также, как в обычной таблице). Ну а затем "Закрыть и загрузить в..."

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

Вопрос 8

Вот такой макрос добавляет в книгу новый лист со списком ячеек и их числовых форматов для того листа, с которого он был запущен.

Sub СписокФорматов()
Dim wsh As Worksheet
Dim nwsh As Worksheet
Dim cll As Range
Dim n As Long

Set wsh = ThisWorkbook.ActiveSheet
Set nwsh = ThisWorkbook.Worksheets.Add
nwsh.Name = "Форматы листа " & Left(wsh.Name, 16)
nwsh.Cells(1, 1) = "Адрес ячейки"
nwsh.Cells(1, 2) = "Формат ячейки"

n = 2
For Each cll In wsh.UsedRange
    nwsh.Cells(n, 1).Value = cll.AddressLocal
    nwsh.Cells(n, 2).Value = cll.NumberFormatLocal
    n = n + 1
Next cll

nwsh.Activate

End Sub

В общем, основная идея - использование свойства NumberFormatLocal. Можем помочь сделать макрос конкретно под Вашу задачу в частном порядке.

Вопрос 9

Вариантов - масса. Разберем пару.

Первый основан на функции ВРЕМЯ и функциях ЧАС, МИНУТЫ и СЕКУНДЫ. Позволяет работать со значениями времени в привычном нам представлении. Во всех примерах исходное время находится в ячейке A2.

Отбрасывание секунд:

=ВРЕМЯ(ЧАС(A2);МИНУТЫ(A2);0)

Округление:

=ВРЕМЯ(ЧАС(A2);МИНУТЫ(A2);ЕСЛИ(СЕКУНДЫ(A2)>=30;60;0))

Второй основан на том факте, что время - это дробная часть единицы (целых суток). Одна минута - это число 1/1440 (24 часа * 60 минут = 1440 минут). Используя функции округления и указывая в качестве заданной точности это число, можно получить нужный результат.

Отбрасывание секунд:

=ОКРВНИЗ(A2;1/1440)

Округление:

=ОКРУГЛТ(A2;1/1440)

Вопрос 10

Вот такая пользовательская функция сделает эту работу.

Function ExtractNum(cll As Range, delim As String)

Dim strAll As String
Dim strSym As String
Dim nextSym As String
Dim n As Long

strAll = cll.Value
If Len(strAll) = 0 Then Exit Function

n = 1
Do While n <> Len(strAll)
    strSym = Mid(strAll, n, 1)
    nextSym = Mid(strAll, n + 1, 1)
    If IsNumeric(strSym) Then
    ExtractNum = ExtractNum + strSym
        If Not IsNumeric(nextSym) Then ExtractNum = ExtractNum + delim
    End If
    n = n + 1
Loop

If IsNumeric(Right(strAll, 1)) Then ExtractNum = ExtractNum + Right(strAll, 1)
If delim = Right(ExtractNum, Len(delim)) Then ExtractNum = Left(ExtractNum, Len(ExtractNum) - Len(delim))

End Function

У нее два аргумента: первый - ячейка с текстом, посреди которого разбросаны цифры, а второй - разделитель (можно указать любой символ или набор символов). Куда копировать код - смотрите здесь. Там же и функция для извлечения всех цифр без разделителя.

Пример работы функции с разделителем:

Вопрос 11

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

В модуль книги добавьте код:

Private Sub Workbook_Open()

Dim SourceWbk As Workbook
Set SourceWbk = Application.Workbooks.Open("V:\SALES\REPORTS\Pick-up new\2017\pick-up\Апрель 2017\Pick Up " + Format(Now(), "dd.mm.yyyy") + ".xlsx")
Application.DisplayAlerts = False

Dim trgtRng As Range, alRng As Range, frm As String, patt As String, res As String
Dim myRegExp As Object
Set myRegExp = CreateObject("VBScript.RegExp")
patt = "\[(.*)\]"

myRegExp.Pattern = patt

'здесь укажите диапазоны, в которых нужно менять формулы
Set trgtRng = Me.Worksheets("ВашЛист").Range("B4:B6,C4:C10")

For Each alRng In trgtRng
    res = myRegExp.Replace(alRng.Formula, "[Pick Up " + Format(Now, "dd.mm.yyyy") + ".xlsx]")
    alRng.Formula = res
Next alRng

Me.Save

SourceWbk.Close
Application.DisplayAlerts = True

End Sub

В строке "Set SourceWbk = " указан путь к папке с файлами, в которых меняются названия в зависимости от даты + первая часть имени (до самой даты).

В строке "Set trgtRng =" нужно указывать адреса диапазонов, в которых меняются формулы. В нашем примере это диапазоны B4:B6 и C4:C10 листа "ВашЛист". Диапазоны указывайте через запятую, изменив имя листа на Ваше. Если диапазоны на разных листа, то воспользуйтесь функцией Union. Пример:

= Union(Me.Worksheets("Лист1").Range("A1:A10,B3:B5"), Me.Worksheets("Лист2").Range("C1:C10,D3:D5"))

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

Вопрос 12

Вот такая формула посчитает:

=СУММЕСЛИМН($B$2:$B$31;$A$2:$A$31;">=01.04.2018";$A$2:$A$31;"<="&СЕГОДНЯ())

B2:B31 - диапазон значений, A2:A31 - диапазон дат. Разумеется, Вам нужно поменять на свои.

Вопрос 13

Лучший из возможных вариантов - скачать официальное приложение Microsoft Excel в App Store. Но имейте в виду, что функционал мобильных версий Excel (как и онлайн версии) очень сильно урезан. Например, те же макросы можно запускать только на компьютерах. Поэтому правильнее всего открывать файлы на ПК или ноутбуках. Открывая на телефоне, Вы, скорее всего, не увидите в файле большую часть того, ради чего мы его Вам демонстрируем.

Вопрос 14

Зачем Вам отдельная функция, если это решается простой математикой? Если значения от 1 до 6, то сумма противоположных пар всегда будет 7 (как на игральной кости, сумма противоположных сторон всегда 7). Получается, что прекрасно справится формула:

=7-А1

где А1 - ячейка с вашим исходным числом.

Для 1 она вернет 6, для 6 - вернет 1, для 2 вернет 5 и т.д.

Вопрос 15

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

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

Вопрос 16

Такой настройки в Excel нет. Единственный вариант - на событие пересчета листа вешать код, который будет либо добавлять/удалять ряды с диаграммы по мере надобности, либо фильтровать их (фильтрация актуальна начиная с Excel 2013).

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

Private Sub Worksheet_Calculate()
    Dim chrt As ChartObject
    Set chrt = ActiveSheet.ChartObjects("Диаграмма 2")
    If ActiveSheet.Range("J2").Value = "" Then
        chrt.Chart.ChartGroups(1).FullCategoryCollection(1).IsFiltered = True
    Else
        chrt.Chart.ChartGroups(1).FullCategoryCollection(1).IsFiltered = False
    End If
    If Range("J3").Value = "" Then
        chrt.Chart.ChartGroups(1).FullCategoryCollection(2).IsFiltered = True
    Else
        chrt.Chart.ChartGroups(1).FullCategoryCollection(2).IsFiltered = False
    End If
End Sub

Работает вот так:

Вопрос 17

Объединить можно) Только что именно Вы понимаете под объединением? Без файла-примера или хотя бы разъяснений и уточнений мы ничего не можем подсказать.

Если просто собрать две таблицы в одну, подложив одну под другую - то это простой копипаст или небольшой макрос.

Если под объединением понимается получение из ячейки со значением 15 и со значением "яблок" итоговой ячейки "15 яблок", то имейте в виду, что итогом будет текст (нельзя получить в ячейке одновременно и число, и текст).

Если таблицы разные по количеству столбцов, то это совсем другая история.

В общем, ждем уточнений (это, кстати, актуально для большинства обращающихся с вопросами).

Вопрос 18

Вам нужно создать надстройку. Файл с макросами нужно сохранить на сетевой диск в формате "Надстройка Excel" (.xlam) или "Надстройка Excel 97-2003" (.xla)

Далее каждый пользователь должен один раз проделать следующее:

"Файл" - "Параметры" - "Надстройки" - "Надстройки Excel" - "Перейти".

Далее "Обзор" - выбирается нужная надстройка в сетевой папке - "ОК"

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

После этого все макросы надстройки будут доступны на компьютере пользователя (она всегда будет запускаться вместе с Excel, как личная книга макросов, только еще более скрытно)

Вопрос 19

Можно воспользоваться обычной заменой. Выделяете весь диапазон, жмете Ctrl+H, в поле найти вводите знак, в поле заменить - пусто. Жмете "Заменить все".

Еще вариант - в соседний столбец пишете формулу:

=ЗНАЧЕН(ПОДСТАВИТЬ(C2;" ₽";""))

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

Поддержать наш проект и его дальнейшее развитие можно вот здесь.

Ваши вопросы по статье можете задавать через нашего бота обратной связи в Telegram: @ExEvFeedbackBot

С уважением, команда tDots.ru