6 малоизвестных, но очень полезных функций Excel. Эксель интересные функции
12 простых приёмов для эффективной работы в Excel
1. Быстро добавить новые данные в диаграмму:Если для вашей уже построенной диаграммы на листе появились новые данные, которые нужно добавить, то можно просто выделить диапазон с новой информацией, скопировать его (Ctrl + C) и потом вставить прямо в диаграмму (Ctrl + V).
2. Мгновенное заполнение (Flash Fill)Эта функция появилась только в последней версии Excel 2013, но она стоит того, чтобы обновиться до новой версии досрочно. Предположим, что у вас есть список полных ФИО (Иванов Иван Иванович), которые вам надо превратить в сокращённые (Иванов И. И.).Чтобы выполнить такое преобразование, нужно просто начать писать желаемый текст в соседнем столбце вручную. На второй или третьей строке Excel попытается предугадать наши действия и выполнит дальнейшую обработку автоматически. Останется только нажать клавишу Enter для подтверждения, и все имена будут преобразованы мгновенно.
3. Скопировать без нарушения форматовВы, скорее всего, знаете про «волшебный» маркер автозаполнения — тонкий чёрный крест в правом нижнем углу ячейки, потянув за который можно скопировать содержимое ячейки или формулу сразу на несколько ячеек. Однако есть один неприятный нюанс: такое копирование часто нарушает дизайн таблицы, т. к. копируется не только формула, но и формат ячейки. Этого можно избежать, если сразу после протягивания чёрным крестом нажать на смарт-тег — специальный значок, появляющийся в правом нижнем углу скопированной области.Если выбрать опцию «Копировать только значения» (Fill Without Formatting), то Microsoft Excel скопирует вашу формулу без формата и не будет портить оформление.
4. Отображение данных из таблицы Excel на картеВ последней версии Excel 2013 появилась возможность быстро отобразить на интерактивной карте ваши геоданные, например продажи по городам и т. п. Для этого нужно перейти в «Магазин приложений» (Office Store) на вкладке «Вставка» (Insert) и установить оттуда плагин Bing Maps.
Это можно сделать и по прямой ссылке с сайта, нажав кнопку Add. После добавления модуля его можно выбрать в выпадающем списке «Мои приложения» (My Apps) на вкладке «Вставка» (Insert) и поместить на ваш рабочий лист. Останется выделить ваши ячейки с данными и нажать на кнопку Show Locations в модуле карты, чтобы увидеть наши данные на ней.
5. Быстрый переход к нужному листуПоявится оглавление, и на любой нужный лист можно будет перейти мгновенно.Также можно создать на отдельном листе оглавление с гиперссылками. Это чуть сложнее, но зачастую удобнее.
6. Преобразование строк в столбцы и обратноЕсли вам когда-нибудь приходилось руками перекладывать ячейки из строк в столбцы, то вы оцените следующий трюк:Выделите диапазон.
Скопируйте его (Ctrl + C) или, нажав на правую кнопку мыши, выберите «Копировать» (Copy).Щёлкните правой кнопкой мыши по ячейке, куда хотите вставить данные, и выберите в контекстном меню один из вариантов специальной вставки — значок «Транспонировать» (Transpose).В старых версиях Excel нет такого значка, но можно решить проблему с помощью специальной вставки (Ctrl + Alt + V) и выбора опции «Транспонировать» (Transpose)
7. Выпадающий список в ячейке
Если в какую-либо ячейку предполагается ввод строго определённых значений из разрешённого набора (например, только «да» и «нет» или только из списка отделов компании и т. д.), то это можно легко организовать при помощи выпадающего списка:Выделите ячейку (или диапазон ячеек), в которых должно быть такое ограничение.Нажмите кнопку «Проверка данных» на вкладке «Данные» (Data — Validation).В выпадающем списке «Тип» (Allow) выберите вариант «Список» (List).В поле «Источник» (Source) задайте диапазон, содержащий эталонные варианты элементов, которые и будут впоследствии выпадать при вводе.
8. «Умная» таблицаЕсли выделить диапазон с данными и на вкладке «Главная» нажать «Форматировать как таблицу» (Home — Format as Table), то наш список будет преобразован в «умную» таблицу, которая (кроме модной полосатой раскраски) умеет много полезного:Автоматически растягиваться при дописывании к ней новых строк или столбцов.Введённые формулы автоматом будут копироваться на весь столбец.Шапка такой таблицы автоматически закрепляется при прокрутке, и в ней включаются кнопки фильтра для отбора и сортировки.На появившейся вкладке «Конструктор» (Design) в такую таблицу можно добавить строку итогов с автоматическим вычислением.
9. СпарклайныСпарклайны — это нарисованные прямо в ячейках миниатюрные диаграммы, наглядно отображающие динамику наших данных. Чтобы их создать, нажмите кнопку «График» (Line) или «Гистограмма» (Columns) в группе «Спарклайны» (Sparklines) на вкладке «Вставка» (Insert). В открывшемся окне укажите диапазон с исходными числовыми данными и ячейки, куда вы хотите вывести спарклайны.После нажатия на кнопку «ОК» Microsoft Excel создаст их в указанных ячейках. На появившейся вкладке «Конструктор» (Design) можно дополнительно настроить их цвет, тип, включить отображение минимальных и максимальных значений и т. д.
10. Восстановление несохранённых файловПятница. Вечер. Долгожданный конец ударной трудовой недели. Предвкушая отдых, вы закрываете отчёт, с которым возились последнюю половину дня, и в появившемся диалоговом окне «Сохранить изменения в файле?» вдруг зачем-то жмёте «Нет».Опустевший офис оглашает ваш истошный вопль, но уже поздно — несколько последних часов работы пошли псу под хвост и вместо приятного вечера в компании друзей вам придётся восстанавливать утраченное.На самом деле, есть неслабый шанс исправить ситуацию. Если у вас Excel 2010, то нажмите на «Файл» — «Последние» (File — Recent) и найдите в правом нижнем углу экрана кнопку «Восстановить несохранённые книги» (Recover Unsaved Workbooks). В Excel 2013 путь немного другой: «Файл» — «Сведения» — «Управление версиями» — «Восстановить несохранённые книги» (File — Properties — Recover Unsaved Workbooks). Откроется специальная папка из недр Microsoft Office, куда на такой случай сохраняются временные копии всех созданных или изменённых, но несохранённых книг.
11. Сравнение двух диапазонов на отличия и совпаденияВесьма часто при работе в Excel возникает необходимость сравнить два списка и быстро найти элементы, которые в них совпадают или отличаются. Самый быстрый и наглядный способ сделать это:Выделите оба сравниваемых столбца (удерживая клавишу Ctrl).Выберите на вкладке «Главная» — «Условное форматирование» — «Правила выделения ячеек» — «Повторяющиеся значения» (Home — Conditional formatting — Highlight Cell Rules — Duplicate Values).Выберите вариант «Уникальные» (Unique) в раскрывающемся списке.
12. Подбор (подгонка) результатов расчёта под нужные значенияВы когда-нибудь подбирали входные значения в вашем расчёте Excel, чтобы получить на выходе нужный результат? В такие моменты чувствуешь себя матёрым артиллеристом, правда? Всего-то пара десятков итераций «недолёт — перелёт», и вот оно, долгожданное «попадание»!Microsoft Excel сможет сделать такую подгонку за вас, причём быстрее и точнее. Для этого нажмите на вкладке «Вставка» кнопку «Анализ „что если“» и выберите команду «Подбор параметра» (Insert — What If Analysis — Goal Seek). В появившемся окне задайте ячейку, где хотите подобрать нужное значение, желаемый результат и входную ячейку, которая должна измениться. После нажатия на «ОК» Excel выполнит до 100 «выстрелов», чтобы подобрать требуемый вами итог с точностью до 0,001.
vsegda-tvoj.livejournal.com
Раскрываем 9 секретных функций «Excel»
Excel — не самая дружелюбная программа на свете. Обычный пользователь использует лишь 5% её возможностей и плохо представляет, какие сокровища скрывают её недра. Используя советы Excel-гуру, можно научиться:
- сравнивать прайс-листы;
- прятать секретную информацию от чужих глаз;
- составлять аналитические отчёты в пару кликов.
1. Супертайный листДопустим, Вы хотите скрыть часть листов в Excel от других пользователей, работающих над книгой. Если сделать это классическим способом — кликнуть правой кнопкой по ярлычку листа и нажать на «Скрыть» (картинка 1), то имя скрытого листа всё равно будет видно другому человеку. Чтобы сделать его абсолютно невидимым, нужно действовать так:
— Нажмите ALT+F11.
— Слева у Вас появится вытянутое окно.
— В верхней части окна выберите номер листа, который хотите скрыть.
— В нижней части в самом конце списка найдите свойство Visible и сделайте его xlSheetVeryHidden. Теперь об этом листе никто, кроме Вас, не узнает.
2. Запрет на изменения задним числомПеред нами таблица с незаполненными полями «Дата» и «Кол-во». Менеджер Вася сегодня укажет, сколько морковки за день он продал. Как сделать так, чтобы в будущем он не смог внести изменения в эту таблицу задним числом?
— Поставьте курсор на ячейку с датой и выберите в меню пункт «Данные».
— Нажмите на кнопку «Проверка данных». Появится таблица.
— В выпадающем списке «Тип данных» выбираем «Другой».
— В графе «Формула» пишем =А2=СЕГОДНЯ().
— Убираем галочку с «Игнорировать пустые ячейки».
— Нажимаем кнопку «ОК». Теперь, если человек захочет ввести другую дату, появится предупреждающая надпись.
— Также можно запретить изменять цифры в столбце «Кол-во». Ставим курсор на ячейку с количеством и повторяем алгоритм действий.
3. Запрет на ввод дублейВы хотите ввести список товаров в прайс-лист так, чтобы они не повторялись. Вы можете установить запрет на такой повтор. В примере указана формула для столбца из 10 ячеек, но их, конечно, может быть любое количество.
— Выделяем ячейки А1:А10, на которые будет распространяться запрет.
— Во вкладке «Данные» нажимаем кнопку «Проверка данных».
— Во вкладке «Параметры» из выпадающего списка «Тип данных» выбираем вариант «Другой».
— В графе «Формула» вбиваем =СЧЁТ ЕСЛИ($A$1:$A$10;A1)<=1.
— В этом же окне переходим на вкладку «Сообщение об ошибке» и там вводим текст, который будет появляться при попытке ввести дубликаты.
— Нажимаем «ОК».
4. Выборочное суммированиеПеред Вами таблица, из которой видно, что разные заказчики несколько раз покупали у Вас разные товары на определённые суммы. Вы хотите узнать, на какую общую сумму заказчик по имени ANTON купил у Вас крабового мяса (Boston Crab Meat).
— В ячейку G4 вы вводите имя заказчика ANTON.
— В ячейку G5 — название продукта Boston Crab Meat.
— Встаёте на ячейку G7, где у Вас будет подсчитана сумма, и пишете для неё формулу {=СУММ((С3:С21=G4)*( B3:B21=G5)*D3:D21)}. Сначала она пугает своими объёмами, но если писать постепенно, то её смысл становится понятен.
— Сначала вводим {=СУММ и открываем скобки, в которых будет три множителя.
— Первый множитель (С3:С21=G4) ищет в указанном списке клиентов упоминания ANTON.
— Второй множитель (B3:B21=G5) делает то же самое с Boston Crab Meat.
— Третий множитель D3:D21 отвечает за столбец стоимости, после него мы закрываем скобки.
— Вместо Enter при написании формул в Excel нужно вводить Ctrl + Shift + Enter.
5. Сводная таблицаУ Вас есть таблица, где указано, какой товар, какому заказчику, на какую сумму продал конкретный менеджер. Когда она разрастается, выбирать отдельные данные из неё очень сложно. Например, Вы хотите понять, на какую сумму продано моркови или кто из менеджеров выполнил больше всего заказов. Для решения таких проблем в Excel существуют сводные таблицы. Чтобы создать такую таблицу, Вам нужно:
— Во вкладке «Вставка» нажать кнопку «Сводная таблица».
— В появившемся окне нажать «ОК».
— Появится окошко, в котором Вы можете сформировать новую таблицу, используя только интересующие Вас данные.
6. Товарный чекЧтобы посчитать общую сумму заказа, можно поступить как обычно: добавить столбец, в котором нужно перемножить цену и количество, а потом посчитать сумму по этому столбцу. Если же перестать бояться формул, можно сделать это более изящно.
— Выделяем ячейку C7.
— Вводим =СУММ(.
— Выделяем диапазон B2:B5.
— Вводим звёздочку, которая в Excel — знак умножения.
— Выделяем диапазон C2:C5 и закрываем скобку (картинка 2).
— Вместо Enter при написании формул в Excel нужно вводить Ctrl + Shift + Enter.
7. Сравнение прайсовЭто пример для продвинутых пользователей Excel. Допустим, у Вас есть два прайса, и Вы хотите сравнить их цены. На 1-й и 2-й картинке у нас прайсы от 4 и от 11 мая 2010 года. Часть товаров в них не совпадает — вот как узнать, что это за товары.
— Создаём в книге ещё один лист и копируем в него списки товаров и из первого, и из второго прайса.
— Чтобы избавиться от дублей товаров, выделяем весь список товаров, включая его название.
— В меню выбираем «Данные» — «Фильтр» — «Расширенный фильтр».
— В появившемся окне отмечаем три вещи:
а) скопировать результат в другое место;
б) поместить результат в диапазон — выберите место, куда хотите записать результат, в примере это ячейка D4;
в) поставьте галочку на «Только уникальные записи».
— Нажимаем кнопку «ОК» и, начиная с ячейки D4, получаем список без дублей.
— Удаляем первоначальный список товаров.
— Добавляем колонки для загрузки значений прайса за 4 и 11 мая и колонку сравнения.
— Вводим в колонку сравнения формулу =D5-C5, которая будет вычислять разницу.
— Осталось автоматически загрузить в колонки «4 мая» и «11 мая» значения из прайсов. Для этого используем функцию: =ВПР( искомое_значение; таблица; номер_столбца; интервальный _просмотр).
— «Искомое_значение» — это строчка, которую мы будем искать в таблице прайса. Легче всего искать товары по их наименованию.
— «Таблица» — это массив данных, в котором мы будем искать нужное нам значение. Он должен ссылаться на таблицу, содержащую прайс от 4-го числа.
— «Номер_столбца» — это порядковый номер столбца в диапазоне, который мы задали для поиска данных. Для поиска мы определили таблицу из двух столбцов. Цена содержится во втором из них.
— Интервальный_просмотр. Если таблица, в которой Вы ищете значение, отсортирована по возрастанию или по убыванию, надо ставить значение ИСТИНА, если не отсортирована — пишете ЛОЖЬ.
— Протяните формулу вниз, не забыв закрепить диапазоны. Для этого поставьте перед буквой столбца и перед номером строки значок доллара (это можно сделать, выделив нужный диапазон и нажав клавишу F4).
— В итоговом столбце отражается разница в ценах по тем позициям, которые есть и в том, и в другом прайсе. Если в итоговом столбце отражается #Н/Д, это значит, что указанный товар есть только в одном из прайсов, а следовательно, разницу вычислить невозможно.
8. Оценка инвестицийВ Excel можно посчитать чистый дисконтированный доход (NPV), то есть сумму дисконтированных значений потока платежей на сегодняшний день. В примере рассчитана величина NPV на основе одного периода инвестиций и четырёх периодов получения доходов (строка 3 «Денежный поток»).
— Формула в ячейке B6 вычисляет NPV с помощью финансовой функции: =ЧПС($B$4;$C$3:$E$3)+B3.
— В пятой строке расчёт дисконтированного потока в каждом периоде находится с помощью двух разных формул.
— В ячейке С5 результат получен благодаря формуле =C3/((1+$B$4)^C2) (картинка 2).
— В ячейке C6 тот же результат получен через формулу {=СУММ(B3:E3/((1+$B$4)^B2:E2))}.
9. Сравнение инвестиционных предложенийВ Excel можно сравнить, какое из двух предложений об инвестировании выгоднее. Для этого нужно выписать в два столбца требуемый объём инвестиций и суммы их поэтапного возврата, а также отдельно указать учётную ставку инвестирования в процентах. С помощью этих данных можно вычислить чистую приведённую стоимость (NPV).
— В свободную ячейку нужно ввести формулу =npv(b3/12,A8:A12)+A7, где b3 — учётная ставка, 12 — число месяцев в году, A8:A12 — столбец с цифрами поэтапного возврата инвестиций, A7 — необходимая сумма вложений.
— По точно такой же формуле рассчитывается чистая приведённая стоимость другого инвест-проекта.
— Теперь их можно сравнить: у кого больше NPV, тот проект выгоднее.
bomba.co
Интересные факты и полезные советы
Все, о чем вы хотели знать, но боялись спросить.
Человеку непосвященному программа работы с таблицами Excel кажется огромной, непонятной и оттого — пугающей. На самом деле это удобный инструмент, и если знать небольшие хитрости, то можно сильно сократить время на выполнение обычных функций.
1. Быстро добавить новые данные в диаграмму:
Если для вашей уже построенной диаграммы на листе появились новые данные, которые нужно добавить, то можно просто выделить диапазон с новой информацией, скопировать его (Ctrl + C) и потом вставить прямо в диаграмму (Ctrl + V).
2. Мгновенное заполнение (Flash Fill)
Эта функция появилась только в последней версии Excel 2013, но она стоит того, чтобы обновиться до новой версии досрочно. Предположим, что у вас есть список полных ФИО (Иванов Иван Иванович), которые вам надо превратить в сокращённые (Иванов И. И.).
Чтобы выполнить такое преобразование, нужно просто начать писать желаемый текст в соседнем столбце вручную. На второй или третьей строке Excel попытается предугадать наши действия и выполнит дальнейшую обработку автоматически. Останется только нажать клавишу Enter для подтверждения, и все имена будут преобразованы мгновенно.
Подобным образом можно извлекать имена из email’ов, склеивать ФИО из фрагментов и т. д.
3. Скопировать без нарушения форматов
Вы, скорее всего, знаете про «волшебный» маркер автозаполнения — тонкий чёрный крест в правом нижнем углу ячейки, потянув за который можно скопировать содержимое ячейки или формулу сразу на несколько ячеек. Однако есть один неприятный нюанс: такое копирование часто нарушает дизайн таблицы, т. к. копируется не только формула, но и формат ячейки. Этого можно избежать, если сразу после протягивания чёрным крестом нажать на смарт-тег — специальный значок, появляющийся в правом нижнем углу скопированной области.
Если выбрать опцию «Копировать только значения» (Fill Without Formatting), то Microsoft Excel скопирует вашу формулу без формата и не будет портить оформление.
4. Отображение данных из таблицы Excel на карте
В последней версии Excel 2013 появилась возможность быстро отобразить на интерактивной карте ваши геоданные, например продажи по городам и т. п. Для этого нужно перейти в «Магазин приложений» (Office Store) на вкладке «Вставка» (Insert) и установить оттуда плагин Bing Maps.
Это можно сделать и по прямой ссылке с сайта, нажав кнопку Add. После добавления модуля его можно выбрать в выпадающем списке «Мои приложения» (My Apps) на вкладке «Вставка» (Insert) и поместить на ваш рабочий лист. Останется выделить ваши ячейки с данными и нажать на кнопку Show Locations в модуле карты, чтобы увидеть наши данные на ней.
При желании в настройках плагина можно выбрать тип диаграммы и цвета для отображения.
5. Быстрый переход к нужному листу
Появится оглавление, и на любой нужный лист можно будет перейти мгновенно.
Также можно создать на отдельном листе оглавление с гиперссылками. Это чуть сложнее, но зачастую удобнее.
6. Преобразование строк в столбцы и обратно
Если вам когда-нибудь приходилось руками перекладывать ячейки из строк в столбцы, то вы оцените следующий трюк:
Выделите диапазон.Скопируйте его (Ctrl + C) или, нажав на правую кнопку мыши, выберите «Копировать» (Copy).Щёлкните правой кнопкой мыши по ячейке, куда хотите вставить данные, и выберите в контекстном меню один из вариантов специальной вставки — значок «Транспонировать» (Transpose).
В старых версиях Excel нет такого значка, но можно решить проблему с помощью специальной вставки (Ctrl + Alt + V) и выбора опции «Транспонировать» (Transpose).
7. Выпадающий список в ячейке
Если в какую-либо ячейку предполагается ввод строго определённых значений из разрешённого набора (например, только «да» и «нет» или только из списка отделов компании и т. д.), то это можно легко организовать при помощи выпадающего списка:
Выделите ячейку (или диапазон ячеек), в которых должно быть такое ограничение.Нажмите кнопку «Проверка данных» на вкладке «Данные» (Data — Validation).В выпадающем списке «Тип» (Allow) выберите вариант «Список» (List).В поле «Источник» (Source) задайте диапазон, содержащий эталонные варианты элементов, которые и будут впоследствии выпадать при вводе.
Продвинутые трюки из той же серии: выпадающий список с наполнением, связанные выпадающие списки, выпадающий список с фотографией и т. д.
8. «Умная» таблица
Если выделить диапазон с данными и на вкладке «Главная» нажать «Форматировать как таблицу» (Home — Format as Table), то наш список будет преобразован в «умную» таблицу, которая (кроме модной полосатой раскраски) умеет много полезного:
Автоматически растягиваться при дописывании к ней новых строк или столбцов.Введённые формулы автоматом будут копироваться на весь столбец.Шапка такой таблицы автоматически закрепляется при прокрутке, и в ней включаются кнопки фильтра для отбора и сортировки.На появившейся вкладке «Конструктор» (Design) в такую таблицу можно добавить строку итогов с автоматическим вычислением.
9. Спарклайны
Спарклайны — это нарисованные прямо в ячейках миниатюрные диаграммы, наглядно отображающие динамику наших данных. Чтобы их создать, нажмите кнопку «График» (Line) или «Гистограмма» (Columns) в группе «Спарклайны» (Sparklines) на вкладке «Вставка» (Insert). В открывшемся окне укажите диапазон с исходными числовыми данными и ячейки, куда вы хотите вывести спарклайны.
После нажатия на кнопку «ОК» Microsoft Excel создаст их в указанных ячейках. На появившейся вкладке «Конструктор» (Design) можно дополнительно настроить их цвет, тип, включить отображение минимальных и максимальных значений и т. д.
10. Восстановление несохранённых файлов
Пятница. Вечер. Долгожданный конец ударной трудовой недели. Предвкушая отдых, вы закрываете отчёт, с которым возились последнюю половину дня, и в появившемся диалоговом окне «Сохранить изменения в файле?» вдруг зачем-то жмёте «Нет».
Опустевший офис оглашает ваш истошный вопль, но уже поздно — несколько последних часов работы пошли псу под хвост и вместо приятного вечера в компании друзей вам придётся восстанавливать утраченное.
На самом деле, есть неслабый шанс исправить ситуацию. Если у вас Excel 2010, то нажмите на «Файл» — «Последние» (File — Recent) и найдите в правом нижнем углу экрана кнопку «Восстановить несохранённые книги» (Recover Unsaved Workbooks). В Excel 2013 путь немного другой: «Файл» — «Сведения» — «Управление версиями» — «Восстановить несохранённые книги» (File — Properties — Recover Unsaved Workbooks). Откроется специальная папка из недр Microsoft Office, куда на такой случай сохраняются временные копии всех созданных или изменённых, но несохранённых книг.
11. Сравнение двух диапазонов на отличия и совпадения
Весьма часто при работе в Excel возникает необходимость сравнить два списка и быстро найти элементы, которые в них совпадают или отличаются. Самый быстрый и наглядный способ сделать это:
Выделите оба сравниваемых столбца (удерживая клавишу Ctrl).Выберите на вкладке «Главная» — «Условное форматирование» — «Правила выделения ячеек» — «Повторяющиеся значения» (Home — Conditional formatting — Highlight Cell Rules — Duplicate Values).Выберите вариант «Уникальные» (Unique) в раскрывающемся списке.
12. Подбор (подгонка) результатов расчёта под нужные значения
Вы когда-нибудь подбирали входные значения в вашем расчёте Excel, чтобы получить на выходе нужный результат? В такие моменты чувствуешь себя матёрым артиллеристом, правда? Всего-то пара десятков итераций «недолёт — перелёт», и вот оно, долгожданное «попадание»!
Microsoft Excel сможет сделать такую подгонку за вас, причём быстрее и точнее. Для этого нажмите на вкладке «Данные» кнопку «Анализ „что если“» и выберите команду «Подбор параметра» (Insert — What If Analysis — Goal Seek). В появившемся окне задайте ячейку, где хотите подобрать нужное значение, желаемый результат и входную ячейку, которая должна измениться. После нажатия на «ОК» Excel выполнит до 100 «выстрелов», чтобы подобрать требуемый вами итог с точностью до 0,001.
evrokom.su
Интересные возможности MS Excel | Microsoft Office
Каждый, кто виртуозно овладеет такой программой, как Microsoft Excel, сможет успешно вести дела в своем бизнесе или до минимума упростить свою повседневную работу в офисе.
В этом посте мне удастся рассказать лишь о некоторых интересных возможностях, которые дарит Microsoft Excel своим пользователям.
Например, можно производить вычисление без формул. Этот инструмент позволяет в некоторых случаях сделать большие вычисления всего парой кликов.
Предположим, имеется таблица, сплошь заполненная большими цифрами.
Требуется разделить эти числа на 100. Конечно же, можно пойти обычным неинтересным путем. Создать формулы и так далее…
Проще же сделать так:
• в любую пустую ячейку вбить 100;• скопировать эту ячейку удобным способом. Например, Ctrl+C;• выделить таблицу с числами;• щелкнуть по выделенной таблице правой кнопкой мыши;• в появившемся окне нажать на «Специальная вставка»;• выбирать «значения», «разделить», OK.
Все, таблица преобразилась. Суммы уменьшились в сто раз.Этой функцией можно пользоваться при
• расчете налогов, имеющих фиксированные ставки;• делать перерасчет таблиц, переводя суммы в другую валюту;• играть с датами, сдвигая их на какое-то количество дней вперед или назад.
На некоторых клавиатурах имеется специальная клавиша с калькулятором.
Но в экселе можно установить это удобное средство расчета на панели быстрого доступа.
Для этого нужно лишь щелкнуть правой мышкой по левой верхней части окна Excel.
В контекстном меню выбрать «Настройки панели быстрого доступа».
Потом выбрать «Все команды» и в их перечне отыскать «Калькулятор».
Кнопка «Добавить» перекинет «Калькулятор» в другое окно. Ок.
Калькулятор попал на верхнюю панель быстрого доступа. Теперь можно им всегда пользоваться.
При необходимости переместить строки на место столбцов и наоборот, делаем следующее:
• выделяем таблицу;• копируем ее с помощью Ctrl+C;• встаем на пустую ячейку. В нее будет помещена новая таблица;• правой кнопкой мыши вызываем контекстное меню;• команда Специальная вставка;• ставим галочку около «транспонировать». ОК.
Таблица перевернулась. Данный способ имеет свои недостатки.
Во первых, ячейки с формулами могут копироваться некорректно, а во вторых, неплохо было бы, чтобы корректировка данных одной таблицы отражалась на другую.
К сожалению, этого не происходит. Но зато форматирование ячеек в таблицах одинаковое.
Похожие записи:
ds-release.ru
Полезные функции для работы в Excel, о которых многие не знают
Краткая памятка всем, кто использует таблицы Excel в работе.
1. Сводные таблицы
ad4
Если вы хотите поглубже изучить Excel, то разобраться в том, как создаются и используются сводные таблицы — это первое, что вам нужно сделать. Они помогут сортировать большое количество данных, автоматически рассчитывать итоговые и промежуточные суммы, получать средние значения.Как применять:— Выделить исходные табличные данные (таблица с данными должна представлять собой сетку, где есть заголовки и названия строк и нет пустых строк).— Выбрать в меню раздел Вставка > Рекомендуемые сводные таблицы. В диалоговом окне Рекомендуемые сводные таблицы выбрать наиболее подходящий для ваших нужд макет, добавление таблицы на тот же лист или на новый лист и нажать кнопку ОК.— Добавится сводная таблица и список полей справа, с помощью которого можно настраивать таблицу так, как вам удобно.
ad5
2. Подбор параметра
Средство подбора параметров пригодится вам в том случае, если вы знаете, что должно получиться в результате вычисления, но не знаете исходных значений для постановки в формулу.Как применять:— Выбрать Данные > Работа с данными > Анализ «что если» > Подбор параметра.— Ввести в поле «Установить в ячейке» ссылку на ту ячейку, где находится формула.— Ввести нужный вам результат вычисления в поле «Значение».— Ввести в поле «Изменяя значение ячейки» ссылку на ту ячейку, где находится корректируемое значение, и нажать кнопку ОК.
3. Условное форматирование
В программе Excel есть очень удобная штука — целая библиотека стилей условного форматирования. С помощью условного форматирования можно сортировать значения в таблице, выделяя их значками или цветом.Как применять:— Вкладка меню Главная — группа Стили — Условное форматирование — выбор нужного стиля форматирования — задание соответствующих форматированию условий.— Например, вы хотите выделить красным цветом все значения больше 1000. Правила выделения ячеек > Больше > 1000 > Формат — выделить красным > ОК.
ad6
4. ИНДЕКС и ПОИСКПОЗ
С помощью этих функций вы сможете легко отыскать нужные данные даже в самой большой таблице.Как применять:— Используйте функцию ПОИСКПОЗ для поиска столбца, в котором расположен искомый элемент, а затем ту же функцию еще раз — для поиска нужной строки.— Вставьте ответы в ИНДЕКС. Excel определит ячейку, в которой найденные столбец и строка пересекаются.Например: ИНДЕКС (array, ПОИСКПОЗ (lookup_value, lookup_array, 0), ПОИСКПОЗ (lookup_value, lookup_array, 0)).
5. Диаграмма «Водопад»
Если вы работаете с финансовыми или статистическими данными, то имеете дело с большим количеством данных, которые удобно представить в графическом виде. Есть такая диаграмма «водопад», которая наглядно позволяет увидеть отклонения показателей в ту или иную сторону в течение конкретного периода.Как применять:— Выделить нужные данные и выбрать Вставка > Водопад или Диаграмма > Водопад.
6. Прогноз
Незаменимая функция для тех, чья работа связана с прогнозированием поведения каких-либо параметров в будущем на основе исторических данных.Как применять:— Указать ячейки, в которых содержатся исторические данные.— Выбрать Данные > Прогноз > Лист прогноза.— В открывшемся окне для создания листа прогноза выбрать подходящие для визуализации прогноза график или гистограмму.— В поле Завершение прогноза выбрать будущую дату (дату завершения прогнозного периода) и нажать кнопку Создать.
Источник
hopop.ru
Статистические функции в Excel - Microsoft Excel для начинающих
В этом разделе даётся обзор некоторых наиболее полезных статистических функций Excel.
СРЗНАЧ
Функция СРЗНАЧ (AVERAGE) используется для вычисления среднего арифметического значения. Аргументы могут быть заданы, например, как ссылка на диапазон ячеек.
СРЗНАЧЕСЛИ
Чтобы вычислить среднее арифметическое ячеек, удовлетворяющих заданному критерию, используйте функцию СРЗНАЧЕСЛИ (AVERAGEIF). Вот как, например, можно вычислить среднее арифметическое значение для всех ячеек диапазона A1:O1, значение которых не равно нулю (<>0).
Замечание: Знак <> означает НЕ РАВНО. Функция СРЗНАЧЕСЛИ очень похожа на функцию СУММЕСЛИ.
МЕДИАНА
При помощи функции МЕДИАНА (MEDIAN) можно определить медиану (середину) набора чисел.
Проверим:
МОДА
Функция МОДА (MODE) находит наиболее часто встречающееся число в наборе чисел.
Стандартное отклонение
Чтобы вычислить стандартное отклонение, используйте функцию СТАНДОТКЛОН (STDEV).
МИН
При помощи функции МИН (MIN) можно найти минимальное значение из набора чисел.
МАКС
При помощи функции МАКС (MAX) можно найти максимальное значение из набора чисел.
НАИБОЛЬШИЙ
Вот как при помощи функции НАИБОЛЬШИЙ (LARGE) можно найти третье наибольшее значение из набора чисел.
Проверим:
НАИМЕНЬШИЙ
Вот как можно найти второе наименьшее значение при помощи функции НАИМЕНЬШИЙ (SMALL).
Проверим:
Оцените качество статьи. Нам важно ваше мнение:
office-guru.ru
6 малоизвестных, но очень полезных функций Excel
Microsoft Excel на сегодняшний день просто незаменим, особенно когда дело касается обработки больших объемов данных.
Однако у этой программы столько функций, что непросто разобраться, какие их них действительно нужные и полезные.
Мы расскажем какими способами можно эффективно систематизировать информацию и разложить все по полочкам.
Сводные таблицы
С помощью сводных таблиц очень удобно сортировать, рассчитывать сумму или получать среднее значение из данных электронной таблицы, при этом никакие формулы выводить не нужно.
Как применять:
- Выберите Вставка > Рекомендуемые сводные таблицы.
- В диалоговом окне Рекомендуемые сводные таблицы щелкните любой макет сводной таблицы, чтобы увидеть его в режиме предварительного просмотра, а затем выберите тот из них, в котором данные отображаются нужным вам образом. Нажмите кнопку ОК.
Подбор параметра
Если вы знаете, какой результат вычисления формулы вам нужен, но не можете определить входные значения, позволяющие его получить, используйте средство подбора параметров.
- Выберите Данные > Работа с данными > Анализ «что если» > Подбор параметра.
- В поле Установить в ячейке введите ссылку на ячейку, в которой находится нужная формула.
- В поле Значение введите нужный результат формулы.
- В поле Изменяя значение ячейки введите ссылку на ячейку, в которой находится корректируемое значение, и нажмите кнопку ОК.
Условное форматирование
Условное форматирование позволяет быстро выделить на листе важные сведения.
На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкойУсловное форматирование и выберите формулу, которая вам понадобится.
Например, если вам нужно выделить все значения меньше 100, выберитеПравила выделения ячеек > Меньше, а затем наберите 100. Перед тем как нажать ОК, можно выбрать формат, который будет применяться для подходящих значений.
ИНДЕКС и ПОИСКПОЗ
Если ВПР помогает находить нужные данные только в первом столбце, то, благодаря функциям ИНДЕКС и ПОИСКПОЗ, можно искать информацию внутри таблицы.
- Используйте функцию ПОИСКПОЗ: сначала, чтобы найти столбец, в котором расположен искомый элемент, и затем еще раз, чтобы перейти к строке с ответом.
- Вставьте ответы в ИНДЕКС, и Excel сможет указать на ячейку, где эти значения пересекаются.
Например: ИНДЕКС (array, ПОИСКПОЗ (lookup_value, lookup_array, 0), ПОИСКПОЗ (lookup_value, lookup_array, 0)).
Диаграмма «водопад»
Это одна из форм визуализации данных, которая позволяет увидеть, в какую сторону менялись показатели в течение определенного периода. Очень полезная штука для тех, чья работа связана с финансами или статистикой.
В версии Excel 2016 необходимо выделить нужные данные и выбратьВставка > Водопад или Диаграмма > Водопад.
Данная функция позволяет вычислять и предсказывать будущие значения на основе уже имеющихся данных.
- Выберите Данные > Прогноз > Лист прогноза.
- В окне Создание листа прогноза выберите график или гистограммудля визуального представления прогноза.
- В поле Завершение прогноза выберите дату окончания, а затем нажмите кнопку Создать.
korysno.pro
