Срок доставки товара в течении 1-3 дней !!!
|
|
Exceltip. Эксель формулы на примерах для работы с таблицами
Формулы
Логическая функция Excel ЕСЛИ - одна из самых востребованных. Она возвращает некоторый результат (значение или другую формулу) в зависимости от наступления некоторого проверяемого условия. К примеру, если значение в некоторой ячейке соответствует заданному условию, то функция выдает определенных результат, в противном случае - альтернативный результат.
CommentsПроводить вычисления в зависимости от некоторого условия - насущная задача при обработке больших и не очень массивов данных. Так, по условию можно суммировать или считать среднюю. Можно даже подсчитать количество ячеек, соответствующих указанному критерию (СЧЁТЕСЛИ). А вот с расчетом максимального и минимального значения всегда возникали трудности. Таких формул просто не было и нужно было использовать обходные маневры. Метод работает надежно, но, разумеется, не всегда удобен. Во-первых, его нужно знать; во-вторых, формулы массивов медленнее, а в табличном режиме вообще не работают.
В начале этого (2016) года Microsoft Excel выпустил очередной релиз своей программы. Появились сразу несколько отличных возможностей для повышения эффективности работы. В этой заметке рассмотрены две новые текстовые функции Excel. На момент написания этой статьи они доступны пока только в режиме Excel online, поэтому скриншоты и последующий ролик записаны именно там.
CommentsНормальный закон – основа и следствие многих статистических методов. Но не из-за того, что исходные данные имеют нормальное распределение, это далеко не так, а потому что есть более сильная причина – Центральная предельная теорема. Поэтому нормальный закон используется гораздо чаще, чем встречается в природе.
CommentsМногие знают и успешно используют формулу ВПР, которая входит в перечень наиболее полезных функций Excel. Иногда для правильной работы ВПР приходится вносить некоторые коррективы, но в целом функция справляется со своей задачей неплохо.
CommentsМногие пользователи знают и применяют формулу ВПР. И это правильно. Известно также, что ВПР имеет ряд особенностей и ограничений, которые несложно обойти. Однако есть еще один нюанс, который значительно ограничивает возможности функции ВПР.
Функция ПОИСКПОЗ используется, когда в рамках указанного массива требуется узнать номер ячейки (начиная сверху), соответствующей условию поиска. Функция ПОИСКПОЗ возвращает не само значение (оно указывается внутри формулы), а позицию в указываемом массиве (столбце).
CommentsФункция ИНДЕКС применяется, когда из имеющейся таблицы нужно извлечь содержимое определенной ячейки, местоположение которой известно.
CommentsЗачастую в Excel требуется провести вычисление по некоторому условию (-ям). Так, для суммирования данных по некоторому критерию предусмотрена функция СУММЕСЛИ и СУММЕСЛИМН, для подсчета средней арифметической – СРЗНАЧЕСЛИ и СРЗНАЧЕСЛИМН, таким же образом можно подсчитать количество непустых ячеек в диапазоне – СЧЁТЕСЛИ и СЧЁТЕСЛИМН. Все эти функции отлично себя зарекомендовали.
CommentsДовольно часто содержимое нескольких ячеек нужно соединить в одну. Осуществить задуманное довольно легко с помощью функции Excel СЦЕПИТЬ. Эта функция умеет не только соединять ячейки, но и добавлять любые другие символы. В строке формул выглядит примерно так.
CommentsФормулы таблиц Excel | Exceltip
Таблицы Excel позволяет упростить процесс форматирования и анализа больших объемов данных. Впервые таблицы Excel были представлены в 2007 версии, как расширение инструмента Списки более ранних версий. Начиная с этой версии, у пользователей появилась возможность использовать формулы извлечения данных с таблиц.
Как создать таблицу Excel
Чтобы конвертировать обычный диапазон данных в таблицу Excel 2007+, вам необходимо выделить любую ячейку данного диапазона, перейти во вкладку Вставка, в группе Таблица нажать кнопку Таблица.
В появившемся диалоговом окне Создание таблицы, Excel укажет диапазон данных, который необходимо превратить в таблицу. Жмем ОК. Диапазон данных будет конвертирован в таблицу Excel.
Зачем нужны таблицы Excel?
Данные таблицы гораздо функциональнее и гибче, нежели стандартные диапазоны данных. К тому же, появляется ряд дополнительных возможностей, которые можно найти во вкладке Работа с таблицами –> Конструктор, которая появляется, если вы выделите любую ячейку таблицы.
Формат: Полностью изменяет внешний вид таблицы с помощью нескольких щелчков мыши.
Подведение итогов: Чтобы добавить дополнительную строку с итоговыми значениями, в группе Параметры стилей таблиц поставьте маркер напротив поля Строка итогов. Обратите внимание, что среди итоговых значений доступны все не только операции суммирования, но и среднее, максимум, минимум и т.д.
Доступ другим пользователям: Вы можете экспортировать выбранную таблицу с помощью сервиса SharePoint.
Задавать имя таблицы: Вы можете задать таблице осмысленное название, которое будет в дальнейшем использоваться в формулах. Чтобы дать имя таблице, перейдите по вкладке Работа с таблицами -> Конструктор в группу Свойства. В поле Имя таблицы укажите желаемое название таблицы.
Формулы таблиц Excel
Табличные формулы позволяют получить данные таблицы простым и интуитивно понятным способом. При создании таблицы, Excel по умолчанию установит название Таблица 1, что не совсем отвечает концепции интуитивно понятный. Поэтому вы можете изменить этот параметр, как мы описывали выше, на что-нибудь более элегантное, например, Продажи.
Теперь вы можете обратиться ко всей таблице целиком, либо к отдельным колонкам или строкам, диапазонам, заголовкам в ваших формулах.
Использование конкретной колонки таблицы в формулах
Возьмем для примера таблицу с данными о продажах, указанную в начале статьи, где вам необходимо определить среднее значение колонки Цель. Для этого необходимо в ячейку ввести формулу =СРЗНАЧ(Продажи[Цель]). Строка Продажи[Цель] указывает на то, что необходимо вернуть столбец Цель из таблицы Продажи. Аналогично, строка Продажи[Факт] вернет нам столбец Факт таблицы Продажи. Точно таким же образом мы можем использовать функции и для других операций, например, =МАКС(Продажи[Цель]), =СЧЁТЗ(Продажи[Цель]) или =МИН(Продажи[Цель]).
Использование конкретной строки таблицы в формулах
Работа со строками таблицы аналогична работе с колонками, но все есть небольшие различия. Чтобы обратиться к строке таблицы, необходимо использовать символ @. Таким образом, если вы хотите обратиться к 10 строке таблицы, вы должны поместить формулу =Продажи[@] в любую ячейку 10 строки рабочего листа. Символ @ означает, что вы обращаетесь к той же строке таблицы, на какой строке находится формула. Т.е. формула =СЧЁТЕСЛИ(Продажи[@];»<>») вернет количество не пустых ячеек соответствующей строки таблицы. Если вы скопируете данную формулу вниз, данные будут актуальны уже для следующей строки, хотя формула не изменится.
Использование всей таблицы в формулах
Подход аналогичный:
- Чтобы обратится ко всей таблице, используйте =Продажи[#Все]
- Если вас интересуют только значения таблицы, =Продажи[#Данные]
- Если только заголовки, =Продажи[#Заголовки]
Использование формулы ПРОМЕЖУТОЧНЫЕ.ИТОГИ в таблице
Другая интересная особенность таблиц является использование функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ, которая состоит из двух частей – первая, указывает на операцию, которую необходимо использовать для подведения итогов, вторая, содержит диапазон данных для обработки. Таким образом, формула =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;E1:E10), вернет сумму диапазона E1:E10, а формула =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(1; E1:E10) – среднее значение.
Возвращаемся к таблицам Excel. Вы можете воспользоваться функцией ПРОМЕЖУТОЧНЫЕ.ИТОГИ, чтобы подвести итоги по всей таблице или конкретным строкам или столбцам. Чтобы вернуть среднее значение колонки Факт, введите следующую формулу =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(1;Продажи[Факт]) .
Вы, наверное, обратили внимание, что когда мы включили опцию Строка итогов, в таблицу была добавлена новая строка с итогами. Теперь мы можем модифицировать итоговые значения, используя таблицу с функциями, изображенную выше. Как вы смогли заметить, функции не ограничиваются только суммированием, можно найти также ряд других полезных операций.
Использование итогов для конкретного столбца в формулах
Чтобы вернуть итоговое значение столбца таблицы Excel, например, Цель, необходимо воспользоваться формулой =Продажи[[#Итоги];[Факт]]. Обратите внимание, что возвращаемое значение не обязательно будет функцией суммирования, а будет определяться той функцией, которая определена для строки итогов. Давайте посмотрим это на примере. Если итоговое значение столбца Факт таблицы Продажи содержит номер функции 4, то в строке итогов вы увидите максимальное значение столбца Факт. А формула =Продажи[[#Итоги];[Факт]] вернет не сумму значений столбца Факт, а его максимум.
Вам также могут быть интересны следующие статьи
exceltip.ru
данные и формулы :: SYL.ru
Для любого офисного работника или студента, да и каждого человека, связанного с компьютерами в повседневной жизни, совершенно необходимо уметь обращаться с офисными программами. Одной из таких и является Microsoft Office Excel. Для начала рассмотрим, как сделать таблицу в Excel.
Основы
В первую очередь пользователь должен осознавать, что создание таблицы в Excel - простейшее дело, ведь программная среда этого офисного приложения создана изначально именно для работы с таблицами.
После запуска приложения перед пользователем появляется рабочая область, состоящая из множества ячеек. То есть вам не надо задумываться, как сделать таблицу в Excel собственными руками, потому что она уже готова. Всё, что нужно сделать пользователю, это очертить границы и ячейки, в которые он будет вносить данные.
У начинающих пользователей возникает естественный вопрос о том, что делать с ячейками при печати. В этом случае необходимо понимать, что на печать выводятся только те, которые вы очертили, именно они в итоге будут вашей таблицей.
Создаём первую таблицу
Для этого выделяем необходимую нам область, зажав левую кнопку мышки. Допустим, нам необходимо посчитать доход семьи за месяц, в котором 30 дней. Нам необходимо 3 столбца и 32 строки. Выделяем ячейки A1-С1 и растягиваем их вниз до А32-С32. Затем на панели находим кнопку границы и заливаем всю область так, чтобы получилась таблица в сетку. После этого приступаем к заполнению ячеек. В первой строке заполняем заголовок таблицы. Первый столбец озаглавим "Дата", второй - "Приход", третий - "Расход". После этого приступим к заполнению значений таблицы.
Надо заметить, что окрашивать границы таблицы и ячеек можно по-разному. Программа предоставляет пользователю различные варианты оформления границ. Это можно совершить следующим способом. Выделив необходимую область, нажимаете на неё правой кнопкой мышки и выбираете "Формат ячеек". Затем переходите на вкладку "Границы". Здесь имеется достаточно понятный интерфейс с образцом. Вы можете поэкспериментировать с выбором способа окрашивания границ вашей таблицы, а также типом линий.
Существует также немного другой способ попасть в это меню. Для этого нажимаем в верхней части экрана на панели управления на кнопку "Формат". В раскрывшемся списке выбираете "Ячейки". Перед вами появится точно такое же окно, как и в предыдущем варианте.
Заполнение таблицы
Разобравшись, как сделать таблицу в Excel, приступим к её наполнению. Для облегчения этой процедуры существует функция автозаполнения ячеек. Понятное дело, что в реальной ситуации таблица, которую мы создавали выше, будет заполняться построчно в течение месяца. Однако в нашем случае вам лучше заполнить ячейки столбцов "Приход" и "Расход" собственноручно.
Для заполнения столбца "Дата" воспользуемся функцией автозаполнения. В ячейке А2 укажем значение "1", а ячейке А3 присвоим значение "2". После этого выделим две эти ячейки мышкой и наведём указатель на правый нижний угол выделенной области. Появится перекрестие. Зажав левую кнопку мышки, растянем эту область до ячейки А31. Таким образом у нас получилась таблица данных в Excel.
Если вы всё сделали правильно, то у вас должен был выйти столбец с цифрами от 1 до 30. Как понимаете, Excel автоматически определил арифметический принцип, по которому вы собирались заполнять столбец. Если бы вы указали последовательно цифры 1 и 3, тогда программа выдала бы вам исключительно нечётные числа.
Подсчёты
Таблицы в Excel служат не только для хранения данных, но и для подсчета значений, исходя из введённых данных. Теперь разберёмся, как сделать таблицу в Excel с формулами.
После заполнения таблицы у нас осталась незаполненная нижняя строка. Сюда, в ячейку А32, вносим слово "Итого". Как вы поняли, здесь мы рассчитаем суммарный доход и расход семьи за месяц. Для того чтобы нам не пришлось вручную с помощью калькулятора рассчитывать итог, в Excel встроено множество различных функций и формул.
В нашей ситуации для подсчета итогового значения нам пригодится одна из простейших функций суммирования. Есть два способа сложить значения в столбцах.
- Сложить каждую ячейку по отдельности. Для этого в ячейке В32 ставим знак "=", после этого щелкаем на нужной нам ячейке. Она окрасится в другой цвет, а в строке формулы добавятся её координаты. У нас это будет "A2". Затем опять же в строке формул ставим "+" и снова выбираем следующую ячейку, её координаты также добавятся в строку формулы. Эту процедуру можно повторять множество раз. Её можно использовать в тех случаях, если значения, необходимые для формулы, находятся в разных частях таблицы. По завершении выделения всех ячеек нажимаем "Enter".
- Другой, более простой способ, может пригодиться, если вам необходимо сложить несколько смежных ячеек. В таком случае можно воспользоваться функцией "СУММ()". Для этого в ячейке В32 также ставим знак равенства, затем русскими заглавными буквами пишем "СУММ(" - с открытой скобкой. Выделяем нужную нам область и закрываем скобку. После чего нажимаем "Enter". Готово, Excel посчитал вам сумму всех ячеек.
Для вычисления суммы следующего столбца просто выделяем ячейку B32 и, как при автозаполнении, перетаскиваем в ячейку С32. Программа автоматически сместит все значения на одну ячейку вправо и посчитает следующий столбец.
Сводка
В некоторых случаях вам могут пригодиться сводные таблицы Excel. Они предназначены для совмещения данных из нескольких таблиц. Для создания такой выделите вашу таблицу, а затем на панели управления выберите "Данные" - "Сводная таблица". После серии диалоговых окон, которые могу помочь вам настроить таблицу, появится поле, состоящее из трёх элементов.
В поля строк перетащите один столбец, который вы считаете основным и по которому хотите сделать выборку. В поле данных перетащите столбец с данными, которые должны быть посчитаны.
Поле, предназначенное для перетаскивания столбцов, служит для сортировки данных по какому-либо признаку. Сюда вы также можете перенести необходимое условие/фильтр для выборки данных.
Заключение
Прочитав эту статью, вы должны были научиться создавать и работать с простейшими видами таблиц, а также осуществлять операции с данными. Мы кратко рассмотрели создание сводной таблицы. Это должно вам помочь освоиться в среде Microsoft Office и чувствовать себя увереннее в программе Excel.
www.syl.ru
