 |
Приемы работы в Microsoft Excel |
Выпадающие списки
- Выбор фото из выпадающего списка Хотите выбирать марку товара из выпадающего списка и получать в соседней ячейке фотографию выбранного продукта? Причем без всякого программирования?
- Выпадающий список в ячейке Как создать выпадающие списки в ячейках листа Excel,чтобы не вводить повторяющиеся значения с клавиатуры. UPDATED!
- Выпадающий список в ячейке с удалением использованных элементов Как создать в ячейке выпадающий список, содержимое которого должно меняться - если один из элементов списка уже был выбран ранее, то он должен автоматически удаляться из выпадающего набора вариантов.
- Выпадающий список с данными из другого файла Как создать в ячейке выпадающий список, содержащий, например, наименования товаров, хранящиеся в другой книге Excel.
- Выпадающий список с добавлением новых элементов Как создать выпадающий список (например, сотрудников) в ячейке. Причем в случае, если нужного человека в списке нет, то пользователь просто вводит его имя с клавиатуры, и новый человек автоматически добавляется в выпадающий список и отображается там в дальнейшем.
- Связанные выпадающие списки Представьте себе два выпадающих списка в ячейках, причем от того, что выбрано в первом - зависит содержимое второго. UPDATED!
Формулы и функции
- Автоматическое разбиение одного столбца с данными на несколько Как быстро и автоматически разбить столбец с данными (например с ФИО полностью) на три столбца с фамилией, именем и отчеством в каждом по-отдельности.
- Быстрое склеивание текста из нескольких ячеек Как собрать текст из нескольких ячеек в одну? Как из Иванова Ивана Ивановича сделать Иванова И.И.? Как объединять ячейки без потери текста всех ячеек кроме верхней левой?
- Выборочное суммирование по двум критериям Как выбрать и просуммировать данные из большой таблицы по нескольким(!) заданным условиям.
- Вычисление возраста или стажа Как точно вычислить возраст/стаж человека (или любой другой интервал времени) в полных годах, месяцах или днях.
- Вычисление нужной даты Как формулой вычислить дату нужного дня недели для заданного месяца и года (например, второй среды мая 2011 г.)
- Делим текст на куски В стандартном наборе Excel очень не хватает функции, которая бы делила текстовую строку на части по заданному разделителю. Восполним этот недостаток!
- Динамическая выборка из списка функциями ИНДЕКС и ПОИСКПОЗ О двух ближайших "родственниках" функции ВПР (VLOOKUP) - функциях поиска и выборки ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH), позволяющих выбирать данные из списка по заданным условиям.
- Динамическая гиперссылка Как с помощью функций ГИПЕРССЫЛКА и ПОИСКПОЗ сделать "живую" ссылку на определенную строку в другой таблице, содержащую нужные вам данные, чтобы потом быстро переходить из одной таблицы в другую.
- Заполнение бланков данными из списка (базы данных) Как при помощи функции ВПР (VLOOKUP) и простого макроса быстро заполнять любой бланк (счет-фактура, платежка и приходно-кассовый ордер) данными из списка (таблицы).
- Заполнение пустых ячеек в списке Как быстро заполнить пустые ячейки в столбце, вставив туда значения вышестоящих ячеек.
- Извлечение уникальных (не повторяющихся) записей из списка Как "выдернуть" из большого списка всех, кто в нем встречается, если элементы перемешаны и каждый может встречаться не один раз?
- Извлечение уникальных строк из таблицы по нескольким столбцам Как извлечь из большой таблицы уникальные строки, учиывая совпадения значений не в одном, а сразу в нескольких столбцах.
- Использование динамических именованных диапазонов Как сделать ссылку на диапазон ячеек так, чтобы она автоматически растягивалась-сжималась в зависимости от количества заполненных ячеек в диапазоне?
- Использование формул массивов в Excel Что такое формула массива? Как ее вводить, редактировать и удалять? Как формулы массива могут упростить и облегчить жизнь пользователя в больших таблицах?
- Использование функции ВПР (VLOOKUP) для подстановки значений Одна из самых мощных и красивых функций в Excel - функция ВПР (VLOOKUP), позволяющая автоматически находить нужные значения в таблице. О том, как ее правильно использовать - просто и понятно.
- Конвертер величин Сколько грамм в двух унциях и дюймов в пяти метрах? Сколько минут в неделе и грамм в столовой ложке? Статья о том как использовать малоизвестную функцию ПРЕОБР (CONVERT) для конвертации из одной системы мер в другую в Excel.
- Кредитный калькулятор Сколькоя могу взять в кредит? Сколько придется выплачивать в месяц по кредиту? На какой срок я могу взять кредит? На все эти вопросы можно ответить с помощью нашего кредитного калькулятора.
- Логика в Excel Как заставить Excel проверить заданное условие и - в зависимости от результата проверки - выполнить то или иное действие (от заливки ячейки нужным цветом до вычислений по разным формулам).
- Номер квартала по дате Как быстро определить номер квартала для заданной даты?
- Номер недели по дате Как определить номер рабочей недели для любой заданной даты.
- Нумерованный список в Excel как в Word Как создать нумерованный список с автоматической перенумерацией при редактировании элементов списка как в Microsoft Word.
- Подбор ближайшего похожего слова Пользовательская функция для поиска наиболее похожего текста в списке (по максимальному количеству подряд идущих совпадений символов).
- Поиск данных в таблице Как извлечь данные из таблицы по заданному критерию. Функция ВПР (VLOOKUP) находит только значение только по первому совпадению, а нужно вытащить все. Поможет формула массива!
- Поиск символов латиницы в русском тексте Как быстро найти в символы латиницы, ошибочно набранные вместо русских букв (английская "С" вместо русской "С") в большом списке?
- Превращение строк в столбцы и обратно Как преобразовать горизонтальный диапазон ячеек в вертикальный и обратно.
- Проверка текста по маске Как быстро проверить - соответствует ли текст в ячейке заданной маске (с использованием символов *, ?, # и т.д.)
- Прятки с формулами Как быстро найти и выделить все ячейки, содержащие формулы, а не значения?
- Работа с датами и временем в Excel Как Excel хранит и обрабатывает ячейки с датами и временем. Какие операции возможны над такими ячейками. Типовые ситуации: вычисление разницы между датами в днях, в рабочих днях, вычисление возраста в полных годах, суммирование временных интервалов для вычисления количества рабочих часов и др.
- Сколько гласных и согласных букв в слове Как быстро подсчитать количество гласных и согласных букв в слове. Или - в более общем случае - количество вхождений символов одной строки в другую.
- Случайная выборка из списка Как случайным образом выбрать из большого массива данных заданное количество элементов?
- Создание писем с помощью функции ГИПЕРССЫЛКА Как быстро создать уже заполненный бланк сообщения электронной почты (получатели, копия, текст) одним нажатием на ячейку.
- Сортировка по цвету Хотите сортировать таблицу по цвету ячеек? Синие - к синим, а красные - к красным и т.д.
- Сортировка формулой Несколько способов отсортировать числовой или текстовый список не через меню Данные - Сортировка, а с помощью формул "на лету".
- Сравнение двух диапазонов данных Несколько способов решения задачи сравнения двух диапазонов данных и выделения отличий.
- Ступенчатые скидки Легкая реализация проверки нескольких условий на примере ступенчатых скидок на оптовые закупки.
- Суммирование округленных значений Сколько будет 2 умножить на 2? Глупый вопрос. Только вот ответ еще глупее - не обязательно 4. По-крайней мере в Excel.
- Типы ссылок на ячейки в формулах Excel В чем разница между относительными, смешанными и абсолютными ссылками на ячейки в формулах? Как сделать ссылку на ячейку, которая не будет изменяться ни при копировании исходной ячейки, ни при вставке перед ней новых строк/столбцов?
- Удаление пустых ячеек из диапазона Несколько способов удалить из заданного диапазона пустые ячейки, оставив только ячейки с данными.
- Улучшаем функцию ВПР (VLOOKUP2) Встроенная функция ВПР (VLOOKUP) - несомненно одна из мощнейших функций Excel. Но имеет один существенный недостаток - ищет только первое вхождение искомого значения в таблицу и только в крайнем левом столбце. А если нужно найти 2-е, 3-е и не в крайнем левом?
- Функция СУММПРОИЗВ - секретное оружие Excel Как быстро создать отчет по большой таблице данных не используя макросов и сводных таблиц.
Форматирование
- Выделение дубликатов цветом Как быстро выделить элементы, которые встрчаются в списке более одного раза?
- Зебра Как быстро залить каждую 2-ю строчку внутри выделения.
- Создание пользовательских форматов в Excel Как создать формат "тыс.", "кг", "шт.", "чел." и тому подобное.
- Условное форматирование Как сделать, чтобы ячейки автоматически выделялись цветом при выполнении определенных условий.
- Условное форматирование в Excel 2010 Новые фишки, возможности и отличия условного форматирования в Excel 2010 по сравнению с прошлыми версиями. NEW!
Макросы
- Автоматическое добавление текущей даты в ячейку Допустим, Вы заполняете таблицу заказов и напротив каждого заказа хотели бы автоматически регистрировать дату и время его занесения... Легко!
- Быстрый ввод даты и времени Как быстро вводить дату и время в виде простых чисел (110507 вместо 11.05.2007 и 0635 вместо 6:35) в заданные ячейки листа.
- Всплывающий календарь DatePicker Самый лучший всплывающий календарь для быстрого ввода даты в любую выбранную ячейку, который я видел! Автор - Sam Radakovitz, program manager of the Microsoft Excel team.
- Выпадающий календарь для ввода дат Вам часто приходится вводить различные даты в ячейки листа? Хотите делать это не вручную с клавиатуры, а быстро и красиво - при помощи выпадающего календаря?
- Заставка при загрузке книги Excel Как создать и выводить на экран окно заставки (с логотипом компании, именем или фотографией автора и т.п.) при открытии определенной книги в Excel.
- Исправление выгрузки из 1С Макрос в виде надстройки для исправления внешнего вида выгруженной из "1С" 8 версии карточки-счета, чтобы ее стало возможно нормально сортировать, фильтровать и т.д.
- Координатное выделение Автоматическая координатная подсветка текущей строки и столбца "крестом" - крайне удобно при просмотре больших таблиц! UPDATED
- Курс доллара для любой заданной даты Макрос, который умеет вводит в текущую ячейку курс доллара для любой заданной Вами даты из архива Центробанка РФ (через интернет).
- Макрос-переводчик Если Вам приходится делать многоязычные отчеты, то Вам пригодится этот макрос, умеющий переводить таблицы Excel с одного языка на другой. Всем работающим в иностранных компаниях посвящается...
- Многоуровневая группировка строк Как быстро сгруппировать строки в сложной многоуровневой таблице, чтобы потом быстро и удобно сворачивать-разворачивать группы-подгруппы с помощью одного щелчка.
- Оглавление книги Excel Хотите автоматически получить список всех листов Вашей книги в виде гиперссылок и переходить на нужный лист одним щелчком?
- Отправка книги или листа по email Как быстро отправить всю книгу или отдельный лист по электронной почте на заданный адрес. UPDATED
- Пометка элементов списка флажками (checkbox) Хотите иметь возможность использовать на листе Excel флажки-галочки, чтобы, например, отбирать из списка нужные элементы?
- Редизайнер таблиц Специальный макрос, позволяющий переконструировать Вашу таблицу в тот вид, который понимает и с которым может работать Excel.
- Сборка листов Макрос для быстрой автоматической сборки листов из нескольких заданных файлов в текущую книгу Excel.
- Создание макросов и пользовательских функций на VBA Как создавать макросы для автоматизации повторяющихся действий в Excel. Как добавить к стандартным функциям Excel свои собственные.
- Создание резервных копий ценных файлов Как заставить Excel автоматически сохранять резервную копию ценного файла в заданную папку?
- Сортировка листов в книге Как быстро отсортировать листы по алфавиту в большой книге?
- Список файлов в папке Как быстро сформировать на листе Excel список всех файлов из заданной папки и ее подпапок NEW!
- Сумма прописью Готовая пользовательская функция на VBA для перевода чисел в текст, т.е. в сумму прописью (123 = сто двадцать три).
- Транслит Пользовательская функция для перевода русского текста в ячейке в транслит (Смирнов = Smirnov и т.п.)
- Удаление всех пустых строк в таблице Быстрый и легкий способ удалить все пустые строки в большой таблице
- Ячейка с накоплением (нарастающим итогом) Как сделать ячейку, при вводе новых значений в которую они будут суммироваться с уже находящимися там числами?
Диаграммы
- Быстрое добавление данных в диаграмму Как быстро добавить в имеющуюся диаграмму дополнительные данные (столбцы)?
- Интерактивная диаграмма Как создать "живую" интерактивную диаграмму с прокруткой во времени, зумом отдельных фрагментов и включением-выключением нужных рядов с помощью флажков.
- Инфокривые sparklines в Excel 2010 Все о новой фишке Excel 2010 - удобном способе наглядного отображения числовых данных в виде миниатюрных графиков внутри ячеек.NEW!
- Календарные графики в Excel (диаграмма Ганта) Давайте рассмотрим способы построения в Excel календарных графиков (графики проектов, дежурств, отпусков и т.д.)
- Календарный график проекта (диаграмма Ганта) версия 3.0 Улучшенный вариант календарного графика проекта (диаграммы Ганта) с учетом выходных и праздников и динамической подсветкой строк-столбцов макросом.
- Микрографики в ячейках Как построить микрографики внутри ячеек, чтобы наглядно отобразить столбец с числовыми данными не используя громоздких классических диаграмм?
- Разные типы диаграмм "в одном флаконе" Сочетание нескольких разных типов диаграмм (гистограмма, график и круговая, например) в одной диаграмме.
Защита данных
- Запрет ввода повторяющихся значений Как запретить пользователю вводить значения, которые уже присутствуют в списке, т.е. обеспечить ввод строго уникальных (неповторяющихся) значений?
- Защита данных в Excel Как запретить пользователю менять данные в ячейках листа? Удалять, копировать и менять имя листа? Как зашифровать файл, чтобы прочитать его могли только знающие пароль?
- Ограничение области доступа на листе Как ограничить область листа, в пределах которой пользователь может просматривать и редактировать содержимое ячеек. Причем без использования команды Сервис-Защита...?
- Правильная защита макросом Как защитить лист так, чтобы пользователь не мог изменять данные на листе, а ваш макрос - мог. Или чтобы можно было использовать группировку на защищенном листе? NEW!
- Спрятать лист от чужих глаз Как скрыть лист, чтобы его не было видно в окне Формат-Лист-Отобразить и другие пользователи не знали о его существовании?
Списки
- Импорт в Excel из внешних баз данных Как настроить подключение Excel к внешней базе данных. Как составить запрос и загрузить в Excel информацию из БД, отфильтровав и отсортировав результаты запроса желаемым образом.
- Консолидация (объединение) данных из нескольких таблиц в одну Как, имея несколько разных таблиц на разных листах или в разных файлах, свести их в один итоговый отчет?
- Основы работы со списками Как создать удобный в последующей работе список. Каким правилам следовать при создании и вводе данных. Как найти нужную информацию в списке. Как закрепить "шапку" таблицы, чтобы она не прокручивалась.
- Отображение условий автофильтра Зачастую при использовании автофильтра вот так вот сразу и не скажешь - какие именно столбцы и по каким критериям сейчас отфильтрованы. Есть способ вывести эти условия на экран...
- Способы фильтрации списков Как быстро отфильтровать (выбрать) из списка нужные записи. Плюсы и минусы автофильтра. Пользовательский фильтр с условиями. Неограниченные возможности расширенного фильтра.
- Суперфильтр Быстрая и удобная альтернатива стандартному Автофильтру с помощью небольшого макроса. Умеет искать по маске и одному/двум сложным условиям (с И и ИЛИ). Без мыши. В реальном времени.
- Удобный автофильтр Сколько раз в день Вы пользуетесь автофильтром? Может сделаем его чуть-чуть удобнее?
- Умные таблицы Excel 2007 Как превратить простой диапазон в действительно удобную для работы таблицу с автоматически подстраивающимися размерами, приятным дизайном и кучей других бонусов. NEW!
Сводные таблицы
- Быстрая настройка форматов данных в сводной таблице Как с помощью простого макроса быстро настроить форматы всех полей в сводной таблице, подтянув их из исходных данных.
- Группировка данных в сводных таблицах Как в сводной таблице сгруппировать даты с нужным шагом (день-месяц-квартал...), числа в заданные диапазоны (от-до) или наименования (товары по категориям).
- Настройка вычислений в сводных таблицах Как произвести тонкую настройку вычисляемых данных в сводной таблице (поменять сумму на количество, посчитать долевые проценты, отобразить динамику и т.д.).
- Новые возможности сводных таблиц в Excel 2010 Что нового появилось в сводных таблицах в крайней версии Excel 2010? Новые функции долевых расчетов, удобное интерактивное фильтрование срезами (slicers) и т.д.
- Сводная по нескольким диапазонам с разных листов Как при помощи простого макроса построить полноценную сводную таблицу не по одному, а по нескольким диапазонам данных, находящихся на разных листах.NEW!
- Сезонность продаж Как вычислить коэффициенты сезонности продаж при помощи сводной таблицы, используя группировку дат с нужным шагом?
- Создание отчетов при помощи сводных таблиц Как за несколько секунд из огромной таблицы в несколько тысяч строк сформировать компактный, наглядный и правильный отчет?
Всякое...
- Быстрый переход между листами Как быстро перейти на нужный лист в книге, если листов много?
- Выбор лучшего банка для кредита с помощью Сценариев Как с помощью инструмента Сценарии (Scenarios) в Excel выбрать себе банк с лучшими условиями кредитования?
- Зачем нужен стиль ссылок R1C1 Если у вас в заголовках столбцов листа Excel вместо букв (А,В,С...) цифры и все формулы превратились в непонятную кашу из букв R,C и квадратных скобок - значит вы переключились в режим R1C1. Что это, зачем он нужен и как с ним бороться?
- Изменение цветов стандартной палитры Вам не надоели цвета стандартной палитры Excel?
- Импорт курса валют из интернета Несколько способов автоматически импортировать в Excel курс доллара, евро или любой другой нужной валюты на заданную дату (или интервал дат) с сайта Центробанка или Яндекса... UPDATED!
- Новые возможности Excel 2007 Самые вкусные фишки новой версии Excel 2007. Коротко, понятно и с наглядными примерами.
- Новые возможности в интерфейсе Excel 2010 Что интерсного, нового и полезного появилось в интерфайсе версии Excel 2010? Как создать и настроить свою вкладку? Что такое предпросмотр режимов вставки? Где теперь круглая кнопка Office? NEW!
- Оптимизация доставки Использование надстройки Поиск решения (Solver) для оптимизации на примере транспортной задачи доставки товаров со склада в магазины.
- Отображение зависимостей Отображение зависимостей между ячейками в виде стрелок, показывающих откуда и куда идут потоки информации в Вашем расчете.
- Распечатка бланков Word с данными из Excel Как автоматически распечатать большое количество однотипных бланков Word, которые должны быть заполнены данными (ФИО, адреса, компании) из таблицы Excel.
- Скрытие/отображение ненужных строк и столбцов Как быстро скрыть (а потом - отобразить обратно) временно ненужные для просмотра на листе строки или столбцы?
- Соответствие команд меню Excel 2003 и ленты Excel 2007 Удобная флеш-анимация для тех, кто недавно перешел на 2007-ю версию Excel и долго ищет привычные команды в новом интерфейсе. Выбираете нужную команду в старом Excel 2003, а вам показывают где ее же найти в Excel 2007.
- Сохранение пользовательских настроек панелей инструментов Как сохранить и перенести на другой компьютер пользовательские настройки панелей инструментов в Excel (расположение панелей, добавленные нестандартные кнопки, созданные самостоятельно панели инструментов и т.д.)
- Сохранение рабочей области Как сохранить форму и расположение окон всех открытых книг, с которыми Вы обычно работаете.
- Сочетания клавиш Excel Удобная таблица с горячими клавишами Excel на русском и английском.
- Увеличение количества откатов в Excel Хотите увеличить количество отменяемых последних действий (Undo) с 16 до 100?
- Уменьшение размера книги Что делать, если ваш рабочий файл "распух" до полусотни мегабайт и Excel жутко тормозит при открытии книги и ее пересчете. Как уменьшить размер файла и ускорить работу?
- Фото в примечаниях к ячейке Хотите использовать фотографию в качестве примечания к ячейке?
- Шпаргалка по горячим клавишам и быстрым трюкам в Excel Шпаргалка, содержащая 57 самых лучших и полезных сочетаний клавиш в Microsoft Excel и 12 быстрых трюков и приемов работы на каждый день. Удобно и компактно сверстана на один лист А4 - распечатайте и пользуйтесь.