ОСНОВНЫЕ ПОНЯТИЯ Как Excel воспринимает данные? Почему Excel меняет число 1.2 на 1.фев? Как с этим бороться и что еще необходимо знать при работе с данными в Excel Что такое формула? В статье разъясняется как само понятие формулы и её аргументов так и способы создания формулы и записи её в ячейки Что такое формула массива Узнайте как одной формулой перемножить диапазоны и разберите на примерах как это работает Динамические массивы в Excel new! Принцип работы динамических массивов в новых версиях 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! Как правильно обрабатывать ошибки в таблицах Power Query и какие при этом могут возникнуть сложности и неожиданные нюансы Как защитить запрос Power Query от изменений new! Можно ли защитить текст запроса PowerQuery от посторонних глаз и от изменений? Разбираем возможности по защите запросов, какие при этом появляются нюансы и ограничения Как из оборотно-сальдовой ведомости сделать сводную таблицу при помощи Power Query Практический пример применения возможностей PowerQuery для обработки оборотно-сальдовой ведомости Собрать и просуммировать данные из разных файлов при помощи PowerQuery Статья с подробным описанием и видеоуроком о том, как при помощи надстройки PowerQuery собрать и просуммировать по определенному столбцу данные со всех файлов Excel в папке в единую таблицу. К статье приложена готовая модель с файлами и запросом Как дождаться обновления выполнения запроса new! При обновлении запросов из VBA через Обновить все(Refresh All), VBA не дожидается обновления запросов, а сразу выполняет следующую строку кода. А необходимо дождаться полного обновления, чтобы работать с полученными от запроса данными. Как кодом VBA обновить запрос так, чтобы код сначала дождался полного выполнения запроса, а затем уже приступил к другим действиям? Как обновить запросы Power Query(и не только) в указанном порядке? new! Команда Обновить все обновляет все без исключения запросы в книге, при этом порядок обновления никак и ничем не регулируется. А как быть, если надо обновить не все запросы в книге, а только указанные, да еще и в строго указанном порядке? Рассматриваем несколько вариантов проблемы и её решения Собрать данные из защищенных паролем файлов - PowerQuery При попытке получить данные из файлов, защищенных паролем, запрос PowerQuery выдает ошибку подключения. Как можно обойти это ограничение и все же обновить запрос, не снимая пароль с каждого файла вручную? Собрать данные с таблиц с изменяющимися столбцами в 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 заставить фильтр отбирать записи, не учитывая при этом регистр(чтобы маленькие и большие буквы воспринимались одинаково)
ОСНОВНЫЕ ПОНЯТИЯ 
СПИСКИ И ДИАПАЗОНЫ 
СВОДНЫЕ ТАБЛИЦЫ И АНАЛИЗ ДАННЫХ 
ГРАФИКИ 
УСЛОВНОЕ ФОРМАТИРОВАНИЕ 
МАКРОСЫ (VBA ПРОЦЕДУРЫ) 
РАБОТА С VB ПРОЕКТОМ 
РАЗНОЕ 
БАГИ И ГЛЮКИ EXCEL 
POWER BI и POWER QUERY 
