Ответы 07/11/2017 (Excel Everyday)

Вопрос 1

Это связано с тем, что у Вас англоязычная версия Excel (а значит все функции и все аргументы нужно писать на английском). В том месте, где для функции ТЕКСТ (TEXT) используется аргумент "ММ.ГГ" или "ДД.М" в Вашей версии нужно использовать "MM.YY" и "DD.M". Аналогично, для функции ЯЧЕЙКА (CELL) вместо аргумента "формат" нужно прописать "format".

Вопрос 2

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

Вопрос 3

Стандартные фильтры такого не умеют. Придется использовать комбинацию расширенного фильтра (обязательно прочтите этот урок, чтобы научиться им правильно пользоваться) и небольшого макроса. На листе с основной таблицей справа от нее создайте 2 мини-таблицы с условиями фильтрации, как на рисунке ниже.

После чего, добавьте в модуль листа (правая кнопка мыши на ярлыке листа - Просмотреть код) код следующего макроса:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = 0

Dim sh1, sh2, sh3 As Worksheet

Set sh1 = Sheets("Общий") 'указываете имя листа с общей таблицей

Set sh2 = Sheets("Фильтр1") 'указываете имя листа со второй таблицей

Set sh3 = Sheets("Фильтр2") 'указываете имя листа с третьей таблицей

If Not Intersect(Target, Range("H2:L1000")) Is Nothing Then 'указываете диапазон изменяемых ячеек

    sh2.Activate

    sh2.Range("A1").CurrentRegion.Clear

    sh1.Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=sh1.Range("O1").CurrentRegion, CopyToRange:=sh2.Range("A1"), Unique:=False

    sh3.Activate

    sh3.Range("A1").CurrentRegion.Clear

    sh1.Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=sh1.Range("U1").CurrentRegion, CopyToRange:=sh3.Range("A1"), Unique:=False

End If

sh1.Activate

Application.ScreenUpdating = 1

End Sub

При необходимости поправьте диапазоны и имена листов в коде на свои:

Range("H2:L1000") - диапазон изменяемых ячеек в основной таблице

Range("O1") - левая верхняя ячейка таблицы условий 1

Range("U1") - левая верхняя ячейка таблицы условий 2

Копироваться будут все столбцы, но ненужные можно просто заранее скрыть. Разумеется, файл нужно сохранить в формате xlsm или xlsb, чтобы макрос был доступен.

Вопрос 4

Все довольно просто. Вводите на листе значения для построения графика, выделяете, далее выбираете "Вставка" - "Диаграмма" - "Точечная с гладкими кривыми". После того, как график будет построен, выделяете нужную ось (X или Y), нажимаете Ctrl+1 или "правая кнопка мыши - Формат оси", в появившейся панели выбираете "Параметры оси", находите галочку "Логарифмическая шкала" и устанавливаете её (там же можно задать основание логарифма, по умолчанию - 10).

Вопрос 5

Вам нужно настроить правило условного форматирования. Выделяете ячейку - Главная - Условное форматирование - Правила выделения ячеек - Текст содержит - и настраиваете нужный формат. Затем повторяете для другого варианта текста.

Примеры разных вариантов условного форматирования давали часто, например здесь, здесь и здесь. Изучите и всё станет понятно.

Вопрос 6

На этот вопрос ответить сходу не получится, слишком обширная тема. Подобные запросы пишутся на языке программирования "M". Мы планируем запустить канал по продвинутым инструментам аналитики, где будем разбирать подобные вещи. Так что следите за новостями.

Вопрос 7

Конкретно по Вашей таблице можно настроить шрифты/заливки/рамки на свой вкус (урок был тут). По желанию, можете использовать условное форматирование для числовых данных (выбирайте: раз, два, три). Что касается диаграммы и визуализации в целом, то это всегда зависит от того, что именно вы хотите показать (что отобразить на диаграмме, на чём сделать акцент, на какую тенденцию обратить внимание и т.д.). Под задачу подбираются средства. Задачи разные и средств тоже немало. Про диаграммы можете почитать на нашем канале (например, тут, тут или тут).

Вопрос 8

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

Sub ShowNames()

For Each Nm In ActiveWorkbook.Names

  If Nm.Visible = False Then

    Nm.Visible = True

  End If

Next Nm

End Sub

Если нужно удалять любые скрытые имена, то строку Nm.Visible = True

замените на Nm.Delete

Вопрос 9

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

Вопрос 10

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

Вопрос 11

Возможно, быстрее получится, если сделать один столбец полностью, скопировать формулу в остальные 24, а затем выделить по очереди каждый из них и с помощью замены (ctrl+H) исправить имя файла на нужное. Заменяемое имя будет везде одно, а вставляемое для каждого столбца свое. Займет пару-тройку минут, учитывая, что структура всех файлов одинакова. Если у Вас много монотонной работы, то можем помочь с ее выполнением, либо автоматизировать ее для Вас.

Вопрос 12

Да. Перемещаете курсор в любую ячейку отфильтрованного диапазона и нажимаете "Главная" - "Сортировка и фильтр" - "Очистить" (или "Данные" - "Сортировка и фильтр" - "Очистить")

Вопрос 13

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

Sub ReValues()

If Selection.Count <> 2 Then Exit Sub

For Each cell In Selection

  n = n + 1

  If n = 1 Then

    Val1 = cell.Value

    Adr1 = cell.Address

  Else

    Val2 = cell.value

    Adr2 = cell.Address

  End If

Next

Range(Adr1) = Val2

Range(Adr2) = Val1

End Sub

Вопрос 14

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

Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = 0

Application.EnableEvents = 0

Dim sh1, sh2 As Worksheet, Adr As String

Adr = ActiveCell.Address

Set sh1 = Sheets("Вопрос 15") 'указываете имя листа, куда вводятся данные

Set sh2 = Sheets("КопияФорматы") 'указываете имя листа с копией форматов

If Not Intersect(Target, Range("A:X")) Is Nothing Then 'указываете диапазон изменяемых ячеек

  sh2.Range("A:X").Copy

  sh1.Range("A:X").PasteSpecial Paste:=xlPasteFormats

  sh1.Range("A:X").PasteSpecial Paste:=xlPasteValidation

End If

Application.CutCopyMode = 0

Range(Adr).Select

Application.ScreenUpdating = 1

Application.EnableEvents = 1

End Sub

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

Вопрос 15

Лучший вариант - разделить имена и фамилии на отдельные столбцы (как это сделать - смотрите тут и тут), а потом соединить Фамилию и Имя в один (урок здесь). А уже в этом столбце найти дубликаты очень просто (как здесь, например). Главное, правильно сформировать столбец с Фамилией и Именем (так как Иванов Иван и Иван Иванов для Excel - два разных человека).

Вопрос 16

Функция =ABS() возвращает модуль указанного числа.

Вопрос 17

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

У вас есть даты А1, А2 и B1,B2. Сейчас вы находите разницу между ними, преобразуете с помощью РАЗНДАТ и хотите потом сложить. Попробуйте поменять порядок шагов. Найдите Х=(А2-А1)+(В2-В1). Это общая разница в днях между двумя парами дат. Затем в вашей формуле в РАЗНДАТ подставьте первым аргументом 0, а вторым аргументом - Х. Получите сумму разностей в нужном формате.

Вопрос 18

Нужно поменять стиль ссылок. Файл - Параметры - Формулы - Снять галочку "Стиль ссылок R1C1" - Нажать ОК

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