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

Вопрос 1

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

Вопрос 2

Для подсчета ячеек и значений в них используются функции СЧЁТ и СЧЁТЗ. Урок по ним был тут.

Вопрос 3

Используйте функцию СРЗНАЧЕСЛИМН. В отдельных ячейках укажите дату с которой считать и дату по которую считать. И задайте условие в функции. Про нее мы рассказывали тут. Как записать условие с датами - смотрите на нашем канале на YouTube (в примере функция СУММЕСЛИ, но суть работы с условиями одинаковая).

Вопрос 4-5

Способы импорта данных из Интернета мы показывали в этой статье. Что касается обновления, то его можно запускать макросом при изменении какой-то ячейки. На VBA обновление запускается, например, командой RefreshAll. Как привязать запуск к событию изменения ячейки - смотрите тут (вопрос 14)

Вопрос 6-7

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

Вопрос 8

Нужно будет добавить на диаграмму дополнительно 6 отрезков (по одному на вертикальную и горизонтальную ось для каждой точки). Для начала нужно рассчитать эти точки. На скриншоте пример для нелинейной функции y=x2. Для любой другой будет аналогично (только вместо =СТЕПЕНЬ(A3;2) нужно будет указать формулу, описывающую Вашу функцию).

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

Теперь построим основной график (если он еще не построен) и добавим на него новые ряды. Ряд для пересечения с вертикальной осью строится так: кликаем на диаграмме - выбираем "Конструктор" - "Выбрать данные" - "Добавить". Задаем имя ряда, указываем точки X (первая и вторая строка мини-блока на скриншоте), указываем точки Y (первая и третья строка мини-блока на скриншоте).

Аналогично строите отрезок для пересечения с горизонтальной осью. Повторяете 3 раза (по количеству точек). Получится следующее:

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

Вопрос 9

Первый вариант.

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

=СУММ(СУММ(ДВССЫЛ(B2:B10)))

где B2:B10 диапазон значений в графике. Формула массива вводится нажатием Ctrl+Shift+Enter.

Еще вариант. Вбивайте текст, как и раньше. Затем функцией СЧЁТЕСЛИ (урок по аналогичной функции - тут) посчитайте, сколько раз за месяц встретился такой текст. А уже это количество умножайте на нужное число. Получите сумму за месяц.

Вопрос 10

Нужно добавить небольшое условие проверки включения переключателя перед пересчетом. Просто замените в коде макроса строчку:

Activesheet.Calculate

на строчку

If Range("C3") = "Да" Then ActiveSheet.Calculate

где С3 - ячейка с переключателем "Да/Нет".

Вопрос 11

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

Sub OtherBook()
    Dim nWb As Workbook
    Set nWb = Application.Workbooks.Add
    ThisWorkbook.Activate
    Selection.Copy nWb.Worksheets(1).Cells(1, 1)
    nWb.Activate
End Sub

Вопрос 12

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

Вопрос 13

Нужно просто отключить сетку листа. Выберите вкладку "Вид" и снимите галочку с сетки.

Когда устанете от скучных формул, цифр и расчетов - окунитесь в атмосферу творчества на нашем канале с качественным и красивым артом

Вопрос 14

Скорее всего дело в региональных настройках систем или локализациях Excel. На разных компьютерах настройки разные, поэтому файл созданный на одном некорректно работает на другом. Нужно привести настройки к единому виду, обратитесь к своему IT-специалисту.

Вопрос 15

Решить данную проблему можно только с помощью VBA, других способов нет.

Вопрос 16

В вопросе 15 вот этого выпуска ответов давали похожее решение. Можно взять тот макрос за основу и слегка переделать. Если нужно конкретное готовое решение под Вашу задачу - обращайтесь. Готовы помочь в частном порядке.

Вопрос 17

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

Если нужно ко множеству ячеек прибавить одно и то же число, то подойдет специальная вставка. Введите число в пустую ячейку, скопируйте, выделите ячейки, к которым его нужно добавить, нажмите правую кнопку мыши, выберите "Специальная вставка", отметьте пункт "Сложить" и нажмите ОК.

Вопрос 18

Скопируйте ссылку на пост с нужным тегом. Как копировать ссылку - показано на скриншоте ниже. Затем вставьте ее в нужный чат, файл или документ. Теперь всегда можно будет перейти по ссылке на этот пост и по тегу найти все уроки. Инструкция к каналу - тут.

Вопрос 19

Объединить очень просто. Первая формула у вас в ячейке F3. Значит нужно во второй везде вместо ячейки F3 поставить первую формулу (обязательно целиком, в скобках и без знака равно). Чтобы было проще, можно укоротить формулу. Конструкцию

=ЕСЛИ(B3-A3-12<0;0;B3-A3-12)

можно заменить на

=МАКС(0;B3-A3-12).

Конструкцию

=ЕСЛИ((F3*C3)*0,5%>=(C3*17%)*50%;(C3*17%)*50%;F3*C3*0,5%)

можно заменить на

=МИН((F3*C3)*0,5%;(C3*17%)*50%)

Ну а теперь заменим во второй формуле F3 на первую формулу. Итоговая объединенная формула такая:

=МИН(((МАКС(0;B3-A3-12))*C3)*0,5%;(C3*17%)*50%)

Вопрос 20

Это работает следующим образом. Когда в диапазоне есть скрытые или сгруппированные ячейки, то нажатие Ctrl+C копирует их. Если эти ячейки скрыты с помощью автофильтра, то они не копируются. Способ копирования по умолчанию не изменить никак.

Чтобы не копировать скрытое, нужно перед нажатием Ctrl+C выделять только видимые ячейки в диапазоне. Как копировать только видимые показывали на канале и на YouTube.

Вопрос 21

Формула примерно такая:

=И(ОСТАТ(ЗНАЧЕН(ЛЕВСИМВ(A1;ПОИСК("/";A1)-1));1)=0;ОСТАТ(ЗНАЧЕН(ПРАВСИМВ(A1;ДЛСТР(A1)-ПОИСК("/";A1)));1)=0)

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

Вопрос 22

Сделайте ниже основной вот такую таблицу и пропишите в ней формулы, как показано на скриншоте ниже:

В ячейку A20 вводите нужное число и будет рассчитан интервал повторений.

Вопрос 23

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

Вопрос 24

Нет, такую диаграмму штатными средствами Excel 2007 построить не получится. Подобный тип диаграмм появился только в Excel 2016 (называется диаграмма "Дерево"). Пожалуй, единственный вариант - создание макроса, который будет рисовать объекты-прямоугольники, либо объединять ячейки. Задача достаточно трудоемкая и проще перейти на новую версию Office. Но если нужно - готовы помочь в разработке.

Вопрос 25

Вы были на правильном пути, просто недокрутили формулу. Функцию ПОДСТАВИТЬ просто нужно применить 3 раза, каждый раз подкладывая в первый аргумент результат предыдущей итерации. Например:

=ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(E9;B9;"");C9;"");A9;"")

В результате получите то, что нужно в примере.

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

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

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