Основные понятия (26) Сводные таблицы и анализ данных (10) Графики и диаграммы (5) Работа с VB проектом (12) Power BI и Power Query (22)

Условное форматирование (5) Списки и диапазоны (5) Макросы(VBA процедуры) (68) Разное (43) Баги и глюки Excel (4)

---

Хитрости

ОСНОВНЫЕ ПОНЯТИЯ

• Как Excel воспринимает данные?
  Почему Excel меняет число 1.2 на 1.фев? Как с этим бороться и что еще необходимо знать при работе с данными в Excel
• Что такое формула?
  В статье разъясняется как само понятие формулы и её аргументов так и способы создания формулы и записи её в ячейки
• Что такое формула массива
  Узнайте как одной формулой перемножить диапазоны и разберите на примерах как это работает
• Формулы перестали считать - как исправить?
  При работе в Excel опять перестали считать формулы? Узнайте почему это происходит и как исправить
• Как просмотреть этапы вычисления формул
  Опять получили файл от коллеги с непонятной мега-формулой? Научитесь читать и понимать сложные формулы!
• Что такое гиперссылка?
  После изучения статьи вы узнаете как из ячейки Excel перейти на указанный сайт, открыть файл или папку
• Почему нет листов?
  Что делать, если вы открыли файл(или выгрузили файл из 1С), а листов в нем нет?
• Почему столбцы стали цифрами или как сменить стиль ссылок
  Вместо A,B,C столбцы теперь 1,2,3? Почему так получилось, как исправить и зачем это нужно
• Что такое макрос и где его искать?
  Часто приходится проделывать одну и ту же операцию в Excel? Научитесь создавать макросы и делайте все нажатием одной кнопки!
• Использование относительных ссылок в макросах
  Как правильно записать макрос, чтобы он работал независимо от количества строк в таблице и определял сам последнюю ячейку?
• Как создать кнопку для вызова макроса на листе
  Макрос уже есть, а проблема его запуска осталась? Научитесь создавать свои кнопки для вызова макроса
• Почему не работает макрос?
  Вам прислали файл с кодами и макросами, но он отказывается работать? Поверьте все проще, чем кажется
• Что такое модуль? Какие бывают модули?
  Узнайте где можно хранить свои коды и особенности каждого модуля
• Что такое функция пользователя(UDF)? update!
  Научитесь создавать свои собственные функции, которых в Excel еще нет
• Как добавить описание(подсказку) к UDF и аргументам UDF new!
  Как добавить понятное описание к собственной функции пользователя и её аргументам? Какие есть варианты и какие у них нюансы
• Сбивается путь к UDF из надстройки
  На нескольких ПК используется одна и та же пользовательская надстройка с одними и теми же функциями UDF, но при пересылке файлов с этими UDF ссылки на них слетают и всегда ссылаются именно на ВАШУ надстройку на ВАШЕМ ПК, а не на ПК того, кому её отправили. Как исправить?
• Как сделать гиперссылку на процедуру?
  В своих примерах я использую прием: по нажатии кнопки на листе вас перебрасывает сразу в код VBA. В статье приведено пара способов как это сделать самостоятельно
• Что такое переменная и как правильно её объявить?
  Dim, Public, Static...As...Объявление переменной - не такое уж простое дело. А вы уверены, что правильно это делаете?
• Variable not defined или что такое Option Explicit и зачем оно нужно?
  А вы уже сталкивались с этой ошибкой? Если нет - узнайте чего в ней полезного и почему её появление вам только на пользу
• Работа с модулями классов
  Научитесь при помощи модулей классов и пяти строк кода создавать одинаковое событие для любого количества текстбоксов
• Отлов ошибок и отладка кода VBA
  Если вы начали программировать на VBA и до сих пор еще не знаете, что такое пошаговая отладка кода и что делать при появлении ошибок - эта статья для вас!
• Как запустить файл с включенными макросами?
  Сделали файл, но работать он должен исключительно с участием макросов? Узнайте как заставить пользователя открыть файл только с разрешенными макросами
• Как создать свою надстройку?
  Уже создали столько макросов, что кнопок на листе на все не хватает? Создайте надстройку - сделайте коды доступными из любой книги
• Надстройка не запускается в Excel
  Скачали надстройку с сайта, открываете или подключаете - а она не работает? Узнайте почему это может происходить и как заставить надстройку работать.
• Топ 10 распространенных ошибок начинающих пользователей Excel
  Все мы совершаем ошибки. Но есть такие ошибки, которые совершают чаще других с завидным постоянством. А вы уверены, что делаете правильно?
• Сочетания клавиш Ctrl+Shift+4 не вставляют время в Excel new!
  Какие основные сочетания клавиш используются в Excel и почему некоторые из них перестают работать без всяких причин? Например, почему вчера сочетание Ctrl+Shift+4 работало, а сегодня внезапно перестало?

СПИСКИ И ДИАПАЗОНЫ

• Списки Автозаполнения
  Часто используете однотипные списки для заполнения шапки таблиц? Научитесь делать это в два клика!
• Выпадающие списки
  В Excel есть отличная возможность создания выбора в одной ячейке нужного значения из списка
• Связанные выпадающие списки
  Как в одной ячейке в списке выбрать Отдел и при этом в другой ячейке сформировался список сотрудников именно выбранного отдела?
• Именованные диапазоны
  Научитесь обращаться к ячейкам не по их адресу, а так, как вам удобно - создавайте свои собственные имена!
• Динамические именованные диапазоны
  Часто используете выпадающие списки, но количество значений меняется и надоело постоянно менять ссылки? Научитесь создавать автоматически расширяемые списки

СВОДНЫЕ ТАБЛИЦЫ И АНАЛИЗ ДАННЫХ

• Общие сведения о сводных таблицах
  Уже слышали про сводные, но все еще не понимаете что это и зачем? Тогда вам сюда!
• Сводная таблица из нескольких листов
  Часто приходится анализировать данные, расположенные в нескольких листах? Такая сводная поможет сделать это проще и быстрее
• Группировка данных в сводной таблице
  Научитесь располагать данные в своих сводных таблицах по месяцам, годам, кварталам и неделям в два клика без создания доп.столбцов в исходных данных!
• Использование вычисляемых полей и объектов в сводных таблицах
  Как создавать и применять вычисляемые поля и вычисляемые объекты в сводных таблицах Excel, для чего они нужны и какие нюансы использования
• Сводная из базы данных Access
  У вас есть миллионы записей или огромная база данных и все это дело необходимо проанализировать и сделать отчеты? Научитесь делать это за 5 минут!
• Автообновляемая сводная таблица
  Постоянно меняете и добавляете данные, на основании которых строится сводная таблица и приходится каждый раз жать Обновить? Узнайте как обновлять сводную таблицу автоматически сразу после внесения изменений
• Как перейти к редактированию исходных данных прямо из сводной таблицы? new!
  Как узнать из каких конкретно данных сводная собрала значение? И не просто узнать - но еще и посмотреть эти данные непосредственно в источнике и сразу отредактировать с обновлением сводной таблицы?
• Как быстро подобрать оптимальный вариант решения
  Как рассчитать наиболее выгодный вариант вложения денежных средств, учитывая различные сроки, суммы и лимиты? Очень просто - Поиск решения решит все за Вас!
• Прогноз продаж в Excel с учетом сезонности
  Как с помощью Excel рассчитать прогноз продаж на будущие периоды с учетом сезонности и построить на основании этих данных красивый график
• Модель хеджирования валютных рисков в Excel
  Как с помощью несложной модели Excel оценить размер рисков, основанных на курсах валют и понять, необходимо ли применять хеджирование или нет

ГРАФИКИ

• Общие сведения о диаграммах и графиках
  Графики, диаграммы, визуализация...На самом деле все не так сложно - графики в Excel может создать любой за 2 минуты!
• Два в одном - как сделать?
  Сделайте свои отчеты и презентации ярче, нагляднее и живее, совместив разные виды диаграмм в одной
• Динамическая диаграмма
  Построили диаграмму, но данные в отчете постоянно пополняются и приходится все время заново указывать диаграмме данные? Создайте диаграмму, которая будет дополняться автоматически
• Ступенчатый график в Excel
  График, по виду напоминающий ступени лестницы до сих пор не входит в стандартный набор диаграмм Excel. Но его можно создать, применив небольшие хитрости
• Интерактивный график
  Пошаговая инструкция и пример того как сделать, чтобы данные на графике изменялись автоматически в зависимости от выбранного значения, не изменяя руками данные графика

УСЛОВНОЕ ФОРМАТИРОВАНИЕ

• Основные понятия условного форматирования и как его создать
  Научитесь закрашивать цвет ячеек автоматически на основании их значений, а так же создавать мини-графики сравнения числовых данных
• Сборник формул для условного форматирования
  Самый сложный и самый мощный метод создания условий для условного форматирования - формулы. Посмотрите некоторые из них и применяйте!
• Выделение текущей даты
  Как автоматически без кодов(макросов) сделать автоматическое выделение цветом текущей даты
• Выделение различий в ячейках по условию
  Как определить, какие ячейки разных столбцов одной строки различаются
• Выделение строк цветом через одну
  Разделите строки в таблице "тельняшкой" или "зеброй"

МАКРОСЫ (VBA ПРОЦЕДУРЫ)

• Как обратиться к диапазону из VBA
  Уже знаете как обратиться к диапазону в любом листе или книге не активируя их(Select, Activate)? А особенности записи/считывания данных диапазона внутри разных модулей? Как считать данные из всех листов книги, не активируя при этом каждый лист?
• Select и Activate - зачем нужны и нужны ли?
  Научитесь правильно чистить код после макрорекордера, делая его красивее, понятнее и быстрее
• Как определить последнюю ячейку на листе через VBA?
  Необходимо дополнить таблицу, но не знаете где она заканчивается? В статье приведено несколько способов определить последнюю строку, описаны принципы, плюсы и минусы каждого способа и практические коды для применения
• Как определить первую заполненную ячейку на листе?
  Таблица постоянно меняет свое расположение? Узнайте, как найти её начало
• Как ускорить и оптимизировать код VBA
  Уже научились писать коды, но они выполняются слишком медленно? В большинстве случаев их можно сделать в десятки, а то и сотни раз быстрее! Узнайте, можно ли ускорить ваш код
• Работа с диалогами
  Показать сообщение пользователю, запросить номер строки, дату или другую информацию для использования в коде - одна из важнейших функций взаимодействия с пользователем и работу с диалогами знать необходимо
• Диалоговое окно выбора файлов/папки
  Необходимо дать пользователю возможность выбрать самому файлы для обработки, но не знаете что применить? Научитесь выводить запрос на выбор файлов с их последующей обработкой
• Кто вызвал функцию или процедуру?
  Как на одну кнопку повесить два макроса? Как определить нажатием какой кнопки был вызван код? Или внутри функции пользователя определить ячейку, в которой эта функция записана? В статье показаны варианты решения всех этих проблем
• Отобразить процесс выполнения кода
  Если код выполняется долго, то очень желательно знать - сколько еще ждать. В статье приведено несколько способов как это сделать наглядно и красиво
• Как запретить сообщения?
  Как в кодах VBA избежать надоедливых сообщений при удалении листов или сохранении книг в режиме совместимости?
• Разделение строк с разным содержимым "зеброй"
  В отличии от простого чередования четных и нечетных строк статья поможет визуально разделить цветами строки, значения которых отличаются от вышестоящих значений
• Выделение сделанных изменений
  Готовый код, помечающий цветом ячейки на листе, в которых были сделаны изменения
• Ведение журнала сделанных в книге изменений
  При работе с одной книгой сразу нескольких пользователей самая частая проблема - найти того, кто внес нежелательные изменения. Статья поможет следить за всеми изменения в файле
• Запись изменений на листе в примечания
  Еще один вариант ведения лога изменений в ячейках - в стандартных примечаниях
• Как проверить открыта ли книга?
  Если для работы кода нужна конкретная книга и в зависимости от того, открыта она или закрыта надо делать разные действия - в статье приведены примеры кода такой проверки
• Как узнать существует ли лист в книге?
  Простая функция проверки наличия листа в указанной книге и примеры кодов как добавить лист, если его нет
• Как собрать данные с нескольких листов или книг?
  Постоянно получаете кучу однотипных файлов, которые необходимо объединить в один? В статье готовый код, с помощью которого вы сможете сделать это в пару кликов
• Как объединить несколько текстовых файлов в один?
  Простой код для объединения информации из множества текстовых файлов в один новый
• Просмотреть все файлы в папке
  Распространенная проблема - пройтись по всем файлам в отдельной папке и её подпапках и произвести с файлами какие-то действия
• Как сменить формат сразу для нескольких файлов Excel
  Как быстро изменить формат сразу для нескольких файлов Excel? Например, преобразовать 50 файлов из формата xls в формат xslm нажатием одной кнопки
• Как средствами VBA переименовать/переместить/скопировать файл
  Какие команды и методы применить в коде VBA чтобы скопировать, переименовать или переместить файл
• Как удалить папку или все файлы из папки через VBA
  В статье приводятся готовые коды с примерами, как в указанной папке удалить все файлы вместе с папкой или только по условию. Например, удалить файлы в папке, дата создания которых раньше указанной
• Как удалить книгу из самой себя
  Как удалить книгу по прошествии определенной даты кодом, расположенным в этой же книге?
• Как получить данные из закрытой книги?
  Разные способы извлечения данных из закрытых книг, их плюсы и минусы, а также функция листа для получения данных из указанного диапазона указанной книги
• Как из VBA выбрать и установить принтер для Excel
  Как в коде VBA дать возможность пользователю указать принтер для печати, не изменяя при этом принтер по умолчанию для ручной печати?
• Как оставить в ячейке только цифры или только текст?
  Как из текста "748шт." или "По всем вопросам звонить 8(111) 12121, после обеда" получить только числа или только номер телефона? Или наоборот - убрать все цифры?
• Как получить текст примечания в ячейку?
  Все основные замечания для сумм вынесли в примечания как вдруг потребовалось отобрать быстро записи с отдельным текстом в примечаниях? Это можно быстро решить при помощи доп.столбца и небольшого кода
• Как скопировать картинку из примечания?
  В прайсе накопилось много товара, фото которого хранится в примечаниях и требуется эти фото вытащить? Не проблема, код из статьи сделает это за пару секунд
• Как получить адрес гиперссылки из ячейки
  В большинстве случаев гиперссылки по умолчанию отображаются именно адресами, но как узнать адрес, если в ячейке отображается другой текст(например, Перейти)?
• Как сохранить картинки из листа Excel в картинки JPG
  Несколько готовых кодов по работе с картинками и фигурами на листах: сохранение всех картинок, сохранение с именем картинки из ячейки, сохранение выделенного диапазона и др.
• Вставить картинку в лист - по списку или выбору из ячейки new!
  Как сделать, чтобы при выборе товара в ячейке рядом появлялось его изображение, расположенное в отдельной папке с картинками? Или как подгрузить по списку в таблице к каждому товару его изображение? Готовые коды и пример реализации в виде готового файла в статье
• Как защитить лист от пользователя, но не от макроса?
  Какую защиту надо применить на листе, чтобы пользователь не мог изменять ячейки, но код VBA мог это делать без ввода пароля?
• Как оставить возможность работать с группировкой/структурой на защищенном листе?
  На защищенном листе разворачивать и сворачивать строки и столбцы группировки нельзя. Маленькая хитрость из статьи поможет это обойти
• Каждому пользователю свой лист/диапазон
  Как разрешить сотрудникам отдела продаж изменять одни ячейки, а сотрудникам коммерческого отдела - другие? Или пользователю Иванов видеть и редактировать только свои листы, а пользователю Петров - свои, не видя при этом данные и листы других пользователей?
• Как вставить скопированные ячейки только в видимые/отфильтрованные ячейки
  При попытке скопировать ячейки в отфильтрованный диапазон они будут вставлены игнорируя фильтр. Как обойти это ограничение?
• Сцепить много ячеек с указанным разделителем
  Как можно быстро объединить текст множества ячеек в одну с разделителем? Стандартно в версиях до 2010 это сделать проблематично
• Как сцепить несколько значений в одну ячейку по критерию? СцепитьЕсли
  Как в одну ячейку собрать через запятую фамилии всех сотрудников отдела, если все сотрудники идут вразнобой
• Сравнение текста по части предложения
  Никогда не приходилось искать совпадение двух строк текста, если слова в них перепутаны("шла маша по шоссе" и "маша по шоссе шла")? Узнайте как сравнить такие строки независимо от расположения текста
• Подбор высоты строки/ширины столбца объединенной ячейки
  Как подобрать высоту ячейки по содержимому текста, если ячейка объединена? Стандартно Excel не может автоматически выставить авторазмер по содержимому для таких ячеек
• Как перевернуть адрес
  Как из строки "143622 Московская обл Волоколамский район с.Рюховское ул.Полевая д.6" сделать "д.6 ул.Полевая с.Рюховское Волоколамский район Московская обл 143622"?
• Функция перемещения слова в строке
  Как во фразе "Ромашка ООО - производство карандашей" поменять местами слова ООО и Ромашка?
• Как из Excel обратиться к другому приложению
  Из Excel можно открыть Word и вставить в него текст из ячеек активной книги. Или "попросить" Outlook отправить письмо с адресом из ячейки. Узнайте как правильно это сделать
• Как отправить письмо из Excel?
  Подробное руководство по отправке писем из Excel, а так же готовые коды по созданию и отправке писем с вложениями с данными из ячеек множеству адресатов сразу
• Вставить в письмо подпись из Outlook через VBA
  При программном создании письма в Outlook подпись не создается, даже если это настроено в параметрах. Узнайте, как можно это исправить
• Вставить в письмо Outlook таблицу Excel с форматированием
  Как в Outlook автоматически из Excel создавать красиво оформленные письма, вложив в них целиком таблицу со всем форматированием ячеек? В статье предлагается универсальный метод, который поможет вложить в письмо любую таблицу, сохранив все форматирование
• Вставить в письмо Outlook диаграмму Excel
  В статье приводится несколько практических кодов VBA, которые помогут из Excel создать письмо Outlook, вложив в него одну или несколько диаграмм с листа. Разобраны разные методы, их плюсы и минусы
• Сохранить вложения из Outlook в указанную папку
  После отпуска накопилось много писем от клиентов с заявками в виде файлов? Отбирать и скачивать вручную долго. Узнайте, как можно быстро отобрать только нужные вложения и сохранить в указанную папку
• Как отправить письмо от другой учетной записи Outlook
  Как при создании письма в Outlook кодом VBA вместо почты отправителя по умолчанию указать другой адрес отправителя, вместо установленного по умолчанию(другую учетную запись)
• Outlook отправляет не все письма кодом VBA - почему и как исправить? new!
  Используете массовую рассылку куче адресатов через Outlook, но некоторые адресаты все равно не получают письма? В статье разъясняется почему так происходит и как этого избежать
• Как отследить событие(например выделение ячеек) в любой книге?
  Отследить изменение значения ячейки на одном конкретном листе просто - нужен код в этой самой книге. Но как сделать это из надстройки для ЛЮБОЙ открытой книги?
• Как убрать заголовок у UserForm VBA
  Иногда просто необходимо запретить пользователю закрывать форму пользователя крестиком и дать возможность закрывать её только нажатием спец.кнопки
• Как быстро заполнить/очистить элементы на форме(TextBox-ы, ComboBox-ы)
  Если на форме много текстбоксов (или других элементов), то проделать с ними одно и то же действие порождает слишком много кода. В статье написано как сделать это буквально тремя строками кода
• Собственное меню для TextBox в UserForm new!
  Как создать свое собственное меню для элемента TextBox на UserForm, если стандартно такой возможности не предусмотрели? Готовые коды создания меню на примере функций Копирования и Вставки текста
• "Обратить выделение" – выделить все ячейки, кроме выделенных
  Если выделить надо все ячейки, кроме пары ячеек - то проще выделить эти пару ячеек и использовать код из статьи
• Массовая замена слов
  Приходилось ли вам когда-нибудь искать и заменять в больших объемах данных группы слов? Если да - то не понаслышке знаете насколько это радует. Решение в этой статье поможет сделать это за пару секунд
• Как отменить действия макроса
  Стандартно после работы практически любого макроса пропадает возможность откатить сделанные кодом изменения. Но применив некоторые хитрости можно сделать и это
• Не работают/пропали меню
  Решили удалить строку или лист, но меню по правой кнопке мыши куда-то пропало? Узнайте почему и как исправить
• Отобразить в формулах вместо ссылок на ячейки значения ячеек
  Как вместо ссылок внутри формулы вида =A1*A14+(5+C13)*C14 /B11 получить формулу, но не с ссылками, а значениями ячеек =10*5,2+(5+5)*10 /7,8
• Как функцией пользователя изменить значение другой ячейки
  Небольшая хитрость, которая поможет обойти запрет изменения функцией значения другой ячейки
• Подсчитать сумму ячеек по цвету заливки
  Если привыкли закрашивать ячейки с числами, суммами и пр., но потом пришло время подсчитывать только закрашенные определенным цветом - эта статья в самый раз
• Подсчитать сумму ячеек по цвету шрифта
  Необходимо вычислить сумму ячеек, цвет шрифта которых совпадает с указанным - статья в помощь!
• Подсчитать сумму ячеек по формату
  Что делать, если приходится постоянно суммировать данные ячеек с разным форматом(правая кнопка мыши -Формат ячеек) - просуммировать или подсчитать, например. Стандартных функций для суммирования таких ячеек нет.
• Защита ячеек в зависимости от цвета
  Как быстро защитить на листе только ячейки с определенным цветом или наоборот - разрешить изменять только ячейки с указанным цветом?
• Получить адрес активной ячейки неактивного листа
  По умолчанию активная ячейка может быть исключительно на активном листе. Но Excel запоминает адреса активных ячеек для каждого листа и их можно у него узнать.
• Перевод текста в ячейках через Google
  Универсальная функция перевода текста в ячейках с любых языков на любые выбранные при помощи переводчика Google
• Получить курс валют от ЦБР
  Функнция пользователя, которая покажет и запишет в ячейку актуальный курс для указанной валюты на указанную дату
• Как скачать файл из интернета по ссылке
  После прочтения статьи не просто научитесь скачивать файлы из интернета, но и сможете скачивать их по списку ссылок, пачками
• Архивация/Извлечение из архива через VBA
  Готовые коды с пояснениями как запаковать в архив zip или rar файлы и папки и как их извлечь из архива

РАБОТА С VB ПРОЕКТОМ

• Ошибка - Cant find Project or library
  Почему в полностью рабочем файле(макросе) вдруг появляется ошибка Can't find Project or library - причины и способы устранения
• Code execution has been interrupted - что за баг?
  Внезапно код стал выдавать ошибку Code execution has been interrupted? Решить это можно разными способами
• Субъект не имеет доверия для выполнения указанного действия
  При попытке вставить на форму элемент получаете ошибку "Субъект не имеет доверия для выполнения указанного действия"? Это легко исправить
• Что необходимо для внесения изменений в проект VBA(макросы) программно
  Описаны требования, предъявляемые к Excel для действий, автоматизирующих процессы изменения VBA проекта(удаление и копирование модулей, модификации кодов и т.п.)
• Как удалить макросы в книге?
  Как в книге быстро удалить все коды, модули, формы так, чтобы в книге не осталось ни одного макроса
• Как защитить проект VBA паролем
  Как скрыть все макросы в книге от просмотра и изменений, чтобы их нельзя было изменить без указания пароля
• Как программно снять пароль с VBA проекта?
  Как автоматически, без ручного вмешательства снять пароль с проекта VBA, если пароль известен
• Как добавить код процедуры программно, скопировать модуль
  Готовые коды и инструкции по копированию кодов из одной книги в другую, а так же по созданию новых процедур автоматически из самого VBA
• Как узнать существует ли модуль в книге
  Как можно проверить наличие модуля с кодами в книге
• Кодовое имя листа - что это и зачем нужно? Какие плюсы и минусы?
  При обращении в кодах к имени листа всегда есть вероятность, что лист будет переименован и обращение выдаст ошибку. Как можно обратиться к листу из кода так, чтобы его можно была как угодно переименовывать и перемещать и это не вызывало бы ошибок
• Ошибка Too Many Line Continuations
  При попытке записать макрос на одних и тех же данных постоянно получаете ошибку Too Many Line Continuations? Что тому причиной и можно ли это исправить?
• Элементы ActiveX перестали работать или ведут себя непредсказуемо
  100 лет использовали элементы ActiveX и все работало замечательно, но в какой-то момент элементы отказались работать. Двойной клик создает новую пустую процедуру, все имена элементов сбросились, а попытка создать новый элемент приводит к ошибке? В статье описаны некоторые возможные причини и способы устранения ошибки

РАЗНОЕ

• Как в ячейке с формулой вместо ошибки показать 0
  На листе много ячеек с формулами, которые показывают ошибки, а начальство требует скрыть эти ошибки? Решить проблему можно за пару секунд
• Проверка данных
  Как заставить пользователя записывать в ячейки только те данные, которые надо, исключив опечатки и неправильные значения?
• Не редактируются ячейки
  Захотели изменить данные в ячейке, но привычными способами сделать это не получается? Узнайте причины и способы устранения
• Пустая ячейка определяется Excel-ем как не пустая. Что за глюк?
  С виду ячейка пустая, но формулы с ней работают не как с пустой, функция ЕПУСТО(ISBLANK) не считает её пустой, а любое математическое действие приводит к ошибке. Почему и что делать?
• Как сделать лист скрытым?
  Как быстро и просто убрать лист из книги Excel так, чтобы его не было видно, но можно было получить с него данные?
• Как сделать лист очень скрытым
  Как скрыть лист в книге таким образом, чтобы его невозможно было отобразить без спец.навыков?
• Защита листов и ячеек в MS Excel
  Подробное описание возможностей защиты ячеек в Excel с разбором всех параметров и их неочевидных нюансов
• Как разрешить изменять только выбранные ячейки?
  Как защитить лист так, чтобы можно было изменять исключительно указанные ячейки, а все остальные были заблокированы для изменений?
• Как получить имя листа формулой
  Узнайте как можно записать в ячейку или формулу имя листа с макросами и без них
• Имя предыдущего листа new!
  Как в Excel получить имя предыдущего листа(того, который по порядку перед текущим) или получить значение ячейки с предыдущего листа. И бонусом значение со следующего листа
• Как удалить в ячейке формулу, оставив значения?
  В листе много лишних формул, которые тормозят работу с файлом и при этом сами формулы уже не нужны - нужны только их значения?
• Видимое значение ячейки в реальное
  В ячейке записано "000748", но при попытке скопировать значение оно превращается в "748"? Почему и как получить именно видимое значение
• Как быстро умножить/разделить/сложить/вычесть из множества ячеек одно и то же число?
  Как двумя кликами мыши увеличить сумму на процент НДС или уменьшить на известный процент скидки без формул и доп.столбцов
• Функция СУММЕСЛИ, а так же СУММЕСЛИ по двум критериям
  Как найти сумму дохода по конкретному отделу, если все отделы перемешаны? А если при этом надо учитывать еще и филиал?
• Как просуммировать данные с нескольких листов, в том числе по условию
  Необходимо суммировать доходы и расходы по статьям в отдельном листе, но исходные данные раскиданы по разным листам и все строки в разном порядке? В статье несколько готовых решений
• Как подсчитать количество повторений
  Как узнать сколько раз в таблице или в отдельной ячейке повторяется указанное значение?
• Как получить список уникальных(не повторяющихся) значений?
  Несколько способов как отобрать из множества ячеек только уникальные значения
• Как найти значение в другой таблице или сила ВПР
  Если часто приходится сравнивать данные в двух столбцах(таблицах) или тянуть в таблицу продаж цену из прайса по наименованию товара - вы обязаны прочитать эту статью!
• ВПР и интервальный просмотр(range_lookup) new!
  Как определить размер процента в зависимости от суммы заказа, если суммы заказа могут быть любыми, а процент надо определить, опираясь на интервал от 0 до 1000 - 0%, от 1000 до 2000 - 2% и т.д. и таких условий может быть 20 и более и в любой момент они могут изменить. Простой приём с использованием ВПР, который поможет сделать это за 10 секунд
• ВПР с поиском по нескольким листам
  Как найти значение, если оно может быть записано не в одном конкретном листе, а в любом листе книги?
• ВПР с возвратом всех значений
  Как найти все значения в таблице, удовлетворяющие заданному критерию? В статье предлагаются решения формулами и макросами
• ВПР по двум и более критериям
  Как найти значение в другой таблице на основании двух и более условий? Например по названию отдела и городу? Стандартно ВПР ищет только по одному критерию
• Найти в ячейке любое слово из списка
  Как и при помощи каких формул можно определить, есть ли в ячейке с текстом любое слово из списка стоп-слов?
• Как получить последнюю заполненную ячейку формулой?
  Необходимо вычислять сумму или среднее в столбце, но данные постоянно дополняются? Конец таблицы можно найти и формулой
• Как перевернуть слово?
  Как из слова "привет" получить "тевирп" с применением макросов и без них
• Как удалить строки по условию?
  Необходимо удалить все строки на листе, содержащие в столбце определенное значение или любое значение из списка? Делюсь готовыми кодами для решения задачи
• Всплывающая подсказка в ячейке
  Инструкция как в ячейках Excel создавать красивые всплывающие подсказки и не только для одной ячейки, но и для всех выбранных ячеек одним кликом!
• Как массово изменить гиперссылки?
  Создали множество гипессылок на файлы или на сайт, но внезапно путь к файлам или домен изменился? В статье приведено решение, как это сделать быстро и без проблем
• Как одновременно просматривать информацию с нескольких листов/диапазонов?
  Хотите узнать как можно одновременно видеть изменения сразу на нескольких листах? Это делается за пару минут
• Как сделать одинаковые изменения сразу на нескольких листах
  Изменить шапку таблицы разом на всех листах книги можно буквально одним движением
• Как поменять в формулах относительные ссылки на абсолютные и наоборот?
  Код из статьи позволит быстро заменить во всех указанных ячейках ссылки на ячейки в абсолютные или относительные
• Как узнать есть ли формула в ячейке?
  Пара способов как определить какой тип данных в ячейке: формула или статичное значение, занесенное руками
• Файл долго фильтрует строки/тормозит при фильтрации
  Файл Excel ни с того ни с сего начал дико тормозить? Даже при фильтрации ячеек приходится ждать по минуте? Узнайте почему происходит и как исправить
• Как уменьшить размер файла
  Ваш файл содержит пару листов и не более 1000 строк на каждом, но размер файла далеко за 1Мб и открывается по минуте? Значит он нуждается в капитальной чистке!
• Excel удаляет вместо отфильтрованных строк - все?! Как избежать
  Отфильтровали строки, удалили отфильтрованное, а после снятия фильтра оказалось, что удалились все строки. Почему и как исправить
• Как заменить/удалить/найти звездочку?
  Как найти текст, содержащий звездочку(*), вопр.знак или тильду, при помощи стандартного поиска, а так же их использование внутри функций ВПР, ПОИСКПОЗ и пр.
• Как вернуть меню в стиле Excel 2003 в версиях 2007 и старше
  Если до сих пор не можете привыкнуть к модному Ribbon-стилю меню в Excel - можно добавить удобное-любимое меню в стиле 2003 Excel
• Как получить слово после последнего пробела
  В статье приведены примеры формул, которые помогут "вытащить" любое слово, расположенное в указанной позиции от начала или от конца текста
• Не изменяется формат ячейки
  Решили изменить цвет ячейки, назначить границы, но формат не хочет изменяться? Узнайте как решить проблему
• Как определить последний рабочий день месяца(Excel, VBA, PowerQuery)
  Как при помощи стандартных формул, Visual Basic for Application и Power Query определить какой день в месяце был последним рабочим днем? На самом деле все не так сложно!
• Невозможно разорвать связи с другой книгой
  Как создать связь с другими книгами, для чего она нужна, как её потом разорвать и что делать, если связь никак не хочет разрываться? Ответы на все эти вопросы в статье
• Производственный календарь в Excel (VBA и Power Qwery)
  Как автоматически получить все праздничные даты согласно производственного календаря? В статье приводится решение через VBA(макросы) и через надстройку Power Qwery
• Получить ключевую ставку ЦБ РФ через VBA и Power Qwery
  Получение ключевой ставки ЦБ РФ на дату и период автоматически при помощи VBA(макросов) и через Power Qwery. Разбираются нюансы получения ставки и как обойти проблемы при получении ставки за периоды и выходные дни

БАГИ И ГЛЮКИ EXCEL

• Excel неправильно считает. Почему?
  Почему при вычитании двух одинаковых чисел Excel считает их разными, и пишет, что разница между ними не равна нулю?
• При вставке из VBA картинки на лист ошибка Метод paste из класса worksheet завершен неверно
  В Excel 2016 можно получить ошибку на ровном месте: при попытке из кода Visual Basic for Applictation вставить скопированную картинку появляется ошибка Метод paste из класса worksheet завершен неверно.
  Почему и как избежать?
• Глюк работы в UDF методов SpecialCells и FindNext
  Используете внутри функций UDF(функции пользователя) методы SpecialCells или FindNext для поиска нужных ячеек, но результат не тот, который ожидался? Узнайте почему и можно ли это исправить
• Excel тормозит/зависает при копировании ячейки/диапазона
  При копировании любой ячейки(одной или нескольких) Excel виснет. Однако все остальные действия(вставка и удаление ячеек, изменение формул и форматов и т.п.) проходят без проблем. Как исправить?
• Что такое _xlfn в формуле Excel? new!
  В файле в формуле загадочное _xlfn.UNIQUE, которого нет ни среди имен, ни в функциях. А если убрать - формула перестает работать. Что это, как появилось и как исправить?

POWER BI и POWER QUERY

• Знакомство с Power BI
  Что такое Power BI, какие возможности представляет, где взять, что делать дальше, зачем он и кому пригодится - кратко об этом здесь
• Power Query - что такое и почему её необходимо использовать в работе?
  Краткое введение в Power Query - что это, где взять, зачем и почему необходимо использовать. Демонстрация некоторых возможностей Power Query по обработке данных
• Получить данные из файлов XML при помощи Power Query
  Пошаговое руководство как получить информацию из файлов XML в таблицу Excel при помощи Power Query. Так же отдельно показано как получить данные из всех файлов XML в папке, какие могут быть ошибки и как их устранить
• Как из Power Query или Power BI получить данные из Google Spreadsheets(Гугл таблицы)
  Как разместить файл Excel в Google-таблицах для редактирования несколькими пользователями и загружать в запрос Power Query/Power BI данные для обработки напрямую из Google
• Курс валют при помощи Power Query
  Как с помощью Power Query сделать обновляемый ежедневно курс валют на заданную дату, для указанной валюты, а так же вывести историю курсов валют за заданный период на указанные валюты?
• Объединение(слияние) запросов при помощи PowerQuery
  Как сравнить две таблицы в PowerQuery и вывести результат? Как "слить" две таблицы в одну так, чтобы в итоге получилась таблицы из всех значений первой и только отсутствующих второй? Как получить только отсутствующие значения первой или второй таблицы? Ответы на все эти вопросы кратко и понятно излагаются в статье (+ видеоурок)
• Вычисления в PowerQuery
  Как в PowerQuery создавать формулы и вычисляемые столбцы? Какие особенности вычислений, нюансы и где найти доступные для использования формулы. Статья как краткая инструкция + подробный видеоурок
• Как защитить запрос Power Query от изменений new!
  Можно ли защитить текст запроса PowerQuery от посторонних глаз и от изменений? Разбираем возможности по защите запросов, какие при этом появляются нюансы и ограничения
• Как из оборотно-сальдовой ведомости сделать сводную таблицу при помощи Power Query
  Практический пример применения возможностей PowerQuery для обработки оборотно-сальдовой ведомости
• Собрать и просуммировать данные из разных файлов при помощи PowerQuery
  Статья с подробным описанием и видеоуроком о том, как при помощи надстройки PowerQuery собрать и просуммировать по определенному столбцу данные со всех файлов Excel в папке в единую таблицу. К статье приложена готовая модель с файлами и запросом
• Собрать данные из защищенных паролем файлов - PowerQuery
  При попытке получить данные из файлов, защищенных паролем, запрос PowerQuery выдает ошибку подключения. Как можно обойти это ограничение и все же обновить запрос, не снимая пароль с каждого файла вручную?
• Как обновить запросы Power Query(и не только) в указанном порядке? new!
  Команда Обновить все обновляет все без исключения запросы в книге, при этом порядок обновления никак и ничем не регулируется. А как быть, если надо обновить не все запросы в книге, а только указанные, да еще и в строго указанном порядке? Рассматриваем несколько вариантов проблемы и её решения
• Собрать данные с таблиц с изменяющимися столбцами в PowerQuery
  Как создать запрос к файлам в PowerQuery таким образом, чтобы он получал данные даже если столбцы в файлах были удалены, изменены или добавлены? По умолчанию PowerQuery использует жесткую привязку к именам столбцов исходного файла
• Создать список дат(месяцев, кварталов, лет) на основании даты начала и окончания периода new!
  Как в Power Query создать непрерывный список дат на каждый день, неделю, месяц или год. Подробная инструкция по загрузке и созданию списка, а так же пара трюков для создания на основе созданной таблицы наглядной диаграммы Ганта.
• План-фактный анализ в Excel при помощи Power Query
  Видеоурок по настройке автоматического сбора данных с множества бюджетов, объединение их в одну сводную таблицу с добавлением вычисляемых столбцов отклонений и настройке удобных срезов
• Относительный путь к данным PowerQuery
  При перемещении или пересылке файла с запросом приходится каждый раз изменять источник данных. Узнайте, как сделать его автоматически изменяемым в зависимости от расположения файла с запросом
• Модель создания план-фактного анализа предприятия при помощи Power BI. Часть 1
  Пошаговое руководство по сбору данных от всех отделов и сравнении их показателей с составленным ранее планом на год
• Оставить цифры или текст при помощи PowerQuery
  Как при помощи Power Query оставить в таблице только цифры или только текст? Как отделить английские буквы от русских внутри артикулов: 12234-RT BRAUN Кофемолка => 12234-RT BRAUN / Кофемолка
• Замена значений по списку в PowerQuery
  Как с помощью Power Query перевести текст с русского на транслит и обратно, а так же заменить сразу несколько слов на другие на основании списка соответствия
• Как в PowerQuery обратиться к предыдущей строке
  Как в Power Query быстро и просто сравнить текущую строку с предыдущей, на примере таблицы с курсами валют
• Как перейти к определенной строке таблицы в Power Query
  Как в Excel и редакторе Power Query перейти к указанной строке. Разбирается несколько способов, их особенности, плюсы и минусы
• Фильтр без учета регистра в Power Query
  Как в Power Query заставить фильтр отбирать записи, не учитывая при этом регистр(чтобы маленькие и большие буквы воспринимались одинаково)

---

Статья помогла? Поделись ссылкой с друзьями!     Видеоуроки

---

Поиск по меткам

Access apple watch Multex Power Query и Power BI VBA управление кодами Бесплатные надстройки Дата и время Записки ИП Надстройки Печать Политика Конфиденциальности Почта Программы Работа с приложениями Разработка приложений Росстат Тренинги и вебинары Финансовые Форматирование Функции Excel акции MulTEx ссылки статистика