Excel Tutorial PDF

1. Fondamentaux de l'Analyse de Données ▪ Collecte de Données : Similaire à la planification d'un voyage, les analystes rassemblent et analysent les données pour prendre des décisions éclairées ▪ Outils et Techniques : Utilisation de logiciels comme Microsoft Excel et Power B...

1. Fondamentaux de l'Analyse de Données ▪ Collecte de Données : Similaire à la planification d'un voyage, les analystes rassemblent et analysent les données pour prendre des décisions éclairées ▪ Outils et Techniques : Utilisation de logiciels comme Microsoft Excel et Power BI ▪ Prise de Décision : Les entreprises s'appuient sur l'analyse des données pour prendre des décisions éclairées 2. Excel - Fonctionnalités de Base Saisie de Données ▪ Déplacement du curseur avec la souris ou les flèches ▪ Alignement : texte à gauche, nombres à droite ▪ Fonction Autocomplete pour les entrées répétitives ▪ Reconnaissance géographique avec "Convert to Geography" ▪ Fonction Annuler disponible sur la barre de titre Formatage ▪ Formatage des nombres (Currency, Comma) ▪ Wrap Text pour les titres de colonnes ▪ Format Painter pour copier le formatage ▪ Ajustement de la taille des cellules 3. Fonctionnalités Avancées d'Excel Navigation et Affichage ▪ Freeze Panes pour figer lignes/colonnes ▪ New Window pour vue multiple ▪ Name Box pour navigation rapide Tri et Filtrage ▪ Tri : ▪ Accessible depuis le ruban Data ▪ Tri à plusieurs niveaux possible ▪ Option "My data has headers" ▪ Filtrage : ▪ Activation via le bouton Filter ▪ Application de filtres par colonne ▪ Vérification du statut des filtres ▪ Suppression des filtres Orange Restricted Calculs et Formules ▪ Recalcul automatique à l'ouverture ▪ Ordre de priorité des opérations : ▪ Multiplication/Division avant Addition/Soustraction ▪ Références de cellules : ▪ Relatives : s'ajustent automatiquement ▪ Absolues : restent constantes ($A$1) 4. Perspectives de Carrière ▪ Forte demande d'analystes de données ▪ Opportunités dans divers secteurs ▪ Progression possible vers des rôles de data scientist ▪ Valorisation des compétences Excel et analyse de données 5. Intégration avec Power BI ▪ Préparation des données Excel pour Power BI ▪ Importance de la visualisation des données ▪ Compétences transférables entre les deux outils Data Collection: Just like planning a vacation or a party, data analysts gather and analyze data to make informed decisions. Tools and Techniques: Analysts use software like Microsoft Excel and Power BI to perform their tasks. Career Opportunities: There is a high demand for data analysts across various sectors, with roles ranging from business analyst to data scientist. Learning Path: The course prepares learners for a career in data analytics, starting with Excel fundamentals and progressing to data analysis techniques. Foundation for Data Analytics: This course provides essential skills for anyone interested in a career in data analytics. Mastering Excel is often the first step before moving on to more advanced tools like Microsoft Power BI. Data-Driven Decision Making: Businesses rely on data analysis to make informed decisions. By learning how to prepare and analyze data, you contribute to reducing risks and improving business outcomes. Integration with Power BI: The skills gained in this course will prepare you for using Power BI, a powerful tool for data visualization and reporting. This integration enhances your ability to present data insights effectively. Career Advancement: Proficiency in Excel and data analysis is highly valued in various industries. This course can enhance your employability and open up opportunities in data- related roles. Real-World Application: The course emphasizes practical skills that can be applied in real business scenarios, making your learning relevant and applicable. Orange Restricted  Data Analysis Basics: Understanding the importance of data in decision-making, as highlighted in the introductory materials, sets the stage for learning how to manipulate and analyze that data using Excel. Excel Skills: The course assumes some familiarity with Excel, which is often introduced in earlier lessons or courses. Mastering Excel fundamentals is crucial before diving into more complex data analysis tasks. Power BI Integration: The skills learned in this course directly prepare you for using Power BI, as you will learn how to prepare data in Excel for analysis in Power BI. This connection emphasizes the importance of data preparation as a precursor to visualization and reporting. Data Entry: Move the cursor using the mouse or arrow keys. Enter "Delaware" in cell C21 and "130422" in cell E21. Text and Number Alignment: Text aligns to the left, while numbers align to the right in cells. Excel treats entries with both letters and numbers as text. Autocomplete Feature: Typing "P" in cell D21 suggests "partner" based on previous entries. For "New Jersey," you must type it fully as it hasn't appeared before. Geographic Recognition: Use the "Convert to Geography" feature for state names, which interacts with Bing for additional information. Undo Feature: Available on the title bar (desktop) or Home ribbon (browser) to reverse recent actions. Autofill: To fill "New York" in cells C19 and C20, drag the fill handle (bottom right corner of the cell). Formatting Numbers: Highlight data from E2 to H21 and apply Currency format. Use the Comma format for thousands separators and decimal places. Column Titles: Type "state code" in B1 and "State" in C1, using Wrap Text to display full titles. Adjust font size and background color for emphasis. Format Painter: Copy formatting from cell B1 to A1:H1, including text wrapping and alignment. Data Entry and Formatting: Learn how to enter data correctly and format it for better readability. Alignment and Autocomplete: Understand how Excel aligns text and numbers, and how to use the autocomplete feature for efficiency. Data Types: Recognize how Excel treats different data types, such as text, numbers, and dates. Formatting Numbers: Familiarize yourself with formatting options for financial data, percentages, and other numeric formats. Using Functions: Get comfortable with common Excel functions and formulas for data analysis. Formatting Techniques: Explore techniques like wrapping text, using the Format Painter, and adjusting cell sizes for better presentation. Use to keep specific rows or columns static while scrolling. Select the row or column you want to freeze, then choose the appropriate option from the Freeze Panes drop-down. Orange Restricted  New Window: Opens a second view of the same file, allowing you to see different parts of the spreadsheet simultaneously. Name Box: Located to the left of the formula bar, it allows you to jump to specific cells by typing the cell reference and pressing "Enter." You can also assign names to cells for easier identification. Sorting Data: Understand how to reorder data in Excel using the sort feature, including single and multilevel sorting. Filtering Data: Learn how to refine data displayed based on specific criteria, hiding rows that do not match your selection. Using Formulas and Functions: Familiarize yourself with common Excel formulas and functions for data analysis. Data Preparation for Analysis: Gain skills in preparing Excel data for analysis, especially when transitioning to tools like Power BI. Sort Feature: o Found in the Sort & Filter group in the Data ribbon. o Reorders rows in the worksheet without changing the actual data. o To revert to the original order, use the Undo command. Be cautious, as this may reverse other actions if not the last one. o Once saved, the sort order becomes permanent. o Multilevel Sorting: o Use the Sort button to open a dialog box. Orange Restricted  o Ensure My data has headers is checked to exclude the header row from sorting. o Select the first sort criterion (e.g., supplier) and then add a second criterion (e.g., date entered) to sort by multiple levels simultaneously. Filtering Data Turning on Filtering: o Activate the filter feature by selecting the Filter button in the Sort & Filter group. o This adds filter arrows to each column header. Applying Filters: o Click the filter arrow next to a column heading to access dropdown lists. o Deselect all entries except the one you want to view (e.g., a specific supplier). o Apply the filter to hide rows that do not match the criteria. Checking Filter Status: o Look for a funnel symbol on the filter arrow to see if filtering is active. o Check for breaks in the sequence of row numbers to identify filtered data. Clearing Filters: o To remove a specific filter, select the filter arrow and choose Clear Filter. o To clear all filters, select the Clear option in the Sort & Filter group. Recalculation: o Excel automatically recalculates formulas when a file is opened. However, you can turn off this feature if needed, but remember to switch it back on afterward. o Order of Precedence: Excel does not always calculate formulas from left to right. Instead, it follows a specific order of precedence: Multiplication and Division are performed before Addition and Subtraction. Understanding this order is essential for creating accurate formulas. À partir de l’adresse o Cell References: Relative Cell References: Adjust automatically when a formula is copied to another cell. For example, if you copy a formula from cell K3, the references will change based on the new location. Absolute Cell References: Remain constant when a formula is copied. To create an absolute reference, add a dollar sign before the column letter and row number (e.g., $A$1). À partir de l’adresse Orange Restricted